A quick tour of using SQL with enterprise geodatabases

You can use SQL to access existing datasets and their properties and edit both versioned and nonversioned datasets in enterprise geodatabases.

You can also use SQL to create tables that can be registered with the geodatabase. These tables can contain nonspatial attributes and SQL spatial or raster types.

When you access a geodatabase using SQL, you are accessing it at the database management system (DBMS) level. That means behaviors and functionality enforced by ArcGIS are not enforced when you use SQL. It also means you must install and configure the DBMS client on the computers from which you will connect directly to the database.

As far as SQL is concerned, you are accessing tables in a database. Therefore, when you execute SQL commands on data in the geodatabase, you can view data and information about data but should not alter any data that participates in geodatabase behavior. See What type of data can be edited using SQL? for more information.

Using SQL to access the properties of datasets

You can use SQL SELECT statements to obtain the properties of existing datasets in ArcSDE geodatabases.

Dataset properties are stored in the geodatabase system tables. To get this information, you query specific columns in the system tables. In some cases, these columns contain XML documents that you query using XPath expressions to get the property information. This is true for geodatabases stored in IBM DB2, PostgreSQL, and Microsoft SQL Server databases. For geodatabases in Oracle, you can access system views to read the plain text in a CLOB column.

Using SQL to access geodatabase data

You can use SQL SELECT statements to return data from existing datasets in geodatabases. If you query versioned datasets in enterprise geodatabases, you must use versioned views.

Versioned views incorporate database views and stored procedures, triggers, and functions to allow you to read or edit versioned data in a geodatabase table or feature class using SQL. When a versioned dataset is accessed through a versioned view, all the records in the business table are selected and merged with records from the delta tables to construct a view that includes all the changes made to the business table in the context of the specified version.

To access and analyze simple spatial data with SQL, use the spatial SQL functions that are installed with the spatial type. Each spatial type has its own set of functions. Esri has defined functions for its ST_Geometry type in Oracle and PostgreSQL, and each DBMS vendor has defined functions for its SQL types. These functions evaluate spatial relationships, perform spatial operations, and return and set spatial properties. See Spatial relationships, Spatial operations, and Geometry properties for an overview of this functionality.

Editing geodatabase data using SQL

You can use the native SQL of your DBMS to edit the simple, nonspatial attribute data in the geodatabase. You can also use the spatial SQL functions installed with spatial types to alter simple spatial data using SQL.

You can use SQL to edit both versioned and nonversioned datasets in enterprise geodatabases. However, as mentioned previously, you should not edit datasets that participate in geodatabase functionality.

When you edit nonversioned data, you must insert unique values to object ID, GUID, and global ID fields using SQL.

You must use versioned views to edit versioned data. The triggers used by versioned views update the delta tables when you edit through a versioned view. This ensures that the insertions are made to the delta tables while editing. Versioned views also insert unique values to object ID fields automatically.

Creating tables with SQL to be used with ArcGIS

You can use the native SQL of your DBMS to create and populate tables. Both spatial and nonspatial tables you create with SQL can be used with ArcMap. You can render spatial tables in ArcMap by creating query layers. The attributes in both spatial and nonspatial tables also can be viewed directly in ArcGIS. Both query layers and the ability to directly access tables are useful if you have tables that work with another system at your site and you want to be able to access those tables through ArcGIS or join them to tables in your geodatabase.

If you want your tables to use geodatabase functionality, register the table with the geodatabase. Remember, though, that once the tables use geodatabase functionality, you cannot edit them using SQL.

See Workflow: Creating a table with SQL and registering it with the geodatabase for instructions.