CREATE SPATIAL INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

SQL Server 2008 Books Online (June 2009)

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.

Note:
For information about spatial indexes, see Spatial Indexing Overview.

Topic link icon Transact-SQL Syntax Conventions

Syntax

Create Spatial Index 
CREATE SPATIAL INDEX index_name 
  ON <object> ( spatial_column_name )
    {
       [ USING <geometry_grid_tessellation> ]
          WITH ( <bounding_box> 
                [ [,] <tesselation_parameters> [ ,...n ] ] 
                [ [,] <spatial_index_option> [ ,...n ] ] ) 
     | [ USING <geography_grid_tessellation> ] 
          [ WITH ( [ <tesselation_parameters> [ ,...n ] ]
                   [ [,] <spatial_index_option> [ ,...n ] ] ) ]
    } 
  [ ON { filegroup_name | "default" } ]
; 

<object> ::=
    [ database_name. [ schema_name ] . | schema_name. ] 
                table_name

<geometry_grid_tessellation> ::=
{ GEOMETRY_GRID }
  
<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_parameters> ::=
{ 
    GRIDS = ( { <grid_density> [ ,...n ] | <density>, <density>, <density>, <density>  } ) 
  | CELLS_PER_OBJECT = n 
}

<grid_density> ::=
{
     LEVEL_1 = <density> 
  |  LEVEL_2 = <density> 
  |  LEVEL_3 = <density> 
  |  LEVEL_4 = <density> 
}

<density> ::= { LOW | MEDIUM | HIGH }

<geography_grid_tessellation> ::= 
{ GEOGRAPHY_GRID }
  
<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
}

Arguments

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 defaults to the type-specific value, as follows:

Data type of column	Tessellation scheme
geometry	GEOMETRY_GRID
geography	GEOGRAPHY_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 Indexing 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.

database_name: Is the name of the database.

schema_name: Is the name of the schema to which the table belongs.

table_name: Is the name of the table to be indexed.

WITH Options

GEOMETRY_GRID: Specifies the geometry grid tessellation scheme that you are using. GEOMETRY_GRID can be specified only on a column of the geometry data type. This is the default for this data type and does not need to be specified.

GEOGRAPHY_GRID: Specifies the geography grid tessellation scheme. GEOGRAPHY_GRID can be specified only on a column of the geography data type. This is the default for this data type and does not need to be specified.

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.

xmin: Specifies the x-coordinate of the lower-left corner of the bounding box.

ymin: Specifies the y-coordinate of the lower-left corner of the bounding box.

xmax: Specifies the x-coordinate of the upper-right corner of the bounding box.

ymax: Specifies the y-coordinate of upper-right corner of the bounding box.

XMIN = xmin: Specifies the property name and value for the x-coordinate of the lower-left corner of the bounding box.

YMIN = ymin: Specifies the property name and value for the y-coordinate of the lower-left corner of the bounding box.

XMAX = xmax: Specifies the property name and value for the x-coordinate of the upper-right corner of the bounding box.

YMAX = ymax: Specifies the property name and value for the y-coordinate of upper-right corner of the bounding box

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.

Note:
For information about tessellation, see Spatial Indexing Overview.

The GRIDS parameters are as follows:

LEVEL_1: Specifies the first (top) level grid.

LEVEL_2: Specifies the second-level grid.

LEVEL_3: Specifies the third-level grid.

LEVEL_4: Specifies the fourth-level grid.

LOW: Specifies the lowest possible density for the grid at a given level. LOW equates to 16 cells (a 4x4 grid).

MEDIUM: Specifies the medium density for the grid at a given level. MEDIUM equates to 64 cells (a 8x8 grid).

HIGH: Specifies the highest possible density for the grid at a given level. HIGH equates to 256 cells (a 16x16 grid).

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. The default number of cells per object is 16. 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.

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 Indexing Overview.

PAD_INDEX = { ON | OFF }

Specifies index padding. The default is OFF.

ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF or fillfactor is not specified: The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

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 Fill Factor.

SORT_IN_TEMPDB = { ON | OFF }

Specifies whether to store temporary sort results in tempdb. The default is OFF.

ON: The intermediate sort results that are used to build the index are stored in tempdb. This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. However, this increases the amount of disk space that is used during the index build.

OFF: The intermediate sort results are stored in the same database as the index.

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 tempdb and Index Creation.

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.

ON: Out-of-date statistics are not automatically recomputed.

OFF: Automatic statistics updating are enabled.

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.

ON: The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

OFF: An error is displayed if the specified index name already exists.

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 available only in SQL Server Enterprise, Developer, and Evaluation editions.

ALLOW_ROW_LOCKS = { ON | OFF }

Specifies whether row locks are allowed. The default is ON.

ON: Row locks are allowed when accessing the index. The Database Engine determines when row locks are used.

OFF: Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }

Specifies whether page locks are allowed. The default is ON.

ON: Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

OFF: Page locks are not used.

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:

1: Suppresses parallel plan generation.

>1: Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.

0 (default): Uses the actual number of processors or fewer based on the current system workload.

For more information, see Configuring Parallel Index Operations.

Note:
Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

Remarks

For an introduction to spatial indexing in SQL Server, see Spatial Indexing 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 Restrictions on Spatial Indexes.

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 Geometry Methods Supported by Spatial Indexes.

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:

sys.spatial_indexes: Represents the main index information of the spatial indexes.

sys.spatial_index_tessellations: Represents the information about the tessellation scheme and parameters of each of the spatial indexes.

For information about the metadata structure of spatial indexes, see Internal Tables.

Additional Remarks About Creating Indexes

For more information about creating indexes, see the "Remarks" section in CREATE INDEX (Transact-SQL).

Permissions

Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Examples

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_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 ) );