CREATE SPATIAL INDEX (Transact-SQL)
Creates a spatial index on a specified table and column. An index can be created before there is data in the table. Indexes can be created on tables or views in another database by specifying a qualified database name. Spatial indexes require the table to have a clustered primary key.
Note
|
|---|
|
For information about spatial indexes, see Spatial Indexes Overview. |
Create Spatial Index
CREATE SPATIAL INDEX index_name
ON <object> ( spatial_column_name )
{
<geometry_tessellation> | <geography_tessellation>
}
[ ON { filegroup_name | "default" } ]
;
<object> ::=
[ database_name. [ schema_name ] . | schema_name. ]
table_name
<geometry_tessellation> ::=
{
<geometry_automatic_grid_tessellation> | <geometry_manual_grid_tessellation>
}
<geometry_automatic_grid_tessellation> ::=
{
[ USING GEOMETRY_AUTO_GRID ]
WITH (
<bounding_box>
[ [,] <tessellation_cells_per_object> [ ,…n] ]
[ [,] <spatial_index_option> [ ,…n] ]
)
}
<geometry_manual_grid_tessellation> ::=
{
[ USING GEOMETRY_GRID ]
WITH (
<bounding_box>
[ [,]<tessellation_grid> [ ,…n] ]
[ [,]<tessellation_cells_per_object> [ ,…n] ]
[ [,]<spatial_index_option> [ ,…n] ]
)
}
<geography_tessellation> ::=
{
<geography_automatic_grid_tessellation> | <geography_manual_grid_tessellation>
}
<geography_automatic_grid_tessellation> ::=
{
[ USING GEOGRAPHY_AUTO_GRID ]
[ WITH (
[ [,] <tessellation_cells_per_object> [ ,…n] ]
[ [,] <spatial_index_option> ]
) ]
}
<geography_manual_grid_tessellation> ::=
{
[ USING GEOGRAPHY_GRID ]
[ WITH (
[ <tessellation_grid> [ ,…n] ]
[ [,] <tessellation_cells_per_object> [ ,…n] ]
[ [,] <spatial_index_option> [ ,…n] ]
) ]
}
<bounding_box> ::=
{
BOUNDING_BOX = ( {
xmin, ymin, xmax, ymax
| <named_bb_coordinate>, <named_bb_coordinate>, <named_bb_coordinate>, <named_bb_coordinate>
} )
}
<named_bb_coordinate> ::= { XMIN = xmin | YMIN = ymin | XMAX = xmax | YMAX=ymax }
<tesselation_grid> ::=
{
GRIDS = ( { <grid_level> [ ,...n ] | <grid_size>, <grid_size>, <grid_size>, <grid_size> }
)
}
<tesseallation_cells_per_object> ::=
{
CELLS_PER_OBJECT = n
}
<grid_level> ::=
{
LEVEL_1 = <grid_size>
| LEVEL_2 = <grid_size>
| LEVEL_3 = <grid_size>
| LEVEL_4 = <grid_size>
}
<grid_size> ::= { LOW | MEDIUM | HIGH }
<spatial_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = OFF
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = OFF
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE }
}
- index_name
-
Is the name of the index. Index names must be unique within a table but do not have to be unique within a database. Index names must follow the rules of identifiers.
- ON <object> ( spatial_column_name )
-
Specifies the object (database, schema, or table) on which the index is to be created and the name of spatial column.
spatial_column_name specifies the spatial column on which the index is based. Only one spatial column can be specified in a single spatial index definition; however, multiple spatial indexes can be created on a geometry or geography column.
- USING
-
Indicates the tessellation scheme for the spatial index. This parameter uses the type-specific value, shown in the following table:
Data type of column
Tessellation scheme
geometry
GEOMETRY_GRID
geometry
GEOMETRY_AUTO_GRID
geography
GEOGRAPY_GRID
geography
GEOGRAPHY_AUTO_GRID
A spatial index can be created only on a column of type geometry or geography. Otherwise, an error is raised. Also, if an invalid parameter for a given type is passed, an error is raised.
Note
For information about how SQL Server implements tessellation, see Spatial Indexes Overview.
- ON filegroup_name
-
Creates the specified index on the specified filegroup. If no location is specified and the table is not partitioned, the index uses the same filegroup as the underlying table. The filegroup must already exist.
- ON "default"
-
Creates the specified index on the default filegroup.
The term default, in this context, is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
<object>::=
Is the fully qualified or non-fully qualified object to be indexed.
USING Options
WITH Options
- BOUNDING_BOX
-
Specifies a numeric four-tuple that defines the four coordinates of the bounding box: the x-min and y-min coordinates of the lower-left corner, and the x-max and y-max coordinates of the upper-right corner.
Bounding-box coordinates apply only within a USING GEOMETRY_GRID clause.
xmax must be greater than xmin and ymax must be greater than ymin. You can specify any valid float value representation, assuming that: xmax > xmin and ymax > ymin. Otherwise the appropriate errors are raised.
There are no default values.
The bounding-box property names are case-insensitive regardless of the database collation.
To specify property names, you must specify each of them once and only once. You can specify them in any order. For example, the following clauses are equivalent:
-
BOUNDING_BOX = ( XMIN = xmin, YMIN = ymin, XMAX = xmax, YMAX = ymax )
-
BOUNDING_BOX = ( XMIN = xmin, XMAX = xmax, YMIN = ymin, YMAX = ymax )
-
- GRIDS
-
Defines the density of the grid at each level of a tessellation scheme. When GEOMETRY_AUTO_GRID and GEOGRAPHY_AUTO_GRID are selected, this option is disabled.
Note
For information about tessellation, see Spatial Indexes Overview.
The GRIDS parameters are as follows:
Using level names allows you to specify the levels in any order and to omit levels. If you use the name for any level, you must use the name of any other level that you specify. If you omit a level, its density defaults to MEDIUM.
If an invalid density is specified, an error is raised.
- CELLS_PER_OBJECT =n
-
Specifies the number of tessellation cells per object that can be used for a single spatial object in the index by the tessellation process. n can be any integer between 1 and 8192, inclusive. If an invalid number is passed or the number is larger than the maximum number of cells for the specified tessellation, an error is raised.
CELLS_PER_OBJECT has the following default values:
USING option
Default Cells per Object
GEOMETRY_GRID
16
GEOMETRY_AUTO_GRID
8
GEOGRAPHY_GRID
16
GEOGRAPHY_AUTO_GRID
12
At the top level, if an object covers more cells than specified by n, the indexing uses as many cells as necessary to provide a complete top-level tessellation. In such cases, an object might receive more than the specified number of cells. In this case, the maximum number is the number of cells generated by the top-level grid, which depends on the density.
The CELLS_PER_OBJECT value is used by the cells-per-object tessellation rule. For information about the tessellation rules, see Spatial Indexes Overview.
- PAD_INDEX = { ON | OFF }
-
Specifies index padding. The default is OFF.
The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.
- FILLFACTOR =fillfactor
-
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity.
Note
Fill factor values 0 and 100 are the same in all respects.
The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.
Important
Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database Engine redistributes the data when it creates the clustered index.
For more information, see Specify Fill Factor for an Index.
- SORT_IN_TEMPDB = { ON | OFF }
-
Specifies whether to store temporary sort results in tempdb. The default is OFF.
In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. For more information, see SORT_IN_TEMPDB Option For Indexes.
- IGNORE_DUP_KEY =OFF
-
Has no effect for spatial indexes because the index type is never unique. Do not set this option to ON, or else an error is raised.
- STATISTICS_NORECOMPUTE = { ON | OFF}
-
Specifies whether distribution statistics are recomputed. The default is OFF.
To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.
Important
Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.
- DROP_EXISTING = { ON | OFF }
-
Specifies that the named, preexisting spatial index is dropped and rebuilt. The default is OFF.
The index type cannot be changed by using DROP_EXISTING.
- ONLINE =OFF
-
Specifies that underlying tables and associated indexes are not available for queries and data modification during the index operation. In this version of SQL Server, online index builds are not supported for spatial indexes. If this option is set to ON for a spatial index, an error is raised. Either omit the ONLINE option or set ONLINE to OFF.
An offline index operation that creates, rebuilds, or drops a spatial index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.
Note
Online index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.
- ALLOW_ROW_LOCKS = { ON | OFF }
-
Specifies whether row locks are allowed. The default is ON.
- ALLOW_PAGE_LOCKS = { ON | OFF }
-
Specifies whether page locks are allowed. The default is ON.
- MAXDOP =max_degree_of_parallelism
-
Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.
Important
Although the MAXDOP option is syntactically supported, CREATE SPATIAL INDEX currently always uses only a single processor.
max_degree_of_parallelism can be:
For more information, see Configure Parallel Index Operations.
Note
Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.
- DATA_COMPRESSION = {NONE | ROW | PAGE}
-
Determines the level of data compression used by the index.
For an introduction to spatial indexing in SQL Server, see Spatial Indexes Overview.
Every option can be specified only once per CREATE SPATIAL INDEX statement. Specifying a duplicate of any option raises an error.
You can create up to 249 spatial indexes on each spatial column in a table. Creating more than one spatial index on specific spatial column can be useful, for example, to index different tessellation parameters in a single column.
|
Important
|
|---|
|
There are a number of other restrictions on creating a spatial index. For more information, see Spatial Indexes Overview. |
An index build cannot make use of available process parallelism.
Methods Supported on Spatial Indexes
Under certain conditions, spatial indexes support a number of set-oriented geometry methods. For more information, see Spatial Indexes Overview.
Spatial Indexes and Partitioning
By default, if a spatial index is created on a partitioned table, the index is partitioned according to the partition scheme of the table. This assures that index data and the related row are stored in the same partition.
In this case, to alter the partition scheme of the base table, you would have to drop the spatial index before you can repartition the base table. To avoid this restriction, when you are creating a spatial index, you can specify the "ON filegroup" option. For more information, see "Spatial Indexes and Filegroups," later in this topic.
Spatial Indexes and Filegroups
By default, spatial indexes are partitioned to the same filegroups as the table on which the index is specified. This can be overridden by using the filegroup specification:
[ ON { filegroup_name | "default" } ]
If you specify a filegroup for a spatial index, the index is placed on that filegroup, regardless of the partitioning scheme of the table.
Catalog Views for Spatial Indexes
The following catalog views are specific to spatial indexes:
Additional Remarks About Creating Indexes
For more information about creating indexes, see the "Remarks" section in CREATE INDEX (Transact-SQL).
A. Creating a spatial index on a geometry column
The following example creates a table named SpatialTable that contains a geometry type column, geometry_col. The example then creates a spatial index, SIndx_SpatialTable_geometry_col1, on the geometry_col. The example uses the default tessellation scheme and specifies the bounding box.
CREATE TABLE SpatialTable(id int primary key, geometry_col geometry); CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1 ON SpatialTable(geometry_col) WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
B. Creating a spatial index on a geometry column
The following example creates a second spatial index, SIndx_SpatialTable_geometry_col2, on the geometry_col in the SpatialTable table. The example specifies GEOMETRY_GRID as the tessellation scheme. The example also specifies the bounding box, different densities on different grid levels, and 64 cells per object. The example also sets the index padding to ON.
CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col2
ON SpatialTable(geometry_col)
USING GEOMETRY_GRID
WITH (
BOUNDING_BOX = ( xmin=0, ymin=0, xmax=500, ymax=200 ),
GRIDS = (LOW, LOW, MEDIUM, HIGH),
CELLS_PER_OBJECT = 64,
PAD_INDEX = ON );
C. Creating a spatial index on a geometry column
The following example creates a third spatial index, SIndx_SpatialTable_geometry_col3, on the geometry_col in the SpatialTable table. The example uses the default tessellation scheme. The example specifies the bounding box and uses different cell densities on the third and fourth levels, while using the default number of cells per object.
CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col3
ON SpatialTable(geometry_col)
WITH (
BOUNDING_BOX = ( 0, 0, 500, 200 ),
GRIDS = ( LEVEL_4 = HIGH, LEVEL_3 = MEDIUM ) );
D. Changing an option that is specific to spatial indexes
The following example rebuilds the spatial index created in the preceding example, SIndx_SpatialTable_geography_col3, by specifying a new LEVEL_3 density with DROP_EXISTING = ON.
CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col3
ON SpatialTable(geography_col)
WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ),
GRIDS = ( LEVEL_3 = LOW ),
DROP_EXISTING = ON );
E. Creating a spatial index on a geography column
The following example creates a table named SpatialTable2 that contains a geography type column, geography_col. The example then creates a spatial index, SIndx_SpatialTable_geography_col1, on the geography_col. The example uses the default parameters values of the GEOGRAPHY_AUTO_GRID tessellation scheme.
CREATE TABLE SpatialTable2(id int primary key, object GEOGRAPHY); CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col1 ON SpatialTable2(object);
|
Note
|
|---|
|
For geography grid indexes, a bounding box cannot be specified. |
F. Creating a spatial index on a geography column
The following example creates a second spatial index, SIndx_SpatialTable_geography_col2, on the geography_col in the SpatialTable2 table. The example specifies GEOGRAPHY_GRID as the tessellation scheme. The example also specifies different grid densities on different levels and 64 cells per object. The example also sets the index padding to ON.
CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col2
ON SpatialTable2(object)
USING GEOGRAPHY_GRID
WITH (
GRIDS = (MEDIUM, LOW, MEDIUM, HIGH ),
CELLS_PER_OBJECT = 64,
PAD_INDEX = ON );
G. Creating a spatial index on a geography column
The example then creates a third spatial index, SIndx_SpatialTable_geography_col3, on the geography_col in the SpatialTable2 table. The example uses the default tessellation scheme, GEOGRAPHY_GRID, and the default CELLS_PER_OBJECT value (16).
CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col3 ON SpatialTable2(object) WITH ( GRIDS = ( LEVEL_3 = HIGH, LEVEL_2 = HIGH ) );
