Ms sql переменная таблица

Долгое время в SQL сервере в качестве хранения каких-либо промежуточных данных использовались временные таблицы.
Создавались они примерно следующим образом:
create table #t1(tid int,tname nvarchar(80));

Но, начиная с 2005 версии ситуация несколько изменилась.

Появились, так называемые табличные переменные
В чем разница?
Во-первых в объявлении. Для объявления табличной переменной необходимо объявить ее:
declare @t1 table (tid int, tname nvarchar(80))

в дальнейшем использование этой переменной ничем не отличается от той же временной таблицы:
declare @t1 table (tid int, tname nvarchar(80))

insert into @t1 values (10,’Акулина’);
insert into @t1 values (20,’Бронислав’);
insert into @t1 values (30,’Богдан’);
insert into @t1 values (40,’Борислав’);

select tid, tname from @t1;

Преимущества:
• Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
• При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
• Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
• Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
• Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)

Недостатки:
• На табличных переменных нельзя создавать некластерные индексы
• Табличные переменные не содержат статистику
• Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
• Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса

В 2008 версии пошли немного дальше… и теперь, для того, что бы использовать какие-нибудь заранее предопределенные данные, уже не нужно создавать ни временную таблицу, ни табличную переменную…
Достаточно обойтись одним оператором value:

select * from (
values (10,’Акулина’)
, (20,’Бронислав’)
, (30,’Богдан’)
, (40,’Борислав’)
) t (tid, tname)

или например так:

select max(case when n=1 then > from
(
select sourceID id , ROW_NUMBER () over (order by sourceID) r
from LoyaltyDiff_BrokerMessagesReceived (nolock)
)t
cross join (values (0) ,(1))nn(n)

Вывод: Используйте новые возможности!

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.

В Microsoft SQL Server есть особый тип данных TABLE, на основе которого мы можем создавать табличные переменные, для того чтобы использовать их в своих инструкциях и процедурах, и сегодня мы с Вами рассмотрим эти переменные, узнаем, как они объявляются и какие у этих переменных особенности.

Описание табличных переменных MS SQL Server

Табличные переменные – это переменные с особым типом данных TABLE, которые используются для временного хранения результирующего набора данных в виде строк таблицы. Появились они еще в 2005 версии SQL сервера. Использовать такие переменные можно и в хранимых процедурах, и в функциях, и в триггерах, и в обычных SQL пакетах. Создаются табличные переменные так же, как и обычные переменные, путем их объявления инструкцией DECLARE.

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

Преимущества табличных переменных в Microsoft SQL Server

• Табличные переменные ведут себя как локальные переменные. Они имеют точно определенную область применения;
• Табличные переменные автоматически очищаются в конце инструкции, где они были определены;
• При использовании табличных переменных в хранимых процедурах повторные компиляции происходят реже, чем при использовании временных таблиц;
• Транзакции с использованием переменных TABLE продолжаются только во время процесса обновления соответствующей переменной. За счет этого табличные переменные реже подвергаются блокировке и требуют меньше ресурсов для ведения журналов регистрации.

Недостатки табличных переменных в MS SQL Server

• Запросы, которые изменяют переменные TABLE, не создают параллельных планов выполнения запроса;
• Переменные TABLE не имеют статистики распределения и не запускают повторных компиляций, поэтому рекомендуется использовать их для небольшого количества строк;
• Табличные переменные нельзя изменить после их создания;
• Табличные переменные нельзя создавать путем инструкции SELECT INTO;
• Переменные TABLE не изменяются в случае откатов транзакций, так как имеют ограниченную область действия и не являются частью постоянных баз данных.

Примеры использования табличных переменных в Microsoft SQL Server

Сейчас давайте перейдем к практике, и для начала хотелось бы отметить, что в качестве сервера у меня выступает Microsoft SQL Server 2016 Express, другими словами все запросы ниже запускались на данной версии СУБД.

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

С помощью инструкции CREATE TABLE я создал таблицу TestTable, затем для добавления данных в таблицу я использовал инструкцию INSERT совместно с конструктором табличных значений VALUES, затем с помощью SELECT сделал выборку из только что созданной таблицы.

Объявление табличной переменной и ее использование

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

Создание табличной переменной с первичным ключом, ограничением UNIQUE и с некластеризованным индексом

В данном примере показано, как можно создавать первичный ключ, ограничения UNIQUE и некластеризованные индексы для табличных переменных. Возможность создания некластеризованного индекса появилась, начиная с версии Microsoft SQL Server 2014.

На этом мой рассказ о табличных переменных закончен, если Вы хотите детально изучить язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL», надеюсь, материал был Вам полезен, пока!

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Специальный тип данных для хранения результирующего набора для обработки в будущем. Is a special data type used to store a result set for processing at a later time. Тип table используется в основном для временного хранения набора строк, возвращаемых как результирующий набор функции с табличным значением. table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. Функции и переменные могут быть объявлены как имеющие тип table. Functions and variables can be declared to be of type table. Переменные table могут использоваться в функциях, хранимых процедурах и пакетах. table variables can be used in functions, stored procedures, and batches. Для объявления переменных типа table используйте инструкцию DECLARE @local_variable. To declare variables of type table, use DECLARE @local_variable.

Применимо к: SQL Server SQL Server ( SQL Server 2008 SQL Server 2008 и выше), База данных SQL Azure Azure SQL Database . Applies to: SQL Server SQL Server ( SQL Server 2008 SQL Server 2008 and later), База данных SQL Azure Azure SQL Database .

Синтаксические обозначения в Transact-SQL Transact-SQL Syntax Conventions

Синтаксис Syntax

Аргументы Arguments

table_type_definition table_type_definition
То же подмножество данных, которое используется для определения таблицы с помощью инструкции CREATE TABLE. Is the same subset of information that is used to define a table in CREATE TABLE. Декларация таблицы включает определения столбцов, имен, типов данных и ограничений. The table declaration includes column definitions, names, data types, and constraints. К допустимым типам ограничений относятся только PRIMARY KEY, UNIQUE KEY и NULL. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Дополнительные сведения о синтаксисе см. в статьях CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) и DECLARE @local_variable (Transact-SQL). For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definition collation_definition
Параметры сортировки столбцов, состоящие из поддерживаемых Microsoft Microsoft Windows языкового стандарта и стиля сопоставления, языкового стандарта Windows и двоичной записи или параметров сортировки Microsoft Microsoft SQL Server SQL Server . Is the collation of the column that is made up of a Microsoft Microsoft Windows locale and a comparison style, a Windows locale, and the binary notation, or a Microsoft Microsoft SQL Server SQL Server collation. Если значение аргумента collation_definition не задано, столбец наследует параметры сортировки текущей базы данных. If collation_definition isn’t specified, the column inherits the collation of the current database. Либо, если столбец определен как имеющий определяемый пользователем тип данных среды CLR, он унаследует параметры сортировки этого определяемого пользователем типа. Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

Remarks Remarks

table — позволяет ссылаться на переменные по имени в пакетном предложении FROM, как показано в следующем примере: table Reference variables by name in a batch’s FROM clause, as shown the following example:

Вне предложения FROM на переменные table нужно ссылаться по псевдонимам, как показано в следующем примере: Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

Переменные table предоставляют указанные ниже преимущества для запросов малого масштаба, которые содержат неизменяющиеся планы запросов. Их также рекомендуется использовать при частой перекомпиляции. table variables provide the following benefits for small-scale queries that have query plans that don’t change and when recompilation concerns are dominant:

• Переменная table ведет себя как локальная переменная. A table variable behaves like a local variable. Она имеет точно определенную область применения. It has a well-defined scope. Эта переменная представлена функцией, хранимой процедурой или пакетом, в котором она объявлена. This variable is the function, stored procedure, or batch that it’s declared in.
  Внутри этой области переменная table может использоваться как обычная таблица. Within its scope, a table variable can be used like a regular table. Она может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. Но переменную table нельзя использовать в следующей инструкции: However, table can’t be used in the following statement:

Переменные table автоматически очищаются в конце функции, хранимой процедуры или пакета, в котором они были определены. table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they’re defined.

• При использовании переменных table в хранимых процедурах приходится реже прибегать к перекомпиляциям, чем при использовании временных таблиц в тех случаях, когда не требуется делать выбор на основе затрат, который влияет на производительность. table variables that are used in stored procedures cause fewer stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.
• Транзакции с использованием переменных table продолжаются только во время процесса обновления соответствующей переменной table. Transactions involving table variables last only for the duration of an update on the table variable. Поэтому переменные table реже подвергаются блокировке и требуют меньше ресурсов для ведения журналов. As such, table variables require less locking and logging resources.

ограничения Limitations and restrictions

Для переменных Table не предусмотрена статистика распределения. Table variables don’t have distribution statistics. Они не будут вызывать перекомпиляцию. They won’t trigger recompiles. Во многих случаях оптимизатор строит план запроса на предположении, что у табличной переменной нет строк. In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. По этой причине следует проявлять осторожность относительно использования табличной переменной, если ожидается большое число строк (больше 100). For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). В этом случае временные таблицы могут быть предпочтительным решением. Temp tables may be a better solution in this case. Для запросов, которые объединяют табличную переменную с другими таблицами, используйте указание RECOMPILE, чтобы оптимизатор использовал правильную кратность для табличной переменной. For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

Переменные table не поддерживаются в модели выбора на основе затрат оптимизатора SQL Server SQL Server . table variables aren’t supported in the SQL Server SQL Server optimizer’s cost-based reasoning model. Поэтому их не нужно использовать, если требуется принять решение на основе затрат, чтобы получить эффективный план запроса. As such, they shouldn’t be used when cost-based choices are required to achieve an efficient query plan. Временные таблицы являются предпочтительными при необходимости осуществления выбора с учетом затрат. Temporary tables are preferred when cost-based choices are required. Этот план обычно включает запросы с соединениями, решения в отношении параллелизма и варианты выбора индекса. This plan typically includes queries with joins, parallelism decisions, and index selection choices.

Запросы, изменяющие переменные table, не создают параллельных планов выполнения запроса. Queries that modify table variables don’t generate parallel query execution plans. При изменении больших переменных table или переменных table в сложных запросах может снизиться производительность. Performance can be affected when large table variables, or table variables in complex queries, are modified. В ситуациях с изменением переменных table мы рекомендуем использовать временные таблицы. Consider using temporary tables instead in situations where table variables are modified. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL). For more information, see CREATE TABLE (Transact-SQL). Запросы, которые считывают переменные table, не изменяя их, могут выполняться параллельно. Queries that read table variables without modifying them can still be parallelized.

Для переменных table нельзя явно создавать индексы, при этом статистика для переменных table не сохраняется. Indexes can’t be created explicitly on table variables, and no statistics are kept on table variables. Начиная с SQL Server 2014 (12.x) SQL Server 2014 (12.x) , реализован новый синтаксис, который позволяет создавать определенные встроенные типы индекса с использованием определения таблицы. Starting with SQL Server 2014 (12.x) SQL Server 2014 (12.x) , new syntax was introduced which allows you to create certain index types inline with the table definition. С помощью этого нового синтаксиса можно создавать индексы в переменной table как часть определения таблицы. Using this new syntax, you can create indexes on table variables as part of the table definition. В некоторых случаях можно добиться повышения производительности за счет использования временных таблиц, которые позволяют работать с индексами и статистикой. In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Дополнительные сведения о временных таблицах и создании встроенных индексов см. в руководстве по использованию CREATE TABLE (Transact-SQL). For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

Ограничения CHECK, значения DEFAULT и вычисляемые столбцы в объявлении типа table не могут вызывать определяемые пользователем функции. CHECK constraints, DEFAULT values, and computed columns in the table type declaration can’t call user-defined functions.

Операция присвоения между переменными table не поддерживается. Assignment operation between table variables isn’t supported.

Так как переменные table имеют ограниченную область действия и не являются частью постоянной базы данных, они не изменяются при откатах транзакций. Because table variables have limited scope and aren’t part of the persistent database, transaction rollbacks don’t affect them.

Табличные переменные нельзя изменить после их создания. Table variables can’t be altered after creation.

Отложенная компиляция табличных переменных Table variable deferred compilation

Отложенная компиляция табличных переменных позволяет оптимизировать план и повысить общую производительность для запросов, ссылающихся на табличные переменные. Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Во время оптимизации и первичной компиляции плана эта функция будет распространять оценки кратности, основанные на фактическом числе строк табличных переменных. During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Эти точные сведения о числе строк затем будут использоваться для оптимизации последующих операций планирования. This exact row count information will then be used for optimizing downstream plan operations.

Функция "Отложенная компиляция табличных переменных" предоставляется в режиме общедоступной предварительной версии в База данных SQL Azure Azure SQL Database и SQL Server 2019 (15.x) SQL Server 2019 (15.x) . Table variable deferred compilation is a public preview feature in База данных SQL Azure Azure SQL Database and SQL Server 2019 (15.x) SQL Server 2019 (15.x) .

При отложенной компиляции табличных переменных компиляция инструкции со ссылкой на табличную переменную откладывается до момента первого фактического выполнения инструкции. With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Это поведение отложенной компиляции совпадает с поведением временных таблиц. This deferred compilation behavior is identical to the behavior of temporary tables. Такое изменение позволяет использовать реальную кратность вместо обычного предположения по одной строке. This change results in the use of actual cardinality instead of the original one-row guess.

Чтобы включить общедоступную предварительную версию отложенной компиляции табличных переменных, активируйте уровень совместимости 150 для базы данных, к которой вы подключаетесь при выполнении запроса. To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you’re connected to when the query runs.

При отложенной компиляции табличных переменных другие характеристики табличных переменных не изменяются. Table variable deferred compilation doesn’t change any other characteristics of table variables. Например, в табличные переменные не добавляется статистика по столбцам. For example, this feature doesn’t add column statistics to table variables.

Также при использовании этой функции не повышается частота перекомпиляции. Table variable deferred compilation doesn’t increase recompilation frequency. Эта функция эффективна при начальной компиляции. Rather, it shifts where the initial compilation occurs. Итоговый кэшированный план создается на основе числа строк табличных переменных начальной отложенной компиляции. The resulting cached plan generates based on the initial deferred compilation table variable row count. Кэшированный план повторно используется последующими запросами The cached plan is reused by consecutive queries. до тех пор, пока план не будет исключен или перекомпилирован. It’s reused until the plan is evicted or recompiled.

Число строк табличных переменных, используемых для начальной компиляции плана, представляет стандартное значение, которое может отличаться от предположительного фиксированного числа строк. Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. Если оно отличается, последующие операции будут более производительными. If it’s different, downstream operations will benefit. Если число строк табличных переменных существенно меняется при каждом выполнении, эта функция не поможет повысить производительность. Performance may not be improved by this feature if the table variable row count varies significantly across executions.

Отключение отложенной компиляции табличной переменной без изменения уровня совместимости Disabling table variable deferred compilation without changing the compatibility level

Отключите отложенную компиляцию табличной переменной в области базы данных или инструкции, сохранив уровень совместимости базы данных 150 и выше. Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. Чтобы отключить отложенную компиляцию табличной переменной для всех запросов, поступающих из базы данных, выполните следующий пример в контексте соответствующей базы данных: To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

Чтобы повторно включить отложенную компиляцию табличной переменной для всех запросов, поступающих из базы данных, выполните следующий пример в контексте соответствующей базы данных: To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

Вы также можете отключить отложенную компиляцию табличной переменной для определенного запроса, назначив DISABLE_DEFERRED_COMPILATION_TV в качестве указания запроса USE HINT. You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. Пример: For example:

Примеры Examples

A. A. Объявление переменной типа table Declaring a variable of type table

В следующем примере создается переменная типа table , в которой хранятся значения, задаваемые в предложении OUTPUT инструкции UPDATE. The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Две следующие инструкции SELECT возвращают значения в табличную переменную @MyTableVar , а результаты операции обновления — в таблицу Employee . Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Результаты в столбце INSERTED.ModifiedDate отличаются от значений в столбце ModifiedDate таблицы Employee . Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. Это связано с тем, что триггер AFTER UPDATE , обновляющий значение ModifiedDate до текущей даты, был определен для таблицы Employee . This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Однако столбцы, возвращенные из OUTPUT , отражают состояние данных перед срабатыванием триггеров. However, the columns returned from OUTPUT reflect the data before triggers are fired. Дополнительные сведения см. в статье Предложение OUTPUT (Transact-SQL). For more information, see OUTPUT Clause (Transact-SQL).

Б. B. Создание встроенной функции с табличным значением Creating an inline table-valued function

Результатом следующего примера является встроенная функция, возвращающая табличное значение. The following example returns an inline table-valued function. Для каждого из товаров, проданных в магазине, она возвращает три столбца: ProductID , Name и статистику с начала года по магазину — YTD Total . It returns three columns ProductID , Name , and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

При вызове этой функции выполняется следующий запрос. To invoke the function, run this query.