Создайте некластерный не уникальный индекс в инструкции CREATE TABLE с SQL Server

В SQL Server CREATE TABLE можно создать первичный ключ или уникальный индекс. Возможно ли создать не уникальный индекс внутри оператора 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

Опять же, цель заключается в создании неидеального индекса внутри оператора CREATE TABLE, а не после него.

Для чего это стоит, я не нашел [SQL Server Books Online entry для CREATE TABLE], чтобы быть полезным.

Кроме того, [Этот вопрос] почти идентичен, но принятый ответ не применяется.

Ответы

Ответ 1

Вы не можете. CREATE/ALTER TABLE принимают только добавленные CONSTRAINT, а не индексы. Тот факт, что первичный ключ и уникальные ограничения реализуются с точки зрения индекса, является побочным эффектом. Чтобы управлять индексами, у вас есть CREATE/ALTER/DROP INDEX, как вам хорошо известно.

Почему у вас есть такое требование к объявлениям, отличным от не-кластеризованных индексов, в инструкции CREATE TABLE?

Обратите внимание, что SQL Server 2014 представил параметр встроенного индекса для создания:

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 non-clustered index on (d, e)
    ,INDEX IX_MyTable4 NONCLUSTERED (d, e)
);
GO

Ответ 2

Это отдельное утверждение.

Также невозможно вставить в таблицу и выбрать из нее и построить индекс в том же самом выражении.

Запись BOL содержит необходимую информацию:

КЛАСТЕРЕД | NONCLUSTERED
Показывают, что кластерный или некластеризованный индекс созданный для ПЕРВИЧНОГО КЛЮЧА или УНИКАЛЬНОГО ограничение. Ограничения PRIMARY KEY по умолчанию CLUSTERED и UNIQUE по умолчанию установлено значение NONCLUSTERED.

В инструкции CREATE TABLE, CLUSTERED может быть указана только для одного ограничение. Если указано CLUSTERED для УНИКАЛЬНОГО ограничения и ПЕРВИЧНОГО Также указывается ограничение KEY. PRIMARY KEY по умолчанию НЕ НЕОБХОДИМО.

Вы можете создать индекс в поле PK, но не некластеризованный индекс в не-pk не-уникальном ограниченном поле.

Индекс NCL не имеет отношения к структуре таблицы и не является ограничением для данных внутри таблицы. Это отдельный объект, поддерживающий таблицу, но не являющийся неотъемлемой частью его функциональности или дизайна.

Вот почему это отдельное утверждение. Индекс NCL не имеет отношения к таблице с точки зрения дизайна (несмотря на оптимизацию запросов).

Ответ 3

Принятый ответ о том, как создать Индекс, построил создание таблицы script, не работал у меня. Это произошло:

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])
)

Помните, что внешние ключи не создают индексы, поэтому рекомендуется их индексировать, так как вы скорее присоединяетесь к ним.

Ответ 4

Согласно T-SQL CREATE TABLE, в 2014 году определение столбца поддерживает определение индекса:

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

и грамматика определяется как:

<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" } ]

Таким образом, многое из того, что вы можете сделать в качестве отдельного заявления, можно сделать встроенным. Я заметил, что include не является вариантом в этой грамматике, поэтому некоторые вещи невозможны.

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
)

Вы также можете иметь встроенные индексы, определенные как другая строка после столбцов, но внутри оператора create table, и это позволяет использовать несколько столбцов в индексе, но все же не оговорка include:

< 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" } ]  

}

Например, здесь мы добавляем индекс для обоих столбцов c и 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)
)