Views in SQL Server
Views in SQL Server
|
| Release 9.3 |
   |
Note:This topic was updated for 9.3.1.
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
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.
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 correctly qualify the tables involved in your queries and use the –D (database) switch to identify which database contains the data on which to create a view.
In the single spatial database model, you cannot have cross-database views between two feature classes in different databases. However, you can create a cross-database view between a feature class in one database and a table in another.
To create a cross-database view involving a feature class and a table, create a simple ArcSDE view of the feature class, then alter it in the SQL Query Analyzer to query the tables in the other database.
For example, you can create a simple ArcSDE view with the sdetable command:
sdetable –o create_view –T taxpars –t taxlots –c taxlots.shape
Then alter this view in Query Analyzer to add an American National Standards Institute (ANSI) join to another table as follows:
ALTER VIEW taxpars
AS
SELECT tx.shape, p.owner1, p.owneradd, p.ownercity,
p.ownerstat, p.bldgval, p.landval, p.totalval,
p.bldgsqft
FROM taxlots tx
inner join parcelinfodb.dbo.taxlotinfo p on tx.shape = p.shape
Creating indexed views (SQL Server 2000 and 2005)
SQL Server 2000 and 2005 Enterprise Edition allow you to create unique clustered indexes on views and partition views across multiple servers.
Indexed views are stored in the database in the same manner as a table, while nonindexed views store only the SQL statements comprising them. Indexed views are advantageous when your view definition employs a complex join. They are a good choice to use on static data, for they are schema bound to their source. Also, they are stored in the same way as a regular table with a clustered index. Before you try creating indexed views, you should become familiar with this topic in the SQL Server Books Online documentation.
Creating an indexed view in SQL Server 2000 involves extra work and has many requirements. These are summarized as follows:
- The view must be created with Schemabinding.
- The view must be created with QUOTED_IDENTIFIER on.
- The view cannot reference other views.
- Tables referenced in views must be two part.
- You cannot reference all columns of a table with "*".
There are additional rules and conditions. For more information, refer to the topic "Creating an Indexed View" in the SQL Server Books Online documentation.
Since ArcSDE does not create schema-bound views by default, follow this procedure:
- Create an ArcSDE view with sdetable –o create_view.
- Open SQL Server Query Analyzer and drop the business table view with DROP VIEW <view name>. Before you do this, be sure to copy the view definition.
- Re-create the business table view with the create view statement but be sure to use the WITH SCHEMABINDING option and qualify your view name and table name with the owner.<table or view name> convention.
- Create a unique clustered index on the view with the CREATE UNIQUE CLUSTERED INDEX statement.
This is an example that creates an indexed view. This example is done in SQL Server 2000 using a SQL Server authenticated user.
- Create a view with sdetable –o create_view:
sdetable –o create_view –T vwWithIndx –t cagis_cent
–c parcelid,object__id,x_coord,y_coord,book,page,parcel,shp
–w "parcel>0393" –D parcels
- In Query Analyzer, copy the view statement and drop the business table view:
drop view vwWithIndx
- Re-create the view and be sure to qualify the view and table names with the owner name, using the WITH SCHEMABINDING statement:
CREATE VIEW vtest.vwWithIndx
WITH SCHEMABINDING AS SELECT parcelid, object__id, x_coord, y_coord, book, page, parcel, shp FROM vtest.cagis_cent WHERE parcel>0393
- Create a unique clustered index on this view:
create unique clustered index shpIdxView on vtest.vwWithIndx (shp)
