Créer un index non unique non clusterisé dans l'instruction CREATE TABLE avec SQL Server

Question

Il est possible de créer une clé primaire ou un index unique dans une instruction CREATE TABLE du serveur SQL. Est-il possible de créer un index non unique dans une déclaration CREATE TABLE ?

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    -- This creates a primary key
    ,CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (a)

    -- This creates a unique nonclustered index on columns b and c
    ,CONSTRAINT IX_MyTable1 UNIQUE (b, c)

    -- Is it possible to create a non-unique index on columns d and e here?
    -- Note: these variations would not work if attempted:
    -- ,CONSTRAINT IX_MyTable2 INDEX (d, e)
    -- ,CONSTRAINT IX_MyTable3 NONCLUSTERED INDEX (d, e)
);
GO

-- The proposed non-unique index should behave identically to
-- an index created after the CREATE TABLE statement. Example:
CREATE NONCLUSTERED INDEX IX_MyTable4 ON MY_TABLE (d, e);
GO

Encore une fois, l'objectif est de créer l'index non unique au sein de l'instruction CREATE TABLE, et non après.

Pour ce qu'il en vaut, je n'ai pas trouvé les [SQL Server Books Online entry for CREATE TABLE] pour être utile.

En outre, [Cette question] est presque identique, mais la réponse acceptée ne s'applique pas.

Answer 1

Depuis SQL 2014, cette opération peut être effectuée via création d'un index en ligne :

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    -- This creates a primary key
    ,CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (a)

    -- This creates a unique nonclustered index on columns b and c
    ,CONSTRAINT IX_MyTable1 UNIQUE (b, c)

    -- This creates a standard non-clustered index on (d, e)
    ,INDEX IX_MyTable4 NONCLUSTERED (d, e)
);
GO

---

Avant SQL 2014, CREATE/ALTER TABLE ne permettait d'ajouter que des CONSTRAINTs, et non des index. Le fait que les contraintes de clé primaire et d'unicité soient implémentées en termes d'index est un effet secondaire.

Answer 2

TLDR :

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_indexName nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL
)

Détails

Conformément à la T-SQL CRÉER UNE TABLE en 2014, la définition de la colonne permet de définir un index :

<column_definition> ::=  
column_name <data_type>  
    ...
    [ <column_index> ]  

et la grammaire <column_index> est définie comme suit :

<column_index> ::=   
 INDEX index_name [ CLUSTERED | NONCLUSTERED ]  
    [ WITH ( <index_option> [ ,... n ] ) ]  
    [ ON { partition_scheme_name (column_name )   
         | filegroup_name  
         | default   
         }  
    ]   
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]  

Ainsi, une grande partie de ce que vous pouvez faire dans une déclaration séparée peut être réalisée en ligne. J'ai remarqué que include n'est pas une option dans cette grammaire et certaines choses ne sont donc pas possibles.

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_indexName nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL
)

Vous pouvez également définir des index en ligne sur une autre ligne après les colonnes, mais dans l'instruction create table, ce qui permet d'avoir plusieurs colonnes dans l'index, mais toujours pas d'index en ligne. include clause :

< table_index > ::=   
{  
    {  
      INDEX index_name [ CLUSTERED | NONCLUSTERED ]   
         (column_name [ ASC | DESC ] [ ,... n ] )   
    | INDEX index_name CLUSTERED COLUMNSTORE  
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] )  
    }  
    [ WITH ( <index_option> [ ,... n ] ) ]   
    [ ON { partition_scheme_name (column_name )   
         | filegroup_name  
         | default   
         }  
    ]   
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]  

}   

Par exemple, nous ajoutons un index sur les colonnes c et d :

CREATE TABLE MyTable(
    a int NOT NULL
    ,b smallint NOT NULL index IX_MyTable_b nonclustered
    ,c smallint NOT NULL
    ,d smallint NOT NULL
    ,e smallint NOT NULL

    ,index IX_MyTable_c_d nonclustered (c,d)
)

Answer 3

La réponse habituelle à la question de savoir comment créer un Index inline a Table creation script n'a pas fonctionné pour moi. Ceci a fonctionné :

CREATE TABLE [dbo].[TableToBeCreated]
(
    [Id] BIGINT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,[ForeignKeyId] BIGINT NOT NULL
    ,CONSTRAINT [FK_TableToBeCreated_ForeignKeyId_OtherTable_Id] FOREIGN KEY ([ForeignKeyId]) REFERENCES [dbo].[OtherTable]([Id])
    ,INDEX [IX_TableToBeCreated_ForeignKeyId] NONCLUSTERED ([ForeignKeyId])
)

N'oubliez pas que les clés étrangères ne créent pas d'index, et qu'il est donc préférable de les indexer, car il est fort probable que vous fassiez des jointures sur ces clés.

Answer 4

Il s'agit d'une déclaration distincte.

Il n'est pas non plus possible d'insérer dans une table, de sélectionner à partir de celle-ci et de construire un index dans la même déclaration.

L'entrée BOL contient les informations dont vous avez besoin :

GROUPÉ | NON GROUPÉ
I qu'un index clusterisé ou non clusterisé est est créé pour la contrainte PRIMARY KEY ou UNIQUE pour la contrainte KEY PRIMAIRE ou UNIQUE. Les contraintes de CLÉ PRIMAIRE prennent par défaut la valeur CLUSTERED, et les contraintes UNIQUE sont définies par défaut sur NONCLUSTERED.

Dans un état CREATE TABLE ne peut être spécifiée que pour une seule contrainte contrainte. Si CLUSTERED est spécifié pour une contrainte UNIQUE et qu'une contrainte PRIMARY est également spécifiée, la valeur par défaut de la KEY PRIMAIRE prend par défaut la valeur NONCLUSTERED.

Vous pouvez créer un index sur un champ PK, mais pas un index non groupé sur un champ non PK non unique.

Un index NCL n'est pas pertinent pour la structure de la table et ne constitue pas une contrainte pour les données contenues dans la table. Il s'agit d'une entité distincte qui prend en charge la table, mais qui ne fait pas partie intégrante de sa fonctionnalité ou de sa conception.

C'est pourquoi il s'agit d'une déclaration distincte. L'index NCL n'est pas pertinent pour la table du point de vue de la conception (nonobstant l'optimisation des requêtes).