Views in an enterprise geodatabase

Database views

Database views are stored queries that select data from specified tables. You can define views on database or enterprise geodatabase tables that are not registered as versioned. 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.

Views used with ArcGIS can contain one spatial column. If a spatial column is present, these views can be referred to as spatial views. Note that when you create a spatial view, you must include the ObjectID column from the same table as the spatial column. If you don't include the ObjectID from the feature class, it violates the unique relationship between the ObjectID and shape column, resulting in inaccurate results when the view is queried or rendered in ArcGIS.

You cannot edit database views through ArcGIS clients or services.

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 different 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 tables.
• You can join a feature class with a nonspatial table when you define a view, thereby combining columns from both.
• ArcGIS 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 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 geoprocessing tool or the database context menu in ArcGIS Desktop to create a database view, or use the native SQL of your database management system (DBMS).

Specific database 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.

Be aware of the following when working with database views:

• Views exist as objects in the database and are not registered with the geodatabase, even when you define them on geodatabase tables using the Create Database View tool.
• Changes made to the schema of the underlying table or tables are not reflected in the view. To include additional columns in a view, you must redefine the view to include those columns.
  Caution:

  If you choose to replace views created by ArcGIS by altering an existing view using SQL or other database tools, you take 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 ArcGIS Pro and in web services, because they require that the ObjectID contain unique values.

• You cannot edit data through a database view in ArcGIS Desktop or an ArcGIS feature service.
• When you create a view on a versioned geodatabase table or feature class, you only see the data in the base table and not the edits in the delta tables. To see edits in a view through ArcGIS clients, you need to reconcile and post the edits to the Default version and compress the geodatabase. If you want to see the edits in a view through SQL, third-party, or custom clients, use the versioned view that is automatically created when you register a geodatabase table or feature class as versioned.
• If you include a spatial column in your view definition, the spatial column must be an SQL geometry data type, the feature class the spatial column comes from cannot be registered as versioned, and you must also include the ObjectID of the feature class that contains the spatial column in the view definition.

Views registered with the geodatabase

If your database contains a geodatabase, you can use the Create Database View tool or SQL to create a database view, and register that view with the geodatabase using the Register With Geodatabase geoprocessing tool.

Some reasons to register a view with the geodatabase include the following:

• When you register a view with the geodatabase, information about the view is stored in geodatabase system tables. This is especially beneficial when you register a spatial view with the geodatabase, as you define the geometry type, spatial reference, and extent when you register the spatial view, which results in improved performance when adding the spatial view to a map.
• You can define metadata on views that are registered with the geodatabase.
• You can publish a registered view in a query-only feature service.

You can include a single spatial column in the view you create 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 and then register it.

Views that are registered with the geodatabase cannot be edited through ArcGIS clients or services.

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 incorporate 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 or third-party apps.

ArcGIS clients automatically create versioned views for tables and feature classes when you register them as versioned.

Tip:

The tables or feature classes you registered as versioned in geodatabases prior to ArcGIS 10.1 did not automatically have an associated versioned view. If you have such versioned data and want a versioned view for it, you can create one by right-clicking the versioned table, feature class, or feature dataset in the ArcMap Catalog tree, pointing to Manage, and clicking Enable SQL Access.

Versioned views work with all data in 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.

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 access to 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 work with all data in 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.