A quick tour of views in the 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.

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.

You can use ArcGIS for Desktop or the native SQL of your database management system (DBMS) to create a database view.

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.

Spatial views

Spatial views are database views that contain a single spatial column. They can only be created on spatial tables that have not been registered with the geodatabase or, if the tables have been registered with the geodatabase, on nonversioned feature classes.

In addition to the reasons for using database views listed in What is a view?, some reasons to create spatial views include:

• 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 ArcGIS or SQL to create a spatial view. The row ID or ObjectID column must be from the same table as the spatial column. If it is not, the unique relationship between the row ID or ObjectID and shape column may be violated, resulting in inaccurate results when queried or rendered in ArcGIS. To create a spatial view using SQL, include the spatial column and qualifying row ID or ObjectID column from the feature class or spatial table in the view.

Note:

When you create a view using SQL or other DBMS tools, you must take into consideration the appropriateness of the view. For example, you must determine whether the specified columns actually exist in the specified tables and if there are indexes on the columns to speed query execution. If you choose to replace views created by ArcGIS by altering the view afterward, 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.

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 data that is already versioned, 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, clicking 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.