SQL access to enterprise geodatabase data

You can use the native SQL of your database management system to read data from the tables and feature classes (collectively referred to as datasets) in an enterprise geodatabase. You can also use SQL to insert into, delete from, and update values in the simple datasets stored in your enterprise geodatabase.

Note:

Do not use SQL to alter the schema of datasets stored in your enterprise geodatabase.

When you execute SQL against a geodatabase dataset, you are querying the data in the base (or business) table. If the dataset you are querying meets the following criteria, querying the base table is sufficient to see the data, and you can update data, insert records to, or delete records from the base table using SQL:

• The dataset does not participate in geodatabase functionality such as annotation, replication, networks, parcel fabrics, relationship classes, schematic datasets, subtypes, domains, terrains, or topology.
• The dataset is not registered as versioned.
• The dataset is not enabled for archiving.
• The dataset does not contain a binary geometry data type or ArcSDE XML data type column.

The following sections describe what you should do if your dataset does not meet these criteria.

If the dataset participates in geodatabase functionality

If the data you want to access participates in geodatabase functionality, you must construct your query to include the required associated tables and join them using the correct columns to see the information in the associated tables.

You can use the Is_Simple function to determine if your dataset participates in geodatabase functionality. If Is_Simple returns False, you can execute SQL SELECT statements against the dataset, but do not edit the dataset using SQL.

If the dataset is registered as versioned

If your dataset is registered as versioned, you can use a versioned view to query it and see the data in the base, adds, and deletes tables without having to write complex join statements. In ArcGIS 10.1 and later releases, a versioned view is created when you register the dataset as versioned. For datasets that were versioned prior to ArcGIS 10.1 or were versioned when you did not have privileges to create a view, you can enable it for SQL access from the table or feature class context menu in ArcGIS for Desktop, thereby creating a versioned view.

If you want to edit a versioned dataset using SQL, you must edit it through a versioned view. This writes the edits to the adds and deletes tables and populates the ObjectID field when records are inserted.

You can use the Is_Versioned function to determine whether or not your dataset is registered as versioned. If it is registered as versioned, use the Version_View_Name function to determine the name of the versioned view.

If the dataset is not registered as versioned but is enabled for archiving

If your dataset is not registered as versioned but is enabled for archiving, an archive view is created when archiving is enabled.

If you want to use SQL to edit a nonversioned dataset that is enabled for archiving, you must edit it through the archive view. Doing so automatically updates the fields that track when a feature or record was updated and the ObjectID field.

Tip:

If you did not have privileges to create a view when archiving was enabled, you can enable SQL access (thereby creating an archive view) from the table or feature class context menu in ArcGIS for Desktop after you have been granted create view privileges in the database.

If the Is_Versioned function returns False, use the Is_Archive_Enabled function to determine if your nonversioned dataset is enabled for archiving. If it is enabled for archiving, use the Archive_View_Name function to determine the name of the archive view, which you can use to edit the dataset using SQL.

If the dataset contains a binary geometry or ArcSDE XML column

If the data you want to access uses binary geometry storage or ArcSDE XML, you must construct your query to include the required associated tables and join them using the correct columns to see the information in the associated tables.

Do not use SQL to edit datasets that contain a binary geometry storage or ArcSDE XML data type columns.