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.

NoteNote:

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.


8/19/2013