A quick tour of using SQL with ArcSDE geodatabases

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

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

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 ArcSDE, the geodatabase, or ArcGIS clients are not enforced when you use SQL.

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.

Additionally, if your DBMS requires a client to access the database, you must install and configure the DBMS client before you can connect directly to the database.

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 ArcSDE geodatabases, you must use multiversioned views.

Multiversioned views incorporate database views, stored procedures, triggers, and functions to access a specified version of data in a geodatabase table using Structured Query Language (SQL).

When a versioned dataset is accessed through a multiversioned 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. 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.

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.

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 ArcSDE geodatabases. However, you should not edit datasets that participate in geodatabase functionality, as mentioned previously in this topic.

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

You must use multiversioned views to edit versioned data. This ensures that the inserts are made to the delta tables while editing. Mulitversioned 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 ArcSDE and the geodatabase. Remember, though, once the tables use geodatabase functionality, you cannot edit them using SQL.

See Example: Registering a spatial table with ArcSDE, Example: Registering a layer with a geodatabase, and Workflow: Creating a table with SQL and registering it with ArcSDE and the geodatabase for instructions.