Views in SQL Server

Creating views can improve query response times by restricting the number of tables and columns searched when a data request is made. You can create views that appear to client applications as feature classes or nonspatial tables. An ArcSDE view can reference a single table or feature class, or you can create views to join data from multiple tables. If your geodatabase is stored using the multiple spatial database model, you can create views between tables and feature classes in different databases on the same SQL Server instance.

Any user that has the required permissions can create a view using SQL or the ArcSDE sdetable command. For information on privileges needed to create views, consult the SQL Server documentation for your release.

View definitions can be accessed through the INFORMATION_SCHEMA.VIEWS system view in Microsoft SQL Server databases.

The INFORMATION_SCHEMA.VIEWS system view

You can obtain metadata for a view by querying the INFORMATION_SCHEMA.VIEWS system view for the particular view.

Field name	Data type	Description*
TABLE_CATALOG	nvarchar(128)	The view qualifier
TABLE_SCHEMA	nvarchar(128)	The schema that contains the view
TABLE_NAME	nvarchar(128)	The name of the view
VIEW_DEFINITION	nvarchar(4000)	The view definition text
CHECK_OPTION	varchar(7)	The type of WITH CHECK OPTION with which the view was created, if any
IS_UPDATABLE	varchar(2)	Indicates if the view can be updated

*For a complete description of the fields and any caveats, see the Microsoft documentation for the release of SQL Server you are using.

Cross-database views

In the multiple spatial database model, it is possible to create views between tables and ArcSDE feature classes that do not reside in the same database. When you do this, you must use the –D (database) switch to identify in which database the view is to be created and use the –t option to list all the tables participating in the view. The table names must be fully qualified to include the database and owner names.

Note:

To migrate to the single spatial database model, you must drop these views. You can re-create them after you have migrated your data.

In the single spatial database model, you cannot use the sdetable command with the create_view operation to create cross-database views between two feature classes in different databases. To create such views, you must use SQL.