Create, Modify, and Drop Spatial Indexes
A spatial index can more efficiently perform certain operations on a column of the geometry or geography data type (a spatial column). More than one spatial index can be specified on a spatial column. This is useful, for example, for indexing different tessellation parameters in a single column.
There are a number of restrictions on creating spatial indexes. For more information, see Restrictions on Spatial Indexes in this topic.
[!UWAGA]
For information about the relationship of spatial indexes to partition and to filegroups, see the "Remarks" section in CREATE SPATIAL INDEX (Transact-SQL).
In This Topic
Creating, Modifying, and Dropping Spatial Indexes
To create a spatial index
To alter a spatial index
To drop a spatial index
Restrictions on Spatial Indexes
Creating, Modifying, and Dropping Spatial Indexes
To create a spatial index
To create a spatial index by using Transact-SQL
CREATE SPATIAL INDEX (Transact-SQL)To create a spatial index by using the New Index dialog box in Management Studio
To create a spatial index in Management Studio
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.
Expand the table for which you want to create the index.
Right-click Indexes and select New Index.
In the Index name field, enter a name for the index.
In the Index type drop-down list, select Spatial.
To specify the spatial column that you want to index, click Add.
In the Select Columns from <table name> dialog box, select a column of type geometry or geography by selecting the corresponding check box. Any other spatial columns then become uneditable. If you want to select a different spatial column, you must first clear the currently selected column. When finished, click OK.
Verify your column selection in the Index key columns grid.
In the Select a page pane of the Index Properties dialog box, click Spatial.
On the Spatial page, specify the values that you want to use for the spatial properties of the index.
When creating an index on a geometry type column, you must specify the (X-min,Y-min) and (X-max,Y-max) coordinates of the bounding box. For an index on a geography type column, the bounding-box fields become read-only after you specify the Geography grid tessellation scheme, because geography grid tessellation does not use a bounding box.
Optionally, you can specify nondefault values for the Cells Per Object field and for the grid density at any level of the tessellation scheme. The default number of cells per object is 16 for SQL Server 2008 or 8 for SQL Server 2012, and the default grid density is Medium for SQL Server 2008.
You can select GEOMETRY_AUTO_GRID or GEOGRAPHY_AUTO_GRID for tessellation scheme in SQL Server. When GEOMETRY_AUTO_GRID or GEOGRAPHY_AUTO_GRID is selected, then Level 1, Level 2, Level 3, and Level 4 grid density options are disabled.
For more information about these properties, see Index Properties F1 Help.
Click OK.
[!UWAGA]
To create another spatial index on the same or a different spatial column, repeat the preceding steps.
[TOP]
To create a spatial index by using Table Designer in Management Studio
To create a spatial index in Table Designer
In Object Explorer, right-click the table for which you want to create a spatial index, and then click Design.
The table opens in Table Designer.
Select a geometry or geography column for the index.
On the Table Designer menu, click Spatial Index.
In the Spatial Indexes dialog box, click Add.
Select the new index in the Selected Spatial Index list, and in the grid to the right, set the properties for the spatial index. For information about the properties, see Spatial Indexes Dialog Box (Visual Database Tools).
[TOP]
To alter a spatial index
-
.gif "Ważna informacja")
Ważne:To change options that are specific to a spatial index, such as BOUNDING_BOX or GRID, you can either use a CREATE SPATIAL INDEX statement that specifies DROP_EXISTING = ON, or drop the spatial index and create a new one. For an example, see CREATE SPATIAL INDEX (Transact-SQL).
[TOP]
To drop a spatial index
To drop a spatial index by using Transact-SQL
DROP INDEX (Transact-SQL)To drop an index by using Management Studio
Delete an IndexTo drop a spatial index by using Table Designer in Management Studio
To drop a spatial index in Table Designer
In Object Explorer, right-click the table with the spatial index you want to delete and click Design.
The table opens in Table Designer.
On the Table Designer menu, click Spatial Index.
The Spatial Index dialog box opens.
Click the index you want to delete in the Selected Spatial Index column.
Click Delete.
[TOP]
Restrictions on Spatial Indexes
A spatial index can be created only on a column of type geometry or geography.
Table and View Restrictions
Spatial indexes can be defined only on a table that has a primary key. The maximum number of primary key columns on the table is 15.
The maximum size of index key records is 895 bytes. Larger sizes raise an error.
[!UWAGA]
Primary key metadata cannot be changed while a spatial index is defined on a table.
Spatial indexes cannot be specified on indexed views.
Multiple Spatial Index Restrictions
You can create up to 249 spatial indexes on any of the spatial columns in a supported table. Creating more than one spatial index on the same spatial column can be useful, for example, to index different tessellation parameters in a single column.
You can create only one spatial index at a time.
Spatial Indexes and Process Parallelism
An index build can use available process parallelism.
Version Restrictions
New spatial tessellations for SQL Server 2012 cannot be replicated to SQL Server 2008 R2 or SQL Server 2008. You have to use SQL Server 2008 R2 or SQL Server 2008 spatial tessellations for spatial indexes when backward compatibility with SQL Server 2008 R2 or SQL Server 2008 databases is a requirement.
[TOP]