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.
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 or higher, 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.
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).
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.
Spatial tessellations introduced in SQL Server 2012 cannot be replicated to SQL Server 2008 R2 or SQL Server 2008. You must 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.