Views in an enterprise geodatabase

There are four types of views you can use in an enterprise geodatabase:

• Database views (or nonspatial views)
• Spatial views
• Versioned views
• Archive views

Database views

Database views are stored queries that select data from specified nonversioned, nonspatial tables. You can define views on database or enterprise geodatabase tables. When used with an enterprise geodatabase, database views can be defined for a single nonversioned table or between two nonversioned tables. Or you can create more complex views that contain subqueries or span databases.

The following are some reasons you would use database views:

• Views can provide subsets of rows or columns in a predefined way. This reduces the volume of data transferred from the database to the client, which can improve performance.
• Views allow you to codify common queries in the database and make them available to multiple users. This reduces the need for users to construct their own complex queries.
• Views can join data from multiple business tables or other views.
• You can use aggregation functions to summarize data in views. This also reduces the volume of data transferred from the database to the client, which can improve performance.
• You can grant users SELECT privileges to views in the same way as to tables. Therefore, you can use views to control access to rows or columns in business tables.

You can use the Create Database View tool or the database context menu in ArcGIS for Desktop to create a database view, or use the native SQL of your database management system (DBMS).

Views exist as objects in the database and are not registered with the geodatabase. Changes that are made to the schema of the underlying table or tables do not get reflected in the view. To include additional columns in a view, you must redefine the view to include those columns.

Specific permissions are needed to allow a user to create a view; for example, the user must be granted CREATE VIEW privileges in Oracle or SQL Server databases. With some database management systems, if you create a view on a feature class or table for which you are not the owner, you cannot grant other users' rights to the view unless the owner of the underlying tables has given you permission to grant privileges to other users.

When you create a view on a versioned dataset, you only see the business (base) table and not the edits in the delta tables. To see the edits in the delta tables, use a versioned view on the business table instead.

Be aware that you cannot edit data through a database view in ArcMap or an ArcGIS feature service.

Spatial views

Spatial views are database views that contain a single spatial column.

In addition to the reasons listed above for using database views, some reasons to create spatial views include the following:

• You can join a feature class with a nonspatial table when you define the spatial view, thereby combining columns from both.
• ArcGIS clients can only work with tables that contain one spatial column. To use a spatial table that contains multiple spatial columns, you can create a spatial view that includes just one spatial column from the table.
• ArcGIS clients can only work with tables that use one spatial reference. If your table contains multiple spatial references, you can create a view that only includes those records that use the same spatial reference.

You can use the Create Database View tool or SQL to create a spatial view if your feature class uses an SQL geometry type and if the feature class is not registered as versioned. To create a spatial view, include the spatial column and the feature class's ObjectID in the view definition.

When you create a spatial view, you must include the ObjectID column from the same table as the spatial column. If the ObjectID from the feature class is not included, the unique relationship between the ObjectID and shape column may be violated, resulting in inaccurate results when queried or rendered in ArcGIS.

As with other database views, spatial views are not registered with the geodatabase.

Note:

If you choose to replace views created by ArcGIS by altering the view afterward using SQL or other database tools, you are taking responsibility for not only the query's correctness and efficiency but also its relevance and suitability for use with ArcGIS clients. For example, creating one-to-many views involving feature classes results in duplicate ObjectIDs. This leads to unexpected behavior in applications such as ArcMap and ArcCatalog, because the ObjectID is supposed to contain unique values.

Versioned views

Versioned views incorporate database views, stored procedures, triggers, and functions to access or edit a specified version of a table or feature class in a geodatabase using SQL.

Reasons to have versioned views include the following:

• Versioned views allow you to see the edits that are currently stored in the table's delta tables.
• Versioned views allow you to edit versioned tables and feature classes using SQL.

Beginning with ArcGIS 10.1, versioned views are automatically created for tables and feature classes when you register them as versioned. If you have existing versioned data, you can create a versioned view by right-clicking the versioned table, feature class, or feature dataset in the Catalog tree of ArcGIS for Desktop, pointing to Manage, and clicking Enable SQL Access.

Versioned views only work with an individual versioned table or feature class. You cannot use a where clause to join multiple tables together or restrict which rows or columns are included in a versioned view.

The primary use of versioned views is to edit versioned data using SQL. You cannot modify the underlying table or feature class through a versioned view using an ArcGIS client application. The table or feature class upon which the versioned view is based must meet the ArcGIS requirements for editing geodatabase data with SQL. See What type of data can be edited using SQL? for more information.

Archive views

An archive view is a database view defined on a nonversioned, archive-enabled table or feature class. Archive views also include triggers that keep the archiving tables up-to-date when edits are made through the archive view. An archive view is created when you enable the dataset for archiving or when you enable SQL access on a nonversioned, archive-enabled dataset.

Reasons to have archive views include the following:

• Archive views allow you to see the data in an archive-enabled table's history table.
• Archive views allow you to use SQL to edit tables and feature classes that have archiving enabled.

Archive views only work with an individual table or feature class. You cannot use a where clause to join multiple tables together or restrict which rows or columns are included in an archive view.