Как мы знаем, статистика не создается на табличных переменных и оптимизатор SQL Server считает, что ожидаемое количество строк при выборке из табличной переменной будет равно единице. На прошедшей конференции SQL Rally 2012 после моего доклада прозвучал очень интересный вопрос: будет ли влиять PRIMARY KEY в табличной переменной на ожидаемое количество строк при выборке. Я решил проверить это на практике, а так как результат, на мой взгляд, получился очень интересным, я решил им поделиться.

Для нашего эксперимента я создам 2 временных таблицы, одну с PRIMARY KEY, а вторую без, и 2 аналогичных табличных переменных. И вставлю в них значения от 1 до 100. И давайте попробуем сделать несколько тестовых выборок из этих таблиц с разными предикатами, а потом проанализируем результаты. Перед запуском скрипта необходимо включить  отображение реального плана выполнения запроса (можно сделать несколькими способами: сочетанием клавиш Ctrl+M, либо через меню Query -> Include Actual Execution Plan, либо нажать соответствующую кнопку на панели инструментов).

-- Создаем временную таблицу с числами от 1 до 1000
IF OBJECT_ID('tempdb.dbo.#nums', 'U') IS NOT NULL
	DROP TABLE #nums;
 
CREATE TABLE #nums (
	[n] int PRIMARY KEY
);
 
DECLARE @max AS INT, @rc AS INT;
SET @max = 800;
SET @rc = 1;
 
INSERT INTO #nums ([n]) VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
	INSERT INTO #nums ([n]) SELECT [n] + @rc FROM #nums;
	SET @rc = @rc * 2;
END
 
INSERT INTO #nums ([n])
SELECT [n] + @rc FROM #nums WHERE [n] + @rc <= @max;
 
/*
Создаем 2 временные таблицы: одну с PRIMARY KEY, вторую без.
И 2 аналогичные табличные переменные. Вставляем в них числа от 1 до 1000.
*/
IF OBJECT_ID('tempdb.dbo.#temp_table', 'U') IS NOT NULL
	DROP TABLE #temp_table;
 
IF OBJECT_ID('tempdb.dbo.#temp_table_pk', 'U') IS NOT NULL
	DROP TABLE #temp_table_pk;
 
CREATE TABLE #temp_table (
	[i] int
);
 
CREATE TABLE #temp_table_pk (
	[i] int PRIMARY KEY
);
 
DECLARE @table_var TABLE (
	[i] int
);
 
DECLARE @table_var_pk TABLE (
	[i] int PRIMARY KEY
);
 
INSERT INTO #temp_table ([i])
SELECT [n] FROM #nums;
 
INSERT INTO #temp_table_pk ([i])
SELECT [n] FROM #nums;
 
INSERT INTO @table_var ([i])
SELECT [n] FROM #nums;
 
INSERT INTO @table_var_pk ([i])
SELECT [n] FROM #nums;
 
-- Делаем несколько тестовых запросов
SELECT [i] FROM #temp_table WHERE [i] < 145;
SELECT [i] FROM #temp_table_pk WHERE [i] < 145;
 
SELECT [i] FROM #temp_table WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM #temp_table_pk WHERE [i] > 378 AND [i] < 634;
 
SELECT [i] FROM @table_var WHERE [i] < 145;
SELECT [i] FROM @table_var WHERE [i] < 145 OPTION (RECOMPILE);
 
SELECT [i] FROM @table_var_pk WHERE [i] < 145;
SELECT [i] FROM @table_var_pk WHERE [i] < 145 OPTION (RECOMPILE);
 
SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);
 
SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634;
SELECT [i] FROM @table_var_pk WHERE [i] > 378 AND [i] < 634 OPTION (RECOMPILE);

А теперь давайте подробно проанализируем планы наших запросов.

1. Выборка и временной таблицы с фильтром по верхней границе.

Результаты запроса

Здесь в принципе нет ничего необычного, оптимизатор построил статистику на временной таблице и использовал ее при выборке. При выборке из временной таблицы с PRIMARY KEY ситуация будет аналогичная, поэтому план запроса не привожу.

2. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы.

Результаты запроса

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

3. Выборка из табличной переменной с фильтром по верхней границе.

Привожу сразу планы запросов для обычной табличной переменной и табличной переменной с PRIMARY KEY.

Результаты запроса

Результаты запроса

Как мы видим и в том и в другом случае количество ожидаемых строк равно единице. Таким образом, PRIMARY KEY никак не влияет на ожидаемое количество строк при выборке из табличной переменной.

4. Выборка из табличной переменной с опцией RECOMPILE. Привожу только план выборки из обычной табличной переменной, т.к. для табличной переменной с PRIMARY KEY будет аналогично.

Результаты запроса

Результаты оказались несколько неожиданными: оптимизатор ожидает на выходе 300 строк вместо одной. 300 строк – это 30%, обычная константа, которая зашита в оптимизатор.

5. Выборка из временной таблицы, но в предикате указываем нижнюю и верхнюю границы. Сначала приведу обычный план запроса, потом план запроса с опцией RECOMPILE.

Результаты запроса

Результаты запроса

В первом плане запросов ожидаемое количество строк равно единице. А вот для запроса с опцией RECOMPILE количество ожидаемых строк равно 90 или 9% от общего числа. Я поэкспериментировал с различным количеством строк в таблице и разными границами предиката, но результата один – 9%. Для запросов к табличным переменным с PRIMARY KEY результаты будут такие же, поэтому планы запросов не привожу.

Вывод.

Пришла пора подвести итог небольшого практического исследования и ответить на главный вопрос статьи: наличие PRIMARY KEY на табличной переменной никак не влияет на ожидаемое количество строк при выборке.

Но в процессе исследования случайно столкнулся с тем, что если использовать опцию RECOMPILE, то количество ожидаемых строк будет 30% от общего числа строк, если использовать в предикате ограничение только по одной границе интервала. А если использовать ограничение с двух сторон, то количество ожидаемых строк будет 9% от общего числа строк в табличной переменной.