Прежде, чем я расскажу о проблемах, с которыми вы можете столкнуться при использовании временных таблиц, немного теории.
Временные таблицы отличаются от постоянных только тем, что хранятся в базе данных tempdb и автоматически удаляются, когда необходимость в них отпадает.
Существует два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных таблиц начинаются с одного символа (#); они видны только текущему соединению пользователя и удаляются, когда пользователь отключается от экземпляра SQL Server. Имена глобальных таблиц начинаются с двух символов номера (##); они видны любому пользователю и удаляются, когда все пользователи, которые на них ссылаются, отключаются от экземпляра SQL Server.
Например, если создать таблицу employees, она будет доступна любому пользователю, которому предоставлены разрешения на ее использование до тех пор, пока не будет удалена. Если во время сеанса базы данных создается локальная временная таблица #employees, с ней сможет работать только данный сеанс. Таблица будет удалена при завершении сеанса. Если создать глобальную временную таблицу ##employees, с ней сможет работать любой пользователь базы данных. Если другие пользователи не будут работать с этой таблицей, она будет удалена после отключения от нее. Если другой пользователь обратится к созданной таблице, SQL Server удалит ее, когда произойдет отключение и другие сеансы перестанут активно к ней обращаться.
А теперь выдержка из BOL, которой я коснусь в этой статье:
Если локальная временная таблица создается хранимой процедурой или приложением, которые одновременно могут выполняться несколькими пользователями, компонент Database Engine должен иметь возможность различать таблицы, созданные разными пользователями. Компонент Database Engine делает это путем внутреннего присоединения числового суффикса к имени каждой локальной временной таблицы. Полное имя временной таблицы, хранящееся в таблице sysobjects базы данных tempdb, состоит из имени таблицы, заданного инструкцией CREATE TABLE, и сформированного системой числового суффикса. Для обеспечения возможности добавления суффикса значение параметра table_name, определенного как имя локальной временной таблицы, не должно содержать более 116 символов.
Действительно, если мы создадим временную таблицу и посмотрим её "ностоящее" наименование в tempdb, то мы увидем совсем не то имя, которое ей давали:
use master
go
create table #t ( id int, val1 varchar(10), val2 datetime )
go
select top 1 * from tempdb.sys.tables
order by create_date desc
go
Как видно на рисунке, таблица создана с уникальным именем. Не смотря на то, что у таблицы такое странное наименование, мы можем обращаться к этой таблице по короткому имени, по тому с которым мы её создали. При этом из другого сеанса к этой таблице нельзя обращаться ни по короткому ни по полному имени, т.к. для другой сеанса наша таблица находится вне области видимости. При этом в другом сеансе можно спокойно создать таблицу с тем же именем не переживая, что сиквел выругается на то, что такой объект уже существует в БД. Но, как оказывается, не всё так гладко. Компонент Database Engine создаёт уникальное имя только для самой таблицы, а для констрейнтов этой таблицы в tempdb уникальность наименования не поддерживается. Для иллюстрации описанной проблемы, немного изменим предыдущий скрипт создания временной таблицы:
use master
go
create table #t ( id int, val1 varchar(10), val2 datetime constraint df_dt default getdate() )
go
select top 1 * from tempdb.sys.tables
order by create_date desc
go
И теперь если запустить этот код в двух разных сеансах, то в первом код успешно отработает: 
А во втором получим ошибку
Msg 2714, Level 16, State 5, Line 1 There is already an object named 'df_dt' in the database.
Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
Констрейнт имеет име 'df_dt', т.е. мы не получили уникальность, как это происходит с именем таблицы. Именно поэтому мы и увидели столь неочевидную ошибку.
Кроме того мы знаем, что
Локальная временная таблица, созданная хранимой процедурой, удаляется автоматически при завершении хранимой процедуры. К этой таблице могут обращаться любые вложенные хранимые процедуры, выполняемые хранимой процедурой, создавшей таблицу. Процесс, вызвавший хранимую процедуру, создавшую таблицу, к этой таблице обращаться не может.
Продолжим наши эксперименты и попробуем вставить код создания таблицы в процедуру:
if object_id( 'dbo.Test_Procedure', 'P' ) is not null
drop procedure dbo.Test_Procedure
go
create procedure dbo.Test_Procedure
as
create table #t ( id int, val1 varchar(10), val2 datetime constraint df_dt default getdate() )
go
Думаю многие из вас, перед выходом из процедуры, не "подчищают" за собой, т.к. временная таблица, созданная хранимой процедурой, удаляется автоматически при завершении хранимой процедуры. Для илюстрации этого можно запустить код вызова нашей процедуры и посмотреть, что лежит в tempdb ниже вызова процедуры:
exec dbo.Test_Procedure
go
select top 2 * from tempdb.sys.objects
order by create_date desc
go
При этом мы не увидим нашей таблицы среди объектов tempdb. Но если запустить вызов процедуры в транзакции, то мы получим совершенно другую картину:
begin tran
go
exec dbo.Test_Procedure
go
select top 2 * from tempdb.sys.objects
order by create_date desc
go
Т.е. наши объекты не были автоматически удалены при выходе из процедуры и они "живут" в tempdb до завершения транзакции. При этом обратиться к нашей таблице из вне процедуры не выйдет, т.к. область видимости ограничивается телом процедуры. Теперь, если мы решим запустить нашу процедуру в одном сеансе дважды в транзакции:
begin tran
go
exec dbo.Test_Procedure
go
exec dbo.Test_Procedure
go
То опять получим ошибку, связанную с тем, что имя констрейнта не уникально:
Msg 2714, Level 16, State 5, Procedure Test_Procedure, Line 3
There is already an object named 'df_dt' in the database.
Msg 1750, Level 16, State 0, Procedure Test_Procedure, Line 3
Could not create constraint. See previous errors.
Мы знаем, что при удалении таблицы, удалятся и все её дочерние объекты (констрейнты, триггеры, индексы и т.д.). Казалось бы логично перед созданием таблицы проверять факт её наличия в БД и предварительно её удалять, чтоб удалились и все связанные с ней объекты. Но с временной таблицей этот номер не выйдет из-за её особенности создавать уникальное име в tempdb.
Но это ещё не всё, теперь попробуем запустить нашу процедуру в транзакции, но в разных сеансах.
begin tran
go
exec dbo.Test_Procedure
go
Не закрывая транзакции, создадим новый сеанс и попробуем запустить нашу процедуру. И вот ещё одна неочевидная проблема, второй сеанс будет находиться в ожидании завершения транзакции, начатой в первом сеансе.
--Первый сеанс
exec sp_lock 52
--Второй сеанс
exec sp_lock 53
Причиной блокировки стал наш констрейнт, а т.к. блокировки Sch-M и Sch-S не совместимы(
http://msdn.microsoft.com/ru-ru/library/ms186396.aspx), то второй сеанс ожидает высвобождения ресурсов.
Именно из-за своей неочевидности эти особенности работы с локальными временными таблицами заслуживают внимания. Проблему можно решить несколькими способами:
1) Удалять временные объекты, перед выходом из процедуры
2) Не создавать явное наименование для констрейнтов на временных таблицах, тогда наш код создания таблицы можно изменить на указанный ниже, и сиквел сам сгенерит своё уникальное имя для объектов:
use master
go
create table #t ( id int, val1 varchar(10), val2 datetime default getdate() )
go
select top 2 * from tempdb.sys.objects
order by create_date desc
go
Была даже идея оформить фидбэк, но это уже сделали до меня...ещё в далёком 2007-ом году (
http://connect.microsoft.com/SQLServer/feedback/details/250046/temporary-tables-with-named-constraints). Официальный ответ:
by design, но возможно в будущих релизах ситуация изменится.
Тесты я проводил на
Microsoft SQL Server "Denali" (CTP3) - 11.0.1440.19 (X64)
Jun 24 2011 17:31:09
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Сомневаюсь, что в ближайшее время ситуация изменится, поэтому будте внимательны при работе с временными таблицами!!!
Подписаться