Partager via


CREATE SPATIAL INDEX (Transact-SQL)

Crée un index spatial sur une table et une colonne spécifiées. Un index peut être créé avant que la table ne contienne des données. Les index peuvent être créés sur des tables ou des vues d'une autre base de données en spécifiant un nom de base de données qualifié.

[!REMARQUE]

Pour plus d'informations sur les index spatiaux, consultez Vue d'ensemble de l'indexation spatiale.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

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
    Nom de l'index. Les noms d'index doivent être uniques dans une table, mais ne doivent pas être nécessairement uniques dans une base de données. Les noms d'index doivent respecter les règles applicables aux identificateurs.

  • ON <objet> ( spatial_column_name )
    Spécifie l'objet (base de données, schéma ou table) sur lequel l'index doit être créé et le nom de la colonne spatiale.

    spatial_column_name spécifie la colonne spatiale sur laquelle l'index est basé. Une seule colonne spatiale peut être spécifiée dans une définition d'index spatial unique ; toutefois, plusieurs index spatiaux peuvent être créés sur une colonne de type geometry ou geography.

  • USING
    Indique le schéma de pavage pour l'index spatial. Ce paramètre a pour valeur par défaut la valeur spécifique au type, comme suit :

    Type de données de la colonne

    Schéma de pavage

    geometry

    GEOMETRY_GRID

    geography

    GEOGRAPHY_GRID

    Un index spatial peut être créé uniquement sur une colonne de type geometry ou geography. Sinon, une erreur est générée. Par ailleurs, si un paramètre non valide pour un type donné est passé, une erreur est générée.

    [!REMARQUE]

    Pour plus d'informations sur la façon dont SQL Server implémente le pavage, consultez Vue d'ensemble de l'indexation spatiale.

  • ON filegroup_name
    Crée l'index spécifié dans le groupe de fichiers spécifié. Si aucun emplacement n'est défini et que la table n'est pas partitionnée, l'index utilise le même groupe de fichiers que la table sous-jacente. Le groupe de fichiers doit déjà exister.

  • ON "default**"**
    Crée l'index spécifié dans le groupe de fichiers par défaut.

    Le terme « default », dans ce contexte, n'est pas un mot clé. Il s'agit de l'identificateur du groupe de fichiers par défaut et il doit être délimité, comme dans ON "default" ou ON [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit avoir la value ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::=

Objet qualifié complet ou partiellement qualifié à indexer.

  • database_name
    Nom de la base de données.

  • schema_name
    Nom du schéma auquel appartient la table.

  • table_name
    Nom de la table à indexer.

Options WITH

  • GEOMETRY_GRID
    Spécifie le schéma de pavage de la grille géométrique que vous utilisez. GEOMETRY_GRID peut être spécifié uniquement sur une colonne du type de données geometry. Il s'agit de l'option par défaut pour ce type de données et il n'est pas nécessaire de la spécifier.

  • GEOGRAPHY_GRID
    Spécifie le schéma de pavage de la grille géographique. GEOGRAPHY_GRID peut être spécifié uniquement sur une colonne du type de données geography. Il s'agit de l'option par défaut pour ce type de données et il n'est pas nécessaire de la spécifier.

  • BOUNDING_BOX
    Spécifie un jeu de quatre tuples numérique qui définit les quatre coordonnées du cadre englobant : les coordonnées min. x et min. y de l'angle inférieur gauche, et les coordonnées max. x et max. y de l'angle supérieur droit.

    • xmin
      Spécifie la coordonnée x de l'angle inférieur gauche du cadre englobant.

    • ymin
      Spécifie la coordonnée y de l'angle inférieur gauche du cadre englobant.

    • xmax
      Spécifie la coordonnée x de l'angle supérieur droit du cadre englobant.

    • ymax
      Spécifie la coordonnée y de l'angle supérieur droit du cadre englobant.

    • XMIN = xmin
      Spécifie le nom et la valeur de la propriété pour la coordonnée x de l'angle inférieur gauche du cadre englobant.

    • YMIN = ymin
      Spécifie le nom et la valeur de la propriété pour la coordonnée y de l'angle inférieur gauche du cadre englobant.

    • XMAX = xmax
      Spécifie le nom et la valeur de la propriété pour la coordonnée x de l'angle supérieur droit du cadre englobant.

    • YMAX = ymax
      Spécifie le nom et la valeur de la propriété pour la coordonnée y de l'angle supérieur droit du cadre englobant.

    Les coordonnées du cadre englobant s'appliquent uniquement dans une clause USING GEOMETRY_GRID.

    xmax doit être supérieur à xmin et ymax doit être supérieur à ymin. Vous pouvez spécifier toute représentation de valeur float valide en supposant que xmax > xmin et ymax > ymin. Sinon, les erreurs appropriées sont générées.

    Il n'y a pas de valeurs par défaut.

    Les noms de propriété du cadre englobant ne respectent pas la casse, indépendamment du classement de base de données.

    Pour spécifier des noms de propriété, vous devez spécifier chacun d'entre eux une seule et unique fois. Vous pouvez les spécifier dans n'importe quel ordre. Par exemple, les clauses suivantes sont équivalentes :

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

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

  • GRIDS
    Définit la densité de la grille à chaque niveau d'un schéma de pavage.

    [!REMARQUE]

    Pour plus d'informations sur le pavage, consultez Vue d'ensemble de l'indexation spatiale.

    Les paramètres GRIDS sont les suivants :

    • LEVEL_1
      Spécifie la grille de premier niveau (haut).

    • LEVEL_2
      Spécifie la grille de second niveau.

    • LEVEL_3
      Spécifie la grille de troisième niveau.

    • LEVEL_4
      Spécifie la grille de quatrième niveau.

    • LOW
      Spécifie la densité la plus faible possible pour la grille à un niveau donné. LOW équivaut à 16 cellules (grille 4x4).

    • MEDIUM
      Spécifie la densité moyenne pour la grille à un niveau donné. MEDIUM équivaut à 64 cellules (grille 8x8).

    • HIGH
      Spécifie la densité la plus élevée possible pour la grille à un niveau donné. HIGH équivaut à 256 cellules (grille 16x16).

    Grâce aux noms de niveau, vous pouvez spécifier les niveaux dans n'importe quel ordre et omettre des niveaux. Si vous utilisez le nom d'un niveau, vous devez utiliser le nom des autres niveaux que vous spécifiez. Si vous omettez un niveau, sa densité prend MEDIUM comme valeur par défaut.

    Si une densité non valide est spécifiée, une erreur est générée.

  • CELLS_PER_OBJECT = n
    Spécifie le nombre de cellules de pavage par objet pouvant être utilisées pour un objet spatial unique dans l'index par le processus de pavage. n peut être un entier compris entre 1 et 8192 (inclus). Le nombre de cellules par objet par défaut est 16. Si un nombre non valide est passé ou que le nombre est supérieur au nombre maximal de cellules pour le pavage spécifié, une erreur est générée.

    Au niveau supérieur, si un objet couvre plus de cellules que le nombre spécifié par n, l'indexation utilise autant de cellules que nécessaire pour fournir un pavage de niveau supérieur complet. Dans de tels cas, un objet peut recevoir plus de cellules que le nombre spécifié. Dans ce cas, le nombre maximal est le nombre de cellules générées par la grille de niveau supérieur, qui dépend de la densité.

    La valeur CELLS_PER_OBJECT est utilisée par la règle de pavage de cellules par objet. Pour plus d'informations sur les règles de pavage, consultez Vue d'ensemble de l'indexation spatiale.

  • PAD_INDEX = { ON | OFF }
    Spécifie le remplissage de l'index. La valeur par défaut est OFF.

    • ON
      Le pourcentage d'espace libre indiqué par fillfactor est appliqué aux pages du niveau intermédiaire de l'index.

    • OFF ou fillfactor n'est pas spécifié
      Les pages de niveau intermédiaire sont presque entièrement remplies, ce qui laisse suffisamment d'espace libre pour au moins une ligne de la taille maximale permise par l'index, en prenant en compte l'ensemble de clés sur les pages intermédiaires.

    L'option PAD_INDEX est utile seulement si FILLFACTOR est spécifié car PAD_INDEX utilise le pourcentage spécifié par FILLFACTOR. Si le pourcentage défini pour FILLFACTOR n'est pas suffisamment élevé pour autoriser une ligne, le moteur de base de données remplace en interne le pourcentage de façon à ce qu'il autorise le minimum. Le nombre de lignes dans une page d'index intermédiaire n'est jamais inférieur à deux, quelle que soit la faiblesse de la valeur de fillfactor.

  • FILLFACTOR = fillfactor
    Spécifie un pourcentage qui indique l'espace occupé par le niveau feuille de chaque page d'index opéré par le moteur de base de données lors de la création ou de la reconstruction d'index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0. Si fillfactor a pour valeur 100 ou 0, le moteur de base de données crée des index avec des pages de niveau feuille intégralement remplies.

    [!REMARQUE]

    Les taux de remplissage 0 et 100 sont identiques en tous points.

    La valeur FILLFACTOR s'applique uniquement lors de la création ou de la recontruction de l'index. Le moteur de base de données ne conserve pas dynamiquement dans les pages le pourcentage d'espace libre défini. Pour afficher le taux de remplissage, utilisez l'affichage catalogue sys.indexes.

    Important

    La création d'un index cluster avec un taux de remplissage inférieur à 100 affecte la quantité d'espace de stockage qu'occupent les données, car le moteur de base de données redistribue les données lorsqu'il crée l'index cluster.

    Pour plus d'informations, consultez Facteur de remplissage.

  • SORT_IN_TEMPDB = { ON | OFF }
    Spécifie si les résultats temporaires du tri doivent être stockés dans tempdb. La valeur par défaut est OFF.

    • ON
      Les résultats de tri intermédiaires utilisés pour créer l'index sont stockés dans tempdb. Cela permet de réduire la durée de création d'un index si tempdb n'est pas sur le même groupe de disques que la base de données utilisateur. Toutefois, une plus grande quantité d'espace disque est alors utilisée lors de la création de l'index.

    • OFF
      Les résultats de tri intermédiaires sont stockés dans la même base de données que l'index.

    Outre l'espace nécessaire dans la base de données utilisateur pour créer l'index, tempdb doit disposer à peu près du même espace supplémentaire pour stocker les résultats de tri intermédiaires. Pour plus d'informations, consultez tempdb et création d'index.

  • IGNORE_DUP_KEY = OFF
    N'a aucun effet pour les index spatiaux, car le type d'index n'est jamais unique. N'activez pas cette option (ON), sinon une erreur est déclenchée.

  • STATISTICS_NORECOMPUTE = { ON | OFF}
    Spécifie si les statistiques de distribution sont recalculées. La valeur par défaut est OFF.

    • ON
      Les statistiques obsolètes ne sont pas recalculées automatiquement.

    • OFF
      La mise à jour automatique des statistiques est activée.

    Pour restaurer la mise à jour automatique des statistiques, affectez la valeur OFF à STATISTICS_NORECOMPUTE, ou exécutez UPDATE STATISTICS sans la clause NORECOMPUTE.

    Important

    La désactivation du recalcul automatique des statistiques de distribution peut empêcher l'optimiseur de requête de sélectionner des plans d'exécution optimaux pour les requêtes qui impliquent la table.

  • DROP_EXISTING = { ON | OFF }
    Spécifie que l'index spatial nommé préexistant est supprimé et reconstruit. La valeur par défaut est OFF.

    • ON
      L'index existant est supprimé et reconstruit. Le nom d'index défini doit être identique à celui de l'index existant. Toutefois, la définition de l'index peut être modifiée. Par exemple, vous pouvez définir des colonnes, un ordre de tri, un schéma de partition ou des options d'indexation différentes.

    • OFF
      Une erreur s'affiche si le nom d'index spécifié existe déjà.

    Le type d'index ne peut pas être modifié avec DROP_EXISTING.

  • ONLINE = OFF
    Indique que les tables sous-jacentes et les index associés ne sont pas disponibles pour les requêtes et la modification de données pendant l'opération d'index. Dans cette version de SQL Server, les constructions d'index en ligne ne sont pas prises en charge pour les index spatiaux. Si cette option a la valeur ON pour un index spatial, une erreur est générée. Omettez l'option ONLINE ou attribuez la valeur OFF à ONLINE.

    Une opération d'index hors connexion qui crée, reconstruit ou supprime un index spatial acquiert un verrou Sch-M (Modification du schéma) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération.

    [!REMARQUE]

    Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Indique si les verrous de ligne sont autorisés. La valeur par défaut est ON.

    • ON
      Les verrous de ligne sont autorisés lors de l'accès à l'index. Le moteur de base de données détermine le moment où les verrous de ligne sont utilisés.

    • OFF
      Les verrous de ligne ne sont pas utilisés.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Spécifie si les verrous de page sont autorisés. La valeur par défaut est ON.

    • ON
      Les verrous de page sont autorisés lors de l'accès à l'index. Le moteur de base de données détermine le moment où les verrous de page sont utilisés.

    • OFF
      Les verrous de page ne sont pas utilisés.

  • MAXDOP = max_degree_of_parallelism
    Remplace l'option de configuration max degree of parallelism pendant la durée de l'indexation. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plans parallèles. Le nombre maximum de processeurs est 64.

    Important

    Bien que l'option MAXDOP soit prise en charge syntaxiquement, CREATE SPATIAL INDEX n'utilise actuellement qu'un processeur unique.

    Valeurs possibles de max_degree_of_parallelism :

    • 1
      Supprime la génération de plans parallèles.

    • >1
      Limite le nombre maximal de processeurs utilisés dans l'opération d'index parallèle au nombre défini ou à un nombre inférieur en fonction de la charge de travail actuelle du système.

    • 0 (valeur par défaut)
      Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

    Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.

    [!REMARQUE]

    Les opérations d'index parallèles ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Notes

Pour une présentation de l'indexation spatiale dans SQL Server, consultez Vue d'ensemble de l'indexation spatiale.

Chaque option ne peut être spécifiée qu'une fois par instruction CREATE SPATIAL INDEX. La spécification d'un doublon de toute option génère une erreur.

Vous pouvez créer jusqu'à 249 index spatiaux sur chaque colonne spatiale dans une table. Il peut être utile de créer plusieurs index spatiaux sur une colonne spatiale spécifique, par exemple pour indexer des paramètres de pavage différents dans une même colonne.

Important

Il existe plusieurs autres restrictions applicables à la création d'un index spatial. Pour plus d'informations, consultez Restrictions sur les index spatiaux.

Une construction d'index ne peut pas utiliser le parallélisme de processus disponible.

Méthodes prises en charge sur les index spatiaux

Dans certaines conditions, les index spatiaux prennent en charge plusieurs méthodes de géométrie basées sur les ensembles. Pour plus d'informations, consultez Méthodes géométriques prises en charge par les index spatiaux.

Index spatiaux et partitionnement

Par défaut, si un index spatial est créé sur une table partitionnée, l'index est partitionné d'après le schéma de partition de la table. Ainsi, les données d'index et la ligne connexe sont stockées dans la même partition.

Dans ce cas, pour altérer le schéma de partition de la table de base, il vous faudrait supprimer l'index spatial avant de pouvoir repartitionner la table de base. Pour éviter cette restriction, vous pouvez spécifier l'option « ON filegroup » lors de la création d'un index spatial. Pour plus d'informations, consultez « Index spatiaux et groupes de fichiers » plus loin dans cette rubrique.

Index spatiaux et groupes de fichiers

Par défaut, les index spatiaux sont partitionnés dans les mêmes groupes de fichiers que la table sur laquelle l'index est spécifiée. Vous pouvez modifier ce comportement en spécifiant un groupe de fichiers :

[ ON { filegroup_name | "default" } ]

Si vous spécifiez un groupe de fichiers pour un index spatial, l'index est placé sur ce groupe de fichiers, quelle que soit le schéma de partitionnement de la table.

Affichages catalogue pour les index spatiaux

Les affichages catalogue suivants sont propres aux index spatiaux :

Pour plus d'informations sur la structure des métadonnées des index spatiaux, consultez Tables internes.

Notes supplémentaires sur la création d'index

Pour plus d'informations sur la création d'index, consultez la section « Remarques » dans CREATE INDEX (Transact-SQL).

Autorisations

Nécessite l'autorisation ALTER sur la table ou la vue. L'utilisateur doit être membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner.

Exemples

A. Création d'un index spatial sur une colonne de type geometry

L'exemple suivant crée une table nommée SpatialTable qui contient une colonne de type geometry, geometry_col. L'exemple crée ensuite un index spatial, SIndx_SpatialTable_geometry_col1, sur la table geometry_col. L'exemple utilise le schéma de pavage par défaut et spécifie le cadre englobant.

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. Création d'un index spatial sur une colonne de type geometry

L'exemple suivant crée un deuxième index spatial, SIndx_SpatialTable_geometry_col2, sur geometry_col dans la table SpatialTable. L'exemple spécifie GEOMETRY_GRID comme schéma de pavage. L'exemple spécifie également le cadre englobant, des densités différentes sur des niveaux de grille différents et 64 cellules par objet. L'exemple attribue aussi la valeur ON au remplissage d'index.

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. Création d'un index spatial sur une colonne de type geometry

L'exemple suivant crée un troisième index spatial, SIndx_SpatialTable_geometry_col3, sur geometry_col dans la table SpatialTable. L'exemple utilise le schéma de pavage par défaut. L'exemple spécifie le cadre englobant et utilise des densités de cellule différentes sur les troisième et quatrième niveaux, tout en utilisant le nombre par défaut de cellules par objet.

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. Modification d'une option qui est spécifique aux index spatiaux

L'exemple suivant reconstruit l'index spatial créé dans l'exemple précédent, SIndx_SpatialTable_geography_col3, en spécifiant une nouvelle densité LEVEL_3 avec 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. Création d'un index spatial sur une colonne de type geography

L'exemple suivant crée une table nommée SpatialTable2 qui contient une colonne de type geography, geography_col. L'exemple crée ensuite un index spatial, SIndx_SpatialTable_geography_col1, sur la table geography_col. L'exemple utilise les valeurs des paramètres par défaut du schéma de pavage GEOGRAPHY_GRID.

CREATE TABLE SpatialTable2(id int primary key, object GEOGRAPHY);
CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col1 
   ON SpatialTable2(object);

[!REMARQUE]

Pour les index de grille géographique, un cadre englobant ne peut pas être spécifié.

F. Création d'un index spatial sur une colonne de type geography

L'exemple suivant crée un deuxième index spatial, SIndx_SpatialTable_geography_col2, sur geography_col dans la table SpatialTable2. L'exemple spécifie GEOGRAPHY_GRID comme schéma de pavage. L'exemple spécifie également des densités de grille différentes sur des niveaux différents et 64 cellules par objet. L'exemple attribue aussi la valeur ON au remplissage d'index.

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. Création d'un index spatial sur une colonne de type geography

L'exemple crée ensuite un troisième index spatial, SIndx_SpatialTable_geography_col3, sur geography_col dans la table SpatialTable2. L'exemple utilise le schéma de pavage par défaut, GEOGRAPHY_GRID et la valeur CELLS_PER_OBJECT par défaut (16).

CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col3
   ON SpatialTable2(object)
   WITH ( GRIDS = ( LEVEL_3 = HIGH, LEVEL_2 = HIGH ) );