When you create a table or add a column to a table in the database, columns are created as a specific data type. Data types are classifications that identify possible values for and operations that can be done on the data, as well as the way the data in that column is stored in the database.
When you access database tables from ArcGIS, you can work with specific data types. When accessing the database table through the Database Connections node in the Catalog tree or through a query layer in ArcMap, ArcGIS filters out any unsupported data types. If you access your database table directly, unsupported data types won't be displayed in the ArcGIS interface and you cannot edit them through ArcGIS. Similarly, when you copy tables containing unsupported data types with ArcGIS, it will only copy the supported data types; when you paste the table to another database or a geodatabase, the unsupported data type columns will not be present.
When you create a feature class or table in ArcGIS or add a column to an existing table or feature class using ArcGIS, there are 12 possible ArcGIS data types that can be assigned to a field. Database data types that don't correspond to these types cannot be used directly in ArcGIS client applications.
The first column lists the ArcGIS data types. The second column lists the SQL Server data type that will be created in the database. The third column shows what other SQL Server data types (if any) map to the ArcGIS data type when viewed in ArcGIS.
| ArcGIS data types | SQL Server data types created | Other SQL Server data types that can be viewed | Notes |
|---|---|---|---|
|
BLOB |
VARBINARY(MAX) | BINARY, IMAGE, TIMESTAMP, VARBINARY(n) | |
|
DATE |
DATETIME2(7) | DATETIME2(n), DATETIME, SMALLDATETIME | |
|
DOUBLE |
NUMERIC(p,s) | DECIMAL, FLOAT, MONEY, SMALLMONEY | The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. |
|
FLOAT |
NUMERIC(p,s) | REAL | The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. |
|
GEOMETRY |
INT, GEOMETRY, GEOGRAPHY | In an enterprise geodatabase, the SQL Server data type that is created depends on the GEOMETRY_STORAGE parameter of the configuration keyword used when the feature class was created. SDEBINARY = INT; GEOMETRY = a GEOMETRY column; GEOGRAPHY = a GEOGRAPHY column. In desktop and workgroup geodatabases, the GEOMETRY type is always used. In a database, you specify whether to use GEOMETRY or GEOGRAPHY when the feature class is created. ArcGIS does not support the following geometry subtypes:
| |
GLOBAL ID | UNIQUEIDENTIFIER | Only supported in geodatabases. | |
|
GUID |
UNIQUEIDENTIFIER | ||
|
LONG INTEGER |
INT | ||
|
RASTER |
INT, ST_RASTER | Raster data types are only supported in geodatabases. In desktop and workgroup geodatabases, rasters are always created as INT data types. In enterprise geodatabases, the SQL Server data type that is created depends on the configuration keyword used when the raster column is created. If the keyword's RASTER_STORAGE parameter is set to BINARY, an INT column is created; if set to ST_RASTER, an ST_Raster column is created. You must separately configure ST_Raster in the enterprise geodatabase if you want to use it. See Install the ST_Raster type in SQL Server for instructions. | |
|
OBJECT ID |
INT(4) when created in an enterprise geodatabase INTEGER with IDENTITY property when created in a database | The ArcGIS type ObjectID is the registered row ID column for the table (or feature class.) Only one may exist per table. | |
|
SHORT INTEGER |
SMALLINT | BIT, TINYINT | |
TEXT |
VARCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX) | CHAR, NCHAR |
Text data types
If you create a VARCHAR or VARCHAR(MAX) field in a SQL Server database, it will be mapped to the ArcGIS TEXT data type when viewed in ArcGIS. If you create a TEXT field in a SQL Server database from ArcGIS, either NVARCHAR or NVARCHAR(MAX) is used.
In an enterprise geodatabase, if the UNICODE_STRING configuration parameter is set to FALSE and the text field is 7,999 characters or fewer, VARCHAR is used.
If the UNICODE_STRING configuration parameter is set to FALSE and the text field is 8,000 characters or more, VARCHAR(MAX) is used.
If the UNICODE_STRING configuration parameter is set to TRUE and the text field is 3,999 characters, NVARCHAR is used.
If the UNICODE_STRING configuration parameter is set to TRUE and the text field is 4,000 characters or more, NVARCHAR(MAX) is used.
Geometry data types
As indicated in the table, ArcGIS creates and can work with three geometry data types in SQL Server: compressed binary, SQL Server geometry, and SQL Server geography. For information on using geometry or geography with ArcGIS, see SQL Server spatial types and ArcGIS.
Compressed binary
The Esri compressed binary storage type uses a binary storage mechanism for storing feature geometry. Compressed binary geometry storage can only be used in geodatabases. A compressed binary feature class is made up of three tables: the business table, feature table, and spatial index table.
After verifying the geometry, the client application compresses and sends it to the geodatabase, where it is stored in compressed binary format in a feature table, or F table. Compressing the geometry on the client unloads the task from the database server and reduces the transmission time to send the geometry. It also offers efficient storage and retrieval of spatial data by reducing the space required to store data by as much as 40 percent.
The business table contains attributes and a spatial column. The spatial column is a key to the feature and spatial index tables.
The relationship between the business table and feature table is managed through the spatial column and the feature ID (FID) column. This key, which is maintained by ArcGIS, is unique.
SQL Server geometry
The following is a summary of the geometry type. For more information on the geometry type and how to use it, see Microsoft SQL Server documentation.
- The geometry type supports any X/Y coordinate system.
- Planar (flat-Earth, Euclidean) calculations and straight-line interpolation between vertices are used for rendering and spatial comparisons.
- Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.9, and compliant with SQL MM, an ISO standard.
SQL Server geography
The following is a summary of the geography type. For more information on the geography type and how to use it, see Microsoft SQL Server documentation.
- The geography type supports many standard geographic coordinate systems, such as GPS latitude and longitude.
Microsoft requires that you use SRIDs and the geographic coordinate systems defined in the SQL Server data dictionary.
- An ellipsoid (round Earth) model and Great Elliptic interpolation of line segments between vertices are used for calculations and spatial comparisons.
- Uses a global (spheroidal) layer extent.
Coordinates of the data cannot exceed global extent.
- In SQL Server 2008 and 2008 R2, features must be smaller than a single hemisphere.
- When used with ArcGIS, features cannot have z- or m-coordinates if you use SQL Server 2008 or 2008 R2.
Raster data types
The default raster type used in geodatabases in SQL Server is an integer type. If you want to access the geodatabase raster using SQL, you can configure your enterprise geodatabase in SQL Server to use the ST_Raster type.
For in-depth information on how the ST_Raster object type is defined, see The ST_Raster data type.