Create, Modify, and Drop Spatial Indexes

  • Article

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.

Note

For information about the relationship of spatial indexes to partition and to filegroups, see the "Remarks" section in CREATE SPATIAL INDEX (Transact-SQL).

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

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.

  3. Expand the table for which you want to create the index.

  4. Right-click Indexes and select New Index.

  5. In the Index name field, enter a name for the index.

  6. In the Index type drop-down list, select Spatial.

  7. To specify the spatial column that you want to index, click Add.

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

  9. Verify your column selection in the Index key columns grid.

  10. In the Select a page pane of the Index Properties dialog box, click Spatial.

  11. 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 (11.x) 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.

    For more information about these properties, see Index Properties F1 Help.

  12. Click OK.

Note

To create another spatial index on the same or a different spatial column, repeat the preceding steps.

To create a spatial index by using Table Designer in Management Studio

To create a spatial index in Table Designer

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

  2. Select a geometry or geography column for the index.

  3. On the Table Designer menu, click Spatial Index.

  4. In the Spatial Indexes dialog box, click Add.

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

To alter a spatial index

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 Index

To drop a spatial index by using Table Designer in Management Studio

To drop a spatial index in Table Designer

  1. In Object Explorer, right-click the table with the spatial index you want to delete and click Design.

    The table opens in Table Designer.

  2. On the Table Designer menu, click Spatial Index.

    The Spatial Index dialog box opens.

  3. Click the index you want to delete in the Selected Spatial Index column.

  4. Click Delete.

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.

Note

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

Spatial tessellations introduced in SQL Server 2012 (11.x) 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.

See Also

Spatial Indexes Overview