A spatial index is an index on a table based on spatial data in the table (a spatial column). Spatial data uses the new data types, GEOMETRY and GEOGRAPHY. Different tessellation schemes are used depending on the data type of the spatial column; a spatial column assigned the GEOMETRY data type uses the GEOMETRY_GRID tessellation scheme, while a spatial column assigned the GEOGRAPHIC data type uses the GEOGRAPHY_GRID tessellation scheme.
The table spatial index options provide a way to define how the index behaves, such as using index padding, how to store temporary sort results used to build the index, whether distribution statistics are recomputed, allowing row or page locks, and so on.
Tessellation options let you specify the density of the grid at each level of a tessellation scheme and define the number of tessellation cells per object that you can use for a single spatial index object during the tessellation process. There are four levels for which you can specify density, and there are three density values you can select:
Specifies to use the lowest density for the grid. Low equals 16 cells, or a 4x4 grid.
Specifies to use the medium density for the grid at the given level. Medium equals 64 cells, or an 8x8 grid.
Specifies to use the highest possible density for the grid at the given level. High equals 256 cells, or a 16x16 grid.
Boundary box options are used to set the four coordinates of the bounding box when GEOMETRY_GRID is your tessellation scheme.
Use the SQL Server Table Spatial Index Editor in a SQL Server 2008 or 2012 physical model to define general options, tessellation options, and boundary box options for an index on a table that contains spatial columns. You can specify more than one spatial index on a spatial column.
To define a SQL Server 2008 or 2012 table spatial index
The SQL Server Table Spatial Index Editor opens.
Note: Click New 
on the toolbar to create a new table spatial index. Use the Enter filter text box to filter a very large list of indexes to quickly locate the index that you want to define.
Specifies the name of the table spatial index. You can change the name of the table spatial index in this field.
Note: Table spatial index names must be unique within the table but do not have to be unique within the database.
Specifies the spatial column upon which the table spatial index is based.
Displays the data type (and therefore the tessellation scheme) of the spatial column. You do not explicitly define the data type here; the data type of the spatial column is defined in the SQL Server Column Editor.
Note: A spatial column assigned the GEOMETRY data type uses the GEOMETRY_GRID tessellation scheme, while a spatial column assigned the GEOGRAPHIC data type uses the GEOGRAPHY_GRID tessellation scheme.
Specifies the filegroup to which the table spatial index belongs. Select the filegroup from the drop-down.
Note: The filegroup you specify must already exist. If you do not specify a filegroup and the table is not partitioned, by default the spatial index uses the filegroup of the table upon which the index is based.
Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.
Lets you specify a percentage to indicate how full the database engine should make the leaf level of each index page during table spatial index creation or build. Enter an integer value in this field.
Specifies whether to use index padding. Select the option from the drop-down. If Pad Index is set to True, specify the percentage of free space specified by Fill Factor.
Specifies whether to store the temporary sort results used to build the index in tempdb. Select the option from the drop-down.
Specifies whether distribution statistics are recomputed. Select the option from the drop-down.
Specifies whether a named preexisting table spatial index is dropped and rebuilt. Select the option using the check box.
Specifies whether row locks are allowed for the selected table spatial index. Select the option from the drop-down.
Specifies whether page locks are allowed for the selected table spatial index. Select the option from the drop-down.
Specifies the maximum parallel property for the selected table spatial index. Enter a value in the field. This value overrides the maximum degree of parallelism option for the duration of the index operation. This is to limit the number of processors used in a parallel plan execution.
Lets you specify the density of the first (top) level grid. Select either Low, Medium, or High for the density level from the drop-down.
Lets you specify the density of the second level grid. Select either Low, Medium, or High for the density level from the drop-down.
Lets you specify the density of the third level grid. Select either Low, Medium, or High for the density level from the drop-down.
Lets you specify the density of the fourth level grid. Select either Low, Medium, or High for the density level from the drop-down.
Note: Low density equals 16 cells (4x4 grid), Medium density equals 64 cells (8x8 grid), and High density equals 256 cells (16x16 grid).
Specifies the number of tessellation cells per object that can be used for a single spatial object in the index by the tessellation process. Enter a numerical value.
Note: Boundary Box options are only available if the spatial column upon which your index is based is Geometric.
Lets you specify the value for the x-coordinate of the lower-left corner of the bounding box. Enter a numerical value.
Lets you specify the value for the y-coordinate of the lower-left corner of the bounding box. Enter a numerical value.
Lets you specify the value for the x-coordinate of the upper-right corner of the bounding box. Enter a numerical value.
Lets you specify the value for the y-coordinate of upper-right corner of the bounding box. Enter a numerical value.
The table spatial index is defined and the SQL Server Table Spatial Index Editor closes.
|
Copyright © 2013 CA.
All rights reserved.
|
|