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