CREATE SPATIAL INDEX (Transact-SQL)CREATE SPATIAL INDEX (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

SQL ServerSQL Server 中,於指定的資料表和資料行上建立空間索引。Creates a spatial index on a specified table and column in SQL ServerSQL Server. 可以在資料表中有資料之前建立索引。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. 如需空間索引的資訊,請參閱空間索引概觀For information about spatial indexes, see Spatial Indexes Overview.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

CREATE SPATIAL INDEX index_name
  ON <object> ( spatial_column_name )  
    {  
       <geometry_tessellation> | <geography_tessellation>  
    }
  [ ON { filegroup_name | "default" } ]  
[;]
  
<object> ::=  
    { database_name.schema_name.table_name | schema_name.table_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 }  
  
<tessellation_grid> ::=  
{
    GRIDS = ( { <grid_level> [ ,...n ] | <grid_size>, <grid_size>, <grid_size>, <grid_size>  }
        )  
}  
<tessellation_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 }  
}  

引數Arguments

index_name 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 )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 會指定當做索引根據的空間資料行。spatial_column_name specifies the spatial column on which the index is based. 在單一空間索引定義中,只能指定一個空間資料行;但是在 geometrygeography 資料行上可以建立多個空間索引。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.

USINGUSING
指示空間索引的鑲嵌式配置。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_GRIDGEOMETRY_GRID
幾何geometry GEOMETRY_AUTO_GRIDGEOMETRY_AUTO_GRID
地理位置geography GEOGRAPHY_GRIDGEOGRAPHY_GRID
地理位置geography GEOGRAPHY_AUTO_GRIDGEOGRAPHY_AUTO_GRID

空間索引只能建立在 geometrygeography 型別的資料行上,否則會引發錯誤。A spatial index can be created only on a column of type geometry or geography, otherwise an error is raised. 如果傳遞特定型別的無效參數,會引發錯誤。If an invalid parameter for a given type is passed, an error is raised.

如需 SQL ServerSQL Server 如何實作鑲嵌的資訊,請參閱空間索引概觀For information about how SQL ServerSQL Server implements tessellation, see Spatial Indexes Overview.

ON filegroup_name ON filegroup_name
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

在指定的檔案群組上建立指定的索引。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"ON "default"
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

在預設的檔案群組上建立指定的索引。Creates the specified index on the default filegroup.

在這個內容中,default 這個字不是關鍵字。The term default, in this context, is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,例如 ON "default" 或 ON [default]。It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. 如果指定了 "default",目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 這是預設值。This is the default setting. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::= <object>::=
這是要建立索引的完整或非完整物件。Is the fully qualified or non-fully qualified object to be indexed.

database_name database_name
這是資料庫的名稱。Is the name of the database.

schema_name schema_name
這是資料表所屬的結構描述名稱。Is the name of the schema to which the table belongs.

table_name table_name
這是要建立索引的資料表名稱。Is the name of the table to be indexed.

當 database_name 是目前的資料庫或 database_name 是 tempdb 且 object_name 的開頭為 # 時,Azure SQL DatabaseAzure SQL Database 支援三部分名稱格式 database_name.[schema_name].object_name。Azure SQL DatabaseAzure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

USING 選項USING options

GEOMETRY_GRIDGEOMETRY_GRID
指定您所使用的 geometry 方格鑲嵌式配置。Specifies the geometry grid tessellation scheme that you are using. GEOMETRY_GRID 只能在 geometry 資料類型的資料行上指定。GEOMETRY_GRID can be specified only on a column of the geometry data type. GEOMETRY_GRID 允許手動調整鑲嵌式配置。GEOMETRY_GRID allows for manual adjusting of the tessellation scheme.

GEOMETRY_AUTO_GRIDGEOMETRY_AUTO_GRID
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later) and Azure SQL DatabaseAzure SQL Database

只能在 geometry 資料類型的資料行上指定。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_GRIDGEOGRAPHY_GRID
指定地理方格鑲嵌式配置。Specifies the geography grid tessellation scheme. GEOGRAPHY_GRID 只能在 geography 資料類型的資料行上指定。GEOGRAPHY_GRID can be specified only on a column of the geography data type.

GEOGRAPHY_AUTO_GRIDGEOGRAPHY_AUTO_GRID
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later) and Azure SQL DatabaseAzure SQL Database

只能在 geography 資料類型的資料行上指定。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.

WITH 選項WITH options

BOUNDING_BOXBOUNDING_BOX
指定定義週框方塊之四個座標的四個數值 Tuple:左下角的 x-min 和 y-min 座標及右上角的 x-max 和 y-max 座標。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 xmin
指定週框方塊左下角的 X 座標。Specifies the x-coordinate of the lower-left corner of the bounding box.

ymin ymin
指定週框方塊左下角的 Y 座標。Specifies the y-coordinate of the lower-left corner of the bounding box.

xmax xmax
指定週框方塊右上角的 X 座標。Specifies the x-coordinate of the upper-right corner of the bounding box.

ymax ymax
指定週框方塊右上角的 Y 座標。Specifies the y-coordinate of the upper-right corner of the bounding box.

XMIN = xmin XMIN = xmin
針對週框方塊左下角的 X 座標指定屬性名稱和值。Specifies the property name and value for the x-coordinate of the lower-left corner of the bounding box.

YMIN =ymin YMIN =ymin
針對週框方塊左下角的 Y 座標指定屬性名稱和值。Specifies the property name and value for the y-coordinate of the lower-left corner of the bounding box.

XMAX =xmax XMAX =xmax
針對週框方塊右上角的 X 座標指定屬性名稱和值。Specifies the property name and value for the x-coordinate of the upper-right corner of the bounding box.

YMAX =ymax YMAX =ymax
針對週框方塊右上角的 Y 座標指定屬性名稱和值。Specifies the property name and value for the y-coordinate of upper-right corner of the bounding box

注意

週框方塊座標只適用於 USING GEOMETRY_GRID 子句內。Bounding-box coordinates apply only within a USING GEOMETRY_GRID clause.

xmax 必須大於 xmin,而 ymax 必須大於 yminxmax must be greater than xmin and ymax must be greater than ymin. 您可以指定任何有效的float 值表示法,前提如下:xmax > xminymax > yminYou 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, YMIN =ymin, XMAX =xmax, YMAX =ymax )

  • BOUNDING_BOX =( XMIN =xmin, XMAX =xmax, YMIN =ymin, YMAX =ymax)BOUNDING_BOX =( XMIN =xmin, XMAX =xmax, YMIN =ymin, YMAX =ymax)

GRIDSGRIDS
定義鑲嵌式配置之每一個層級上的方格密度。Defines the density of the grid at each level of a tessellation scheme. 已選取 GEOMETRY_AUTO_GRID 和 GEOGRAPHY_AUTO_GRID 時,會停用這個選項。When GEOMETRY_AUTO_GRID and GEOGRAPHY_AUTO_GRID are selected, this option is disabled.

如需鑲嵌的資訊,請參閱空間索引概觀For information about tessellation, see Spatial Indexes Overview.

GRIDS 參數如下所示:The GRIDS parameters are as follows:

LEVEL_1LEVEL_1
指定第一層 (上層) 方格。Specifies the first-level (top) grid.

LEVEL_2LEVEL_2
指定第二層方格。Specifies the second-level grid.

LEVEL_3LEVEL_3
指定第三層方格。Specifies the third-level grid.

LEVEL_4LEVEL_4
指定第四層方格。Specifies the fourth-level grid.

LOWLOW
針對給定層級的方格指定可能的最低密度。Specifies the lowest possible density for the grid at a given level. LOW 等於 16 個資料格 (4x4 方格)。LOW equates to 16 cells (a 4x4 grid).

MEDIUM MEDIUM
針對給定層級的方格指定中密度。Specifies the medium density for the grid at a given level. MEDIUM 等於 64 個資料格 (8x8 方格)。MEDIUM equates to 64 cells (an 8x8 grid).

HIGHHIGH
針對給定層級的方格指定可能的最高密度。Specifies the highest possible density for the grid at a given level. HIGH 等於 256 個資料格 (16x16 方格)。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. 如果您省略層級,它的密度預設為 MEDIUM。If you omit a level, its density defaults to MEDIUM.

警告

如果指定了無效的密度,將會引發錯誤。If an invalid density is specified, an error is raised.

CELLS_PER_OBJECT =n 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 可以是 1 與 8192 (含) 之間的任何整數。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 的預設值如下:CELLS_PER_OBJECT has the following default values:

USING 選項USING option 每一物件的預設資料格數目Default Cells per Object
GEOMETRY_GRIDGEOMETRY_GRID 1616
GEOMETRY_AUTO_GRIDGEOMETRY_AUTO_GRID 88
GEOGRAPHY_GRIDGEOGRAPHY_GRID 1616
GEOGRAPHY_AUTO_GRIDGEOGRAPHY_AUTO_GRID 1212

在最上層,如果物件涵蓋的資料格數目要比 n指定的數目還要多,則索引會盡量使用所需的資料格數目來提供完整的最上層鑲嵌。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.

CELLS_PER_OBJECT 值會由每一物件的資料格鑲嵌式規則所使用。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 }PAD_INDEX = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

開啟ON
指出 fillfactor 指定的可用空間百分比會套用到索引的中繼層級頁面上。Indicates that the percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

OFF 或未指定 fillfactorOFF or fillfactor is not specified
指出中繼層級頁面會幾乎填滿整個容量,但會考量中繼頁面上的索引鍵集,而保留至少可供索引所能擁有之大小上限的一個資料列使用的足夠空間。Indicates that 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.

只有在指定 FILLFACTOR 時,才能使用 PAD_INDEX 選項,因為 PAD_INDEX 會使用 FILLFACTOR 所指定的百分比。The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. 如果 FILLFACTOR 所指定的百分比不夠,無法允許一個資料列,Database EngineDatabase Engine 會在內部覆寫該百分比以允許最小值。If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database EngineDatabase Engine internally overrides the percentage to allow for the minimum. 不論 fillfactor 的值設得多低,中繼索引頁面上的資料列數目絕對不能少於兩個。The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

FILLFACTOR =fillfactor FILLFACTOR =fillfactor
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定用以指出建立或重建索引時,Database EngineDatabase Engine 填滿各索引頁面分葉層級之程度的百分比。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or rebuild. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 預設值是 0。The default is 0. 如果 fillfactor 是 100 或 0, Database EngineDatabase Engine 會利用已填滿容量的分葉頁面來建立索引。If fillfactor is 100 or 0, the Database EngineDatabase Engine creates indexes with leaf pages filled to capacity.

注意

填滿因數值 0 和 100 在各方面都是一樣的。Fill factor values 0 and 100 are the same in all respects.

只有在建立或重建索引時才會套用 FILLFACTOR 設定。The FILLFACTOR setting applies only when the index is created or rebuilt. Database EngineDatabase Engine 不會動態保留頁面中空白空間的指定百分比。The Database EngineDatabase Engine does not dynamically keep the specified percentage of empty space in the pages. 若要檢視填滿因數設定,請使用 sys.indexes 目錄檢視表。To view the fill factor setting, use the sys.indexes catalog view.

重要

利用小於 100 的 FILLFACTOR 來建立叢集索引,會影響資料所佔用的儲存空間數量,因為 Database EngineDatabase Engine 在建立叢集索引時會轉散發資料。Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database EngineDatabase 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 }SORT_IN_TEMPDB = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否要將暫時排序結果儲存在 tempdb 中。Specifies whether to store temporary sort results in tempdb. 預設值為 OFF。The default is OFF.

開啟ON
用來建立索引的中繼排序結果會儲存在 tempdb 中。The intermediate sort results that are used to build the index are stored in tempdb. 如果 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.

OFFOFF
中繼排序結果會儲存在與用來儲存索引相同的資料庫中。The intermediate sort results are stored in the same database as the index.

除了在建立索引時,使用者資料庫中所需的空間以外,tempdb 還需要大約相同數量的額外空間來容納中繼排序結果。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. 如需詳細資訊,請參閱索引的 SORT_IN_TEMPDB 選項For more information, see SORT_IN_TEMPDB Option For Indexes.

IGNORE_DUP_KEY =OFF IGNORE_DUP_KEY =OFF
對於空間索引沒有任何作用,因為索引類型絕對不是唯一的。Has no effect for spatial indexes because the index type is never unique. 請勿將這個選項設定為 ON,否則會引發錯誤。Do not set this option to ON, or else an error is raised.

STATISTICS_NORECOMPUTE = { ON | OFF}STATISTICS_NORECOMPUTE = { ON | OFF}
指定是否要重新計算散發統計資料。Specifies whether distribution statistics are recomputed. 預設值為 OFF。The default is OFF.

開啟ON
不會自動重新計算過期的統計資料。Out-of-date statistics are not automatically recomputed.

OFFOFF
啟用自動統計資料更新。Automatic statistics updating are enabled.

若要還原自動統計資料更新,請將 STATISTICS_NORECOMPUTE 設為 OFF,或執行不含 NORECOMPUTE 子句的 UPDATE STATISTICS。To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

重要

停用散發統計資料的自動重新計算,可防止查詢最佳化工具取得與資料表有關之查詢的最佳執行計畫。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 }DROP_EXISTING = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定要卸除及重建預先存在的具名空間索引。Specifies that the named, preexisting spatial index is dropped and rebuilt. 預設值為 OFF。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.

OFFOFF
如果所指定的索引名稱已存在,畫面上會出現錯誤。An error is displayed if the specified index name already exists.

您無法利用 DROP_EXISTING 來變更索引類型。The index type cannot be changed by using DROP_EXISTING.

ONLINE =OFF ONLINE =OFF
指定在索引作業期間,基礎資料表和相關聯的索引無法供查詢和資料修改使用。Specifies that underlying tables and associated indexes are not available for queries and data modification during the index operation. 在這一版的 SQL ServerSQL Server 中,空間索引不支援線上索引建立。In this version of SQL ServerSQL Server, online index builds are not supported for spatial indexes. 如果此選項針對空間索引設定為 ON,就會引發錯誤。If this option is set to ON for a spatial index, an error is raised. 請省略 ONLINE 選項,或是將 ONLINE 設定為 OFF。Either omit the ONLINE option or set ONLINE to OFF.

建立、重建或卸除空間索引的離線索引作業會取得資料表的結構描述修改 (Sch-M) 鎖定。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.

注意

SQL ServerSQL Server的所有版本都無法使用線上索引作業。Online index operations are not available in every edition of SQL ServerSQL Server. 如需 SQL ServerSQL Server版本支援的功能清單,請參閱 SQL Server 2016 版本支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當存取索引時,允許資料列鎖定。Row locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用資料列鎖定的時機。The Database EngineDatabase Engine determines when row locks are used.

OFFOFF
不使用資料列鎖定。Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

開啟ON
當存取索引時,允許頁面鎖定。Page locks are allowed when accessing the index. Database EngineDatabase Engine 會決定使用頁面鎖定的時機。The Database EngineDatabase Engine determines when page locks are used.

OFFOFF
不使用頁面鎖定。Page locks are not used.

MAXDOP =max_degree_of_parallelism MAXDOP =max_degree_of_parallelism
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 and later) and Azure SQL DatabaseAzure SQL Database

針對索引作業持續時間覆寫 max degree of parallelism 組態選項。Overrides the max degree of parallelism configuration option for the duration of the index operation. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

重要

雖然 MAXDOP 選項在語法上有受到支援,但是 CREATE SPATIAL INDEX 目前一定只會使用單一處理器。Although the MAXDOP option is syntactically supported, CREATE SPATIAL INDEX currently always uses only a single processor.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>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 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

MicrosoftMicrosoftSQL ServerSQL Server 的每個版本都無法使用平行索引作業。Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 如需 SQL ServerSQL Server版本支援的功能清單,請參閱 SQL Server 2016 版本支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

DATA_COMPRESSION = {NONE | ROW | PAGE}DATA_COMPRESSION = {NONE | ROW | PAGE}
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later) and Azure SQL DatabaseAzure SQL Database

決定索引使用的資料壓縮層級。Determines the level of data compression used by the index.

NONE
索引不會將壓縮用於資料上。No compression used on data by the index

ROWROW
索引會將資料列壓縮用於資料上。Row compression used on data by the index

PAGEPAGE
索引會將頁面壓縮用於資料上。Page compression used on data by the index

備註Remarks

每一個 CREATE SPATIAL INDEX 陳述式只能指定每一個選項一次。Every option can be specified only once per CREATE SPATIAL INDEX statement. 指定重複的任何選項都會引發錯誤。Specifying a duplicate of any option raises an error.

在資料表的每一個空間資料行上最多可以建立 249 個空間索引。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.

重要

建立空間索引時,有許多其他限制。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. 為了避免這項限制,當您正在建立空間索引時,可以指定 "ON filegroup" 選項。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" } ][ 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_indexessys.spatial_indexes
表示空間索引的主要索引資訊。Represents the main index information of the spatial indexes.

sys.spatial_index_tessellationssys.spatial_index_tessellations
表示有關鑲嵌式配置和每一個空間索引之參數的資訊。Represents the information about the tessellation scheme and parameters of each of the spatial indexes.

有關建立索引的其他備註Additional remarks about creating indexes

如需索引建立的詳細資訊,請參閱 CREATE INDEX (Transact-SQL) 中的<備註>一節。For more information about creating indexes, see the "Remarks" section in CREATE INDEX (Transact-SQL).

權限Permissions

使用者必須擁有資料表或檢視表的 ALTER 權限,或者使用者必須是 sysadmin 固定伺服器角色的成員或 db_ddladmindb_owner 固定資料庫角色成員。The user must have ALTER permission on the table or view, or be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

範例Examples

A.A. 在幾何資料行上建立空間索引Creating a spatial index on a geometry column

下列範例會建立包含 SpatialTablegeometry類型資料行 且名稱為 geometry_col 的資料表。The following example creates a table named SpatialTable that contains a geometry type column, geometry_col. 然後,此範例會在 SIndx_SpatialTable_geometry_col1 上建立空間索引 geometry_colThe 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.B. 在幾何資料行上建立空間索引Creating a spatial index on a geometry column

下列範例會在 SIndx_SpatialTable_geometry_col2 資料表的 geometry_col 上建立第二個空間索引 SpatialTableThe following example creates a second spatial index, SIndx_SpatialTable_geometry_col2, on the geometry_col in the SpatialTable table. 此範例會指定 GEOMETRY_GRID 做為鑲嵌式配置。The example specifies GEOMETRY_GRID as the tessellation scheme. 此範例也會指定週框方塊、不同方格層級上的不同密度,以及每一物件 64 個資料格。The example also specifies the bounding box, different densities on different grid levels, and 64 cells per object. 此範例也會將索引填補設定為 ONThe 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.C. 在幾何資料行上建立空間索引Creating a spatial index on a geometry column

下列範例會在 SIndx_SpatialTable_geometry_col3 資料表的 geometry_col 中建立第三個空間索引 SpatialTableThe 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.D. 變更空間索引所特有的選項Changing an option that is specific to spatial indexes

下列範例會使用 DROP_EXISTING = ON 來指定新的 SIndx_SpatialTable_geography_col3 密度,藉以重建上述範例中所建立的空間索引 LEVEL_3The 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.E. 在地理資料行上建立空間索引Creating a spatial index on a geography column

下列範例會建立包含 SpatialTable2geography類型資料行 且名稱為 geography_col 的資料表。The following example creates a table named SpatialTable2 that contains a geography type column, geography_col. 然後,此範例會在 SIndx_SpatialTable_geography_col1 上建立空間索引 geography_colThe example then creates a spatial index, SIndx_SpatialTable_geography_col1, on the geography_col. 此範例會使用 GEOGRAPHY_AUTO_GRID 鑲嵌式配置的預設參數值。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);  

注意

如果是地理方格索引,將無法指定週框方塊。For geography grid indexes, a bounding box cannot be specified.

F.F. 在地理資料行上建立空間索引Creating a spatial index on a geography column

下列範例會在 SIndx_SpatialTable_geography_col2 資料表的 geography_col 上建立第二個空間索引 SpatialTable2The following example creates a second spatial index, SIndx_SpatialTable_geography_col2, on the geography_col in the SpatialTable2 table. 此範例會指定 GEOGRAPHY_GRID 做為鑲嵌式配置。The example specifies GEOGRAPHY_GRID as the tessellation scheme. 此範例也會在不同層級上指定不同方格密度,並指定每一物件 64 個資料格。The example also specifies different grid densities on different levels and 64 cells per object. 此範例也會將索引填補設定為 ONThe 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.G. 在地理資料行上建立空間索引Creating a spatial index on a geography column

然後此範例會在 SIndx_SpatialTable_geography_col3 資料表的 geography_col 中建立第三個空間索引 SpatialTable2The example then creates a third spatial index, SIndx_SpatialTable_geography_col3, on the geography_col in the SpatialTable2 table. 此範例會使用預設鑲嵌式配置 GEOGRAPHY_GRID 及預設 CELLS_PER_OBJECT 值 (16)。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 ) );  

另請參閱See also

ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL) CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
資料類型 (Transact-SQL) Data Types (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.index_columns (Transact-SQL) sys.index_columns (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.spatial_index_tessellations (Transact-SQL) sys.spatial_index_tessellations (Transact-SQL)
sys.spatial_indexes (Transact-SQL) sys.spatial_indexes (Transact-SQL)
空間索引概觀Spatial Indexes Overview