CREATE INDEX (Transact-SQL)

Чтобы создать кластеризованный индекс для таблицы (кучи) или удалить и повторно создать существующий кластеризованный индекс, требуется дополнительная рабочая область в базе данных для сортировки и временного копирования данных исходной таблицы или существующего кластеризованного индекса. Дополнительные сведения см. в разделе Определение требований к месту на диске для индексов. Дополнительные сведения о кластеризованных индексах см. в разделе Создание кластеризованных индексов.

Если существует уникальный индекс, то каждый раз при добавлении данных с помощью операции вставки компонент Database Engine делает проверку на появление повторяющихся значений. Производится откат операций вставки, которые могли бы создать повторяющиеся значения ключей, и компонент Database Engine выдает сообщение об ошибке. Это происходит даже в случае, если операция вставки изменяет несколько строк, а повторяющееся значение может появиться всего одно. Если делается попытка ввести данные, для которых существует уникальный индекс, и предложение IGNORE_DUP_KEY имеет значение ON, сбоем завершаются операции только с теми строками, где нарушается свойство уникальности индекса. Дополнительные сведения об уникальных индексах см. в разделе Создание уникальных индексов.

Секционированные индексы создаются и поддерживаются так же, как и секционированные таблицы, но обрабатываются как отдельные объекты базы данных подобно обычным индексам. Можно создать секционированный индекс для несекционированной таблицы и несекционированный индекс для секционированной таблицы.

Если создается индекс для секционированной таблицы и не указывается файловая группа, в которую должен быть помещен индекс, индекс секционируется так же, как и базовая таблица. Дело в том, что по умолчанию индексы помещаются в те же файловые группы, что и их базовые таблицы, а в случае секционированной таблицы — в схему секционирования, использующую те же самые столбцы секционирования.

Если секционируется неуникальный кластеризованный индекс, компонент Database Engine по умолчанию добавляет столбцы секционирования в список кластеризованных ключей индекса, если они еще не заданы.

Индексированные представления могут создаваться для секционированных таблиц таким же образом, как и индексы для таблиц. Дополнительные сведения о секционированных индексах см. в разделе Секционированные таблицы и индексы.

Создание уникального кластеризованного индекса для представления повышает производительность запросов, т. к. представление хранится в базе данных так же, как и таблица с кластеризованным индексом. Оптимизатор запросов может использовать индексированные представления для ускорения выполнения запросов. Чтобы оптимизатор рассматривал представление для подстановки, это представление не обязательно должно быть указано в запросе.

Для успешного создания индексированного представления сделайте следующие шаги.

1. Убедитесь в правильности параметров SET для всех существующих таблиц, на которые ссылается представление.

2. Прежде чем создавать новые таблицы и представление, проверьте параметры SET для сеанса.

3. Проверьте, что определение представления детерминировано.

4. Создайте представление с помощью WITH SCHEMABINDING.

5. Создайте уникальный кластеризованный индекс для представления.

Обязательные параметры SET для индексированных представлений

Если при выполнении запроса активны разные параметры SET, оценка одного и того же выражения может дать разные результаты в компоненте Database Engine. Например, если параметр SET CONCAT_NULL_YIELDS_NULL равен ON, выражение 'abc' + NULL возвращает значение NULL. Но если параметр CONCAT_NULL_YIEDS_NULL равен OFF, то же самое выражение дает результат 'abc'.

Для правильной поддержки представлений и получения согласованных результатов некоторые параметры SET индексированных представлений должны иметь определенные значения. В следующих случаях параметрам SET из следующей таблицы должны быть присвоены значения, указанные в столбце Обязательноезначение:

• Создается индексированное представление.

• С любой из таблиц, используемых в индексированном представлении, выполняется операция вставки, обновления или удаления. Сюда входят такие операции, как массовое копирование, репликация и распределенные запросы.

• Индексированное представление используется оптимизатором запросов для создания плана запроса.

  Параметры SET	Обязательное значение	Значение сервера по умолчанию	Значение по умолчанию для OLE DB и ODBC	Значение по умолчанию для DB-Library
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *Если уровень совместимости базы данных равен 90 или более, при установке параметра ANSI_WARNINGS в состояние ON параметр ARITHABORT также устанавливается в состояние ON. Если уровень совместимости базы данных установлен в состояние 80 или более раннее, то параметр ARITHABORT необходимо явным образом установить в состояние ON.

Если используется соединение с сервером через интерфейсы OLE DB или ODBC, достаточно изменить параметр ARITHABORT. Все значения DB-Library должны быть правильно установлены на уровне сервера с помощью хранимой процедуры sp_configure, или из приложения с помощью команды SET. Дополнительные сведения о параметрах SET см. в разделе Использование параметров в СУБД SQL Server.

Важно!
Настоятельно рекомендуется присвоить пользовательскому параметру ARITHABORT значение ON на всем сервере, как только в какой-либо базе данных сервера будет создано первое индексированное представление или индекс на вычисляемом столбце.

Детерминированные функции

Дополнительные требования

Отфильтрованный индекс является оптимизированным некластеризованным индексом, предназначенным для запросов, выбирающих небольшой процент строк таблицы. Чтобы проиндексировать часть данных таблицы, в нем используется предикат фильтра. Правильно составленный отфильтрованный индекс может увеличить скорость выполнения запроса, уменьшить стоимость хранения и обслуживания.

Обязательные параметры SET для отфильтрованных индексов

Дополнительные сведения о пространственных индексах см. в разделах CREATE SPATIAL INDEX (Transact-SQL) и Работа с пространственными индексами (компонент Database Engine).

Дополнительные сведения об XML-индексах см. в разделе CREATE XML INDEX (Transact-SQL) и Индексы для столбцов типа данных xml.

Максимальный размер ключа индекса равен 900 байт. Индексы на столбцах типа varchar, размер которых превышает 900 байт, могут быть созданы, если в момент создания индекса объем существующих данных в столбцах не превышает 900 байт; но последующие операции вставки или обновления, вызывающие превышение общего размера 900 байт, будут заканчиваться ошибкой. Дополнительные сведения см. в разделе Максимальный размер ключей индекса. Ключ кластеризованного индекса не может включать в себя столбцы varchar, для которых существуют данные в единице распределения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается для столбца varchar и существующие данные располагаются в единице распределения IN_ROW_DATA, то последующие операции вставки или обновления для данного столбца, принудительно отправляющие данные за пределы строки, будут завершаться ошибкой. Дополнительные сведения о единицах распределения см. в разделе Организация таблиц и индексов.

Некластеризованные индексы могут включать неключевые столбцы на конечном уровне индекса. При вычислении размера ключа индекса компонент Database Engine эти столбцы не рассматривает. Дополнительные сведения см. в разделе Индекс с включенными столбцами.

Индексы могут создаваться на вычисляемых столбцах. Кроме того, вычисляемые столбцы могут иметь свойство PERSISTED. Это значит, что компонент Database Engine хранит вычисленные значения в таблице и обновляет их при обновлении любых столбцов, от которых зависит вычисляемый столбец. Компонент Database Engine использует эти сохраняемые значения, когда создает индекс по столбцу и когда запрос обращается к индексу.

Для индексации вычисляемого столбца этот вычисляемый столбец должен быть детерминированным и точным. Если используется свойство PERSISTED, список типов индексируемых вычисляемых столбцов расширяется и включает следующее.

• Вычисляемые столбцы, основанные на выражениях языка Transact-SQL, функциях CLR и методах определяемых пользователем типов данных CLR, помеченных пользователем как детерминированные.

• Вычисляемые столбцы, основанные на выражениях, которые определены компонентом Database Engine как детерминированные, но не являются точными.

Для материализованных вычисляемых столбцов необходимо, чтобы следующие параметры SET имели значения, указанные выше в разделе «Обязательные параметры SET для индексированных представлений».

Ограничения UNIQUE или PRIMARY KEY могут содержать вычисляемый столбец, если он удовлетворяет всем условиям для индексирования. Вычисляемый столбец должен быть детерминированным и точным или детерминированным и сохраняемым. Дополнительные сведения о детерминизме см. в разделе Детерминированные и недетерминированные функции.

Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут индексироваться как ключевые или включенные неключевые столбцы, если тип данных вычисляемого столбца приемлем как тип данных для ключевого столбца индекса или неключевого столбца. Например, нельзя создать первичный XML-индекс для вычисляемого столбца типа xml. Если размер ключа индекса превышает 900 байт, выдается предупреждение.

Создание индекса на вычисляемом столбце может привести к ошибке в операциях вставки или обновления, которые до этого успешно выполнялись. Такое неудачное завершение возможно, если вычисляемый столбец вызывает арифметическую ошибку. Например, вычисляемый столбец c в следующей таблице вызывает арифметическую ошибку, но инструкция INSERT работает.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Если же после создания таблицы создать индекс на вычисляемом столбце c, та же инструкция INSERT будет заканчиваться ошибкой.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Дополнительные сведения см. в разделе Создание индексов вычисляемых столбцов.

Неключевые столбцы, называемые «включенными столбцами», могут добавляться на конечный уровень некластеризованного индекса для повышения производительности запроса благодаря тому, что индекс включает все необходимые данные для запроса. Т. е. все столбцы, указанные в запросе, включаются в индекс в качестве ключевых или неключевых столбцов. Таким образом оптимизатор запросов может найти все необходимые данные путем просмотра индекса, не обращаясь к данным таблицы или кластеризованного индекса. Дополнительные сведения см. в разделе Индекс с включенными столбцами.

На сервере SQL Server 2005 представлены новые параметры индексов и изменен способ установки параметров. Для обратной совместимости синтаксиса аргумент WITH option_name эквивалентен аргументу WITH (<option_name> = ON). Устанавливая параметры индекса, необходимо соблюдать следующие правила.

• Новые параметры индекса могут быть заданы только с помощью аргумента WITH (option_name= ON | OFF).

• Нельзя задавать параметры с помощью нового синтаксиса и совместимого старого в одной и той же инструкции. Например, инструкция с WITH (DROP_EXISTING, ONLINE = ON) вызовет ошибку.

• При создании XML-индекса параметры должны указываться с помощью аргумента WITH (option_name= ON | OFF).

Предложение DROP_EXISTING может использоваться для перестроения индекса, добавления или удаления столбцов, изменения параметров, изменения порядка сортировки столбцов, а также изменения схемы секционирования или файловой группы.

Если индекс налагает ограничение PRIMARY KEY или UNIQUE и его определение никак не меняется, он удаляется и создается вновь с сохранением существующих ограничений. Но если изменить определение индекса, инструкция вызовет ошибку. Чтобы изменить ограничение PRIMARY KEY или UNIQUE, удалите ограничение и добавьте ограничение вместе с новым определением.

Предложение DROP_EXISTING повышает производительность, если заново создается кластеризованный индекс с тем же самым или другим набором ключей на таблице, имеющей также некластеризованные индексы. Предложение DROP_EXISTING заменяет удаление старого кластеризованного индекса с помощью инструкции DROP INDEX и последующее создание нового кластеризованного индекса с помощью инструкции CREATE INDEX. Некластеризованные индексы перестраиваются один раз, а после этого только в случае, если меняется определение индекса. Предложение DROP_EXISTING не перестраивает некластеризованные индексы, если определение индекса содержит то же самое имя индекса, ключевые столбцы, столбцы секционирования, атрибут уникальности и порядок сортировки, что и исходный индекс.

Независимо от того, перестраиваются ли некластеризованные индексы, они всегда остаются в своих исходных файловых группах или схемах секционирования и используют исходные функции секционирования. Если кластеризованный индекс перестраивается в другой файловой группе или схеме секционирования, некластеризованные индексы не перемещаются вместе с кластеризованным индексом. Поэтому даже если некластеризованные индексы раньше были выровнены по кластеризованному, теперь это может быть не так. Дополнительные сведения о выравнивании секционированного индекса см. в разделе Дополнительные рекомендации по секционированным индексам.

Предложение DROP_EXISTING не сортирует данные заново, если те же столбцы ключа индекса используются в том же порядке с тем же порядком сортировки по возрастанию или убыванию, за исключением случаев, когда инструкция индекса задает некластеризованный индекс и параметр ONLINE равен OFF. Если кластеризованный индекс отключен, операция CREATE INDEX WITH DROP_EXISTING должна выполняться с параметром ONLINE в значении OFF. Если некластеризованный индекс отключен и не связан с отключенным кластеризованным индексом, операция CREATE INDEX WITH DROP_EXISTING может выполняться с параметром ONLINE в значении OFF или ON.

Если удаляются или перестраиваются индексы с 128 или более экстентами, компонент Database Engine откладывает фактическое освобождение страниц и связанных с ними блокировок до фиксации транзакции. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.

Следующие правила применяются к операциям с индексами в режиме в сети.

• Во время выполнения операций с индексами в сети базовая таблица не может изменяться, усекаться или удаляться.

• Для операций с индексами требуется дополнительное временное место на диске. For more information, see Определение требований к месту на диске для индексов.

• Обработка индексов в сети может выполняться для секционированных индексов, содержащих материализованные вычисляемые столбцы или включенные столбцы.

Дополнительные сведения см. в разделе Выполнение операции с индексами в сети.

Если заданы аргументы ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при обращении к индексу разрешены блокировки на уровне строк, страниц и таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы. Дополнительные сведения см. в разделе Укрупнение блокировки (компонент Database Engine).

Если заданы аргументы ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при обращении к индексу разрешены только блокировки на уровне таблиц.

Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса.

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

Сжатие данных описывается в разделе Создание сжатых таблиц и индексов. Необходимо учесть следующие основные моменты.

• С помощью сжатия можно хранить больше строк в странице, максимальный размер строки при этом не изменяется.

• Неконечные страницы индекса не сжаты на уровне страниц, но могут быть сжаты на уровне строк.

• У каждого некластеризованного индекса индивидуальные настройки сжатия, которые не наследуются от базовой таблицы.

• При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия кучи, если не указано другое состояние сжатия.

На секционированные индексы налагаются следующие ограничения.

• Если у таблицы есть невыровненные индексы, изменить настройку сжатия отдельной секции невозможно.

• Инструкция ALTER INDEX <index> ... Инструкция REBUILD PARTITION ... производит перестроение указанной секции индекса.

• Инструкция ALTER INDEX <index> ... Инструкция REBUILD WITH ... производит перестроение всех секций индекса.

Оценить состояние сжатия таблицы, индекса или секции можно с помощью хранимой процедуры sp_estimate_data_compression_savings.

В следующем примере создается некластеризованный индекс по столбцу BusinessEntityID таблицы Purchasing.ProductVendor.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

В следующем примере создается некластеризованный составной индекс по столбцам SalesQuota и SalesYTD таблицы Sales.SalesPerson.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

В следующем примере создается уникальный некластеризованный индекс по столбцу Name таблицы Production.UnitMeasure. Индекс требует уникальности данных, вставляемых в столбец Name.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

Следующий запрос проверяет ограничение уникальности данных при попытке вставить строку с тем же значением, что и в уже существующей строке.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

В результате выдается сообщение об ошибке:

Сервер: Сообщение 2601, уровень 14, состояние 1, строка 1

Невозможно вставить повторяющуюся строку ключей в объект UnitMeasure с уникальным индексом AK_UnitMeasure_Name. Выполнение данной инструкции было прервано.

В следующем примере демонстрируется влияние параметра IGNORE_DUP_KEY со значениями ON и OFF на операцию вставки нескольких строк во временную таблицу. В таблицу #Test вставляется одна строка, которая намеренно приведет к появлению повторяющихся значений при выполнении второй многострочной операции вставки INSERT. Счетчик строк таблицы возвращает количество вставленных строк.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Сервер: Сообщение 3604, уровень 16, состояние 1, строка 5 Повторяющийся ключ был пропущен.

Число строк

--------------

38

Обратите внимание, что строки из таблицы Production.UnitMeasure, не нарушающие ограничение уникальности, были успешно вставлены. Было выдано предупреждение, и строка с повторяющимся значением не была вставлена, но отката всей транзакции не произошло.

Те же инструкции выполняются вновь, но теперь с аргументом IGNORE_DUP_KEY, равным OFF.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Сервер: Сообщение 2601, уровень 14, состояние 1, строка 5

Невозможно вставить повторяющуюся строку ключей в объект #Test с уникальным индексом.

'AK_Index'. Выполнение данной инструкции было прервано.

Число строк

--------------

1

Обратите внимание, что ни одна из строк таблицы Production.UnitMeasure не была вставлена, хотя ограничение индекса UNIQUE было нарушено только одной строкой.

В следующем примере индекс по столбцу ProductID таблицы Production.WorkOrder удаляется и создается вновь с помощью параметра DROP_EXISTING. Указываются также аргументы FILLFACTOR и PAD_INDEX.

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

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

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

В следующем примере создается некластеризованный индекс с одним ключевым столбцом (PostalCode) и четырьмя неключевыми столбцами (AddressLine1, AddressLine2, City, StateProvinceID). Далее следует запрос, все данные для которого есть в индексе. Прежде чем выводить индекс, выбранный оптимизатором запросов, выберите в меню Запрос среды Среда SQL Server Management Studio команду Показать действительный план выполнения.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

В следующем примере создается некластеризованный секционированный индекс в существующей схеме секционирования TransactionsPS1. В данном примере подразумевается, что образец секционированного индекса установлен.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

В следующем примере создается отфильтрованный индекс для таблицы Production.BillOfMaterials. Предикат фильтра может включать столбцы, не являющиеся ключевыми в отфильтрованном индексе. Предикат в примере выбирает только те строки, где EndDate не равно NULL.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

Следующий пример демонстрирует создание индекса для несекционированной таблицы с помощью сжатия строк.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия строк во всех секциях индекса.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия страниц для секции 1 индекса и сжатия строк для секций индекса со 2 по 4.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO