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.
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 certain 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 to use with traditional versioning, 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. A versioned view is created when you register the dataset as versioned.
If you want to edit a traditional 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 to participate in traditional versioning. If it is, use the Version_View_Name function to determine the name of the versioned view.
If your data is registered to use branch versioning, do not edit it using SQL.
If the dataset is not registered as versioned but is enabled for archiving
If your dataset is not registered to participate in traditional versioning 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 Object ID field.
If the Is_Versioned function returns False and you know for sure the dataset is not registered to participate in a branch versioin, use the Is_Archive_Enabled function to determine if your 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 query 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.