| title | description | author | ms.author | ms.date | ms.service | ms.topic | helpviewer_keywords | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Create, Modify, and Drop Spatial Indexes |
Create, Modify, and Drop Spatial Indexes |
MladjoA |
mlandzic |
03/14/2017 |
sql |
conceptual |
|
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance] 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).
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
-
In Object Explorer, connect to an instance of the [!INCLUDEssDEnoversion] 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 [!INCLUDEsql2008-md] or 8 for [!INCLUDEssSQL11] or higher, and the default grid density is Medium for [!INCLUDEsql2008-md].
You can select GEOMETRY_AUTO_GRID or GEOGRAPHY_AUTO_GRID for tessellation scheme in [!INCLUDEssNoVersion]. 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.
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
-
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).
-
[!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).
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
-
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.
A spatial index can be created only on a column of type geometry or geography.
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.
Spatial tessellations introduced in [!INCLUDEssSQL11] cannot be replicated to [!INCLUDEsql2008r2] or [!INCLUDEsql2008-md]. You must use [!INCLUDEsql2008r2] or [!INCLUDEsql2008-md] spatial tessellations for spatial indexes when backward compatibility with [!INCLUDEsql2008r2] or [!INCLUDEsql2008-md] databases is a requirement.