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.
For information about the relationship of spatial indexes to partition and to filegroups, see the "Remarks" section in CREATE SPATIAL INDEX (Transact-SQL).
To create a spatial index
To alter a spatial index
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).
To drop a spatial index
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.
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.
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.