Enterprise geodatabases support database views, versioned views, and archive views. You can register database views with the geodatabase to store metadata and information about the views in geodatabase system tables or keep the views as database objects.
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 object ID column from the same table as the spatial column. If you don't include the object ID from the feature class, it violates the unique relationship between the object ID and shape column, resulting in inaccurate results when the view is queried or rendered in ArcGIS.
You cannot edit data in 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 to create a database view, or use the native SQL of your database management system.
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 Microsoft 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.
- You cannot edit data through a database view in ArcGIS Desktop.
- Views are not supported in ArcGIS Server feature services.
- When you create a view on a geodatabase table or feature class that participates in traditional versioning, 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. 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 object ID of the feature class that contains the spatial column in the view definition.
Views registered with the geodatabase
When your database contains a geodatabase, you can register database views 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 information, such as the geometry type, spatial reference, and extent, results in improved performance when you add a view that contains a spatial field to a map.
- You can define metadata on views that are registered with the geodatabase if you are the view owner.
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. You must include the feature class's object ID in the view definition to register the view with the geodatabase.
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 that allow you to use SQL to access or edit a specified traditional geodatabase version of a table or feature class.
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 traditional 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 for traditional versioning. Versioned views are only supported with traditional versioning.
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 traditional 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.