You can create database views in enterprise geodatabases. You can register the 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.
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 object IDs. This leads to unexpected behavior in applications such as ArcMap and ArcGIS Pro and in web services, because they require that the object ID contain unique values.
- 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.
- 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.