При работе с SQL Server часто существует несколько способов достижения одного и того же результата. Одна из таких ситуаций – использование переменных таблиц и временных таблиц. Хотя существует множество статей и обсуждений о различиях и сходствах между этими двумя вариантами, есть некоторые малоизвестные различия, которые стоит изучить.
XML-коллекция
Одно из ключевых различий между переменными таблицами и временными таблицами – это то, как они обрабатывают XML-коллекции. Если у вас есть XML-коллекция в вашей базе данных и вы пытаетесь использовать ее во временной таблице, вы можете столкнуться с ошибкой. Это происходит потому, что временные таблицы создаются в TempDB, которая не имеет доступа к XML-коллекции. Один из способов обойти это – использовать неопределенный XML во временной таблице вместо этого.
CREATE TABLE #TestXmls (
JobCandidateID int NOT NULL,
[Resume] xml PRIMARY KEY CLUSTERED(JobCandidateID)
)
INSERT #TestXmls (JobCandidateID, [Resume])
SELECT JobCandidateID, [Resume] FROM HumanResources.JobCandidate
SELECT * FROM #TestXmls
Пользовательские типы данных и пользовательские типы (UDT)
Подобно XML-коллекциям, пользовательские типы данных (UDT) и пользовательские типы (CLR-типы) ограничены базой данных, в которой они созданы. Если вы попытаетесь использовать UDT во временной таблице, вы можете столкнуться с ошибкой. Один из способов обойти это – использовать собственный тип данных UDT вместо этого во временной таблице.
CREATE TABLE #TestUDTs (
AddressTypeID int NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID)
)
INSERT #TestUDTs (AddressTypeID, [Name])
SELECT AddressTypeID, [Name] FROM Person.AddressType
SELECT * FROM #TestUDTs
Сортировка
Сортировка также может быть фактором при использовании переменных таблиц и временных таблиц. Если сортировка для строковых столбцов не указана явно, временные таблицы унаследуют сортировку TempDB, в то время как переменные таблицы унаследуют сортировку текущей пользовательской базы данных. Это может привести к конфликтам сортировки при сравнении строк. Один из способов решения – принудительное преобразование сортировки при сравнении строк или явное указание сортировки для строкового столбца во временной таблице.
CREATE TABLE #TestCollations (
RID int identity not null,
[Name] nvarchar(100) collate database_default NOT NULL PRIMARY KEY CLUSTERED(RID)
)
INSERT #TestCollations ([Name]) VALUES (N'R2')
INSERT #TestCollations ([Name]) VALUES (N'R3')
SELECT A.* FROM dbo.Test1 A, #TestCollations B WHERE A.C1=B.[Name]
Вывод
При выборе между переменными таблицами и временными таблицами важно учитывать эти различия. Переменные таблицы позволяют использовать пользовательские типы данных, UDT и XML-коллекции, определенные в вашей базе данных, без проблем. Однако, если вам нужно использовать временную таблицу, лучше следовать этим bewt практикам:
- Всегда добавляйте фразу “collate database_default” для строковых столбцов во временной таблице.
- Всегда используйте неопределенный XML для столбцов XML во временной таблице.
- Всегда используйте эквивалентный собственный тип данных для пользовательского типа данных во временной таблице.
- Если вам нужно использовать UDT во временной таблице, зарегистрируйте тип в TempDB.
Понимая эти различия и следуя лучшим практикам, вы можете принимать обоснованные решения при работе с переменными таблицами и временными таблицами в SQL Server.