Views in an enterprise geodatabase

Database views are stored queries that select data from the tables you specify in the view definition. Views can include a single table, multiple tables, and subqueries.

Views exist as objects in the database and require specific database permissions to allow a user to create them. Read the documentation for your database management system to understand what is or is not supported for views in that type of database.

You can use the Create Database View geoprocessing tool or the New > View option on the database connection context menu to create a database view in an enterprise geodatabase, or use the native SQL of the database management system.

Note:

Views are not automatically registered with the geodatabase, even if the view is defined on geodatabase tables and feature classes and even if you create the view using the Create Database View geoprocessing tool. The view is not registered with the geodatabase unless you explicitly register it.

Reasons to use views

The following are some reasons to create database views in a geodatabase:

  • 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.
  • Limiting the columns or rows present in a view also allows you to control what data users see. For example, employees concerned with assessing inventory and ordering products do not need to see the names of the people who purchased products or the sales price. Rather than give those employees access to the entire table where that information is stored, you can create a view that does not include those columns and grant the inventory employees select privilege on the view.
  • Limiting columns and rows also allows you to meet ArcGIS data requirements. For example, ArcGIS can only work with tables that contain one spatial column. To use a spatial table that contains multiple spatial columns, you can create views. Each view can contain a single spatial column. Similarly, ArcGIS can only work with tables that use one spatial reference. If the table contains data stored in multiple spatial references, you can create a view that only includes those rows (features) that use the same spatial reference.
  • 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 join a feature class with a nonspatial table when you define a view, thereby combining columns from both.

Considerations when using views

Be aware of the following when working with views in ArcGIS:

  • You cannot edit the data through the view using ArcGIS clients or services, even if you register the view with the geodatabase.
  • 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 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 ArcGIS AllSource and in web services, because they require that the object ID contain unique values.

  • In most cases, the geodatabase tables and feature classes you include in a view should not be registered for traditional versioning or enabled for archiving. 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 or the records stored in the archiving tables. To see edits in a view through ArcGIS clients, you must reconcile and post the edits to the default version and fully compress the geodatabase.
  • The following requirements must be met when you include a spatial column in the definition of a view used in ArcGIS:
    • Only one spatial column can be present in the view.
    • The view definition must include the object ID column from the same feature class 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.

Views registered with the geodatabase

After you create a view in a geodatabase, you can run the Register With Geodatabase geoprocessing tool to make the view a geodatabase object.

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.