A quick tour of views in the geodatabase

There are three types of views you can use in an ArcSDE geodatabase:

• Database views (or nonspatial views)
• Spatial views
• Multiversioned views

License:

Views can only be used in ArcSDE geodatabases licensed through ArcGIS Server Enterprise.

Database views

Database views are stored queries that select data from specified nonversioned, nonspatial tables. When used with ArcSDE, 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, create a multiversioned view on the business table instead.

You can use the native SQL of your database management system (DBMS) to create a database view or the ArcSDE sdetable command with the create_view operation.

The syntax of sdetable –o create_view is as follows:

sdetable –o create_view  
–T <view_name> 
–t <table1,table2...tablen>     
–c <table_col1,table_col2...table_coln
[–a <view_col1,view_col2...view_coln>] 
[–w <"where_clause">]
[–i {<direct connection> | <service> | <port#>}] 
[–s <server_name>] 
[–D <database>]
–u <DB_User_name> [–p <DB_User_password>] [–N] [–q]

You must list the columns (–c) you want in the view. For an explanation of the sdetable command and its operations and options, see the ArcSDE Administration Command Reference.

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. If you do not have proper privileges in the database to create a view, you cannot execute the sdetable –o create_view command. Additionally, 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 colums 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.

You can use either the ArcSDE sdetable command with the create_view operation or SQL to create a spatial view. If you use the sdetable command, be sure to include the row ID (or ObjectID) and spatial column in the list of columns specified with the –c option. 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. After creating the view in SQL, you must register it with at least ArcSDE for it to appear as a feature class in ArcGIS clients.

When you create a view using ArcSDE tools, 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 ArcSDE 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 ArcSDE 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.

Multiversioned views

Multiversioned 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.

Use the sdetable command with the create_mv_view operation to create a multiversioned view. The syntax of sdetable -o create_mv_view is as follows:

sdetable –o create_mv_view 
–T <view_name> –t <table_name> 
[–i {<direct connection> | <service> | <port#>}] 
[–s <server_name>] [–D <database>] 
–u <DB_User_name> [–p <DB_User_password>] [–N] [–q]

For an explanation of the sdetable command and its operations and options, see the ArcSDE Administration Command Reference.

Multiversioned 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 multiversioned view.

The primary use of multiversioned views is to edit versioned data using SQL. You cannot modify the underlying table or feature class through a multiversioned view using an ArcGIS client application. The table or feature class upon which the multiversioned 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.