Create, Modify, and Drop Spatial Indexes

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

[TOP]

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

[TOP]

• ALTER INDEX (Transact-SQL)

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

• Modify an Index

• Move an Existing Index to a Different Filegroup

[TOP]

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.

[TOP]

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.

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.

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.

[TOP]