О пользе ограничений

Как мы привыкли, ограничения (CONSTRAINT) — способ указания допустимых значений для столбцов в таблице. С этой своей функцией они справляются на отлично! Но помимо этого, ограничения так же помогают анализатору запросов генерировать более эффективный план выполнения. Как именно? Читайте ниже.

Есть такое мнение «Дайте MS SQL максимальную информацию о данных, которые вы храните». Что это значит? Во-первых, очевидные вещи: подбирайте наиболее подходящий тип данных (там где нужен int, можно не использовать bigint или float), указывайте размерность строковых или бинарных данных (nvarchar(10) вместо nvarchar(max)). А во-вторых — это ограничения: если значение в столбце может быть в пределах от 1 до 4 то укажите это явно, сервер вам только спасибо скажет!

Создадим таблицу:

CREATE TABLE [TestTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [Status] [int] NOT NULL
)

Добавим в нее 1 миллион записей следующего вида:

Id Value Status
1 value 1
2 value 2
3 value 3
4 value 1
5 value 2
6 value 3
7 value 1
8 value 2
9 value 3
10 value 1

И выполним запрос, предварительно включив сбор статистики:

SET STATISTICS IO ON
SELECT tt.Status, COUNT(*)
FROM [TestTable] AS tt
WHERE tt.Status <> 1
GROUP BY tt.Status

Запрос довольно жизненный — нам нужно количество записей по всем статусам кроме первого («Посчитать количество заказов в каждом статусе, кроме статуса Открыто»).

Получаем следующую статистику:

Table 'TestTable'. Scan count 9, logical reads 4590, physical reads 0, read-ahead reads 4038, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

А на самой табличке вот такой план:

Очевидный Table Scan — нам пришлось просмотреть всю таблицу, т.к. у нас нет индексов.

Давайте добавим индекс:

CREATE NONCLUSTERED INDEX [IX_TestTable_Status] ON [TestTable]
(
   [Status] ASC
)

И повторим запрос:

Table 'TestTable'. Scan count 2, logical reads 1501, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ожидаемо увидели Index Seek с интересным предикатом:Staus < 1 и Status > 1. Если вспомнить, что индексы — это дерево, а операции поиска по дереву выполняются достаточно быстро, то данный запрос выглядит вполне логично. Сервер преобразовал неравенство в два сравнения, которые может обработать наиболее эффективно. При этом, количество чтений так же уменьшилось.

А теперь давайте скажем серверу, что у нас в столбце Status могут быть только значения 1, 2, 3 и 4:

ALTER TABLE [TestTable] WITH CHECK ADD CONSTRAINT [CK_TestTable]
CHECK (([Status]=(4) OR [Status]=(3) OR [Status]=(2) OR [Status]=(1)))

И выполним запрос:

Table 'TestTable'. Scan count 3, logical reads 1508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Как ни странно, в плане выполнения теперь нет ни одного упоминания о статусе «1». Вместо этого оптимизатор заменил предикат<> 1на предикатIN (2, 3, 4), чего он раньше не делал до того момента, пока мы не добавили ограничение на столбец.

В данном примере нам не удалось выиграть на количестве чтений.

Рассмотрим еще один пример с другим запросом:

SET STATISTICS IO ON
SELECT tt.Status, COUNT(*)
FROM [TestTable] AS tt
WHERE tt.Status NOT IN (1,3)
GROUP BY tt.Status

Table 'TestTable'. Scan count 2, logical reads 1501, physical reads 1, read-ahead reads 1959, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TestTable'. Scan count 2, logical reads 757, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.