Использование данных типа sql_variant
Тип данных sql_variant работает так же, как и тип данных variant в Microsoft Visual Basic. Тип sql_variant позволяет в одном столбце, параметре или переменной хранить значения, относящиеся к различным типам данных. Например, один столбец sql_variant может хранить значение int, decimal, char, binary или nchar. Каждый экземпляр столбца sql_variant хранит значение данных и метаданные, которые определяют базовый тип данных, максимальный размер, масштаб, точность и параметры сортировки.
Правила использования sql_variant
При использовании типа данных sql_variant применяются следующие правила:
Общее присваивание значения
Объекты типа sql_variant могут хранить данные SQL Server любого типа, кроме text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp и определяемых пользователем типов данных Microsoft CLR .NET Framework. Экземпляр данных sql_variant не может также иметь sql_variant в качестве базового типа.
В предикатах и операциях присваивания для столбца sql_variant может быть указан любой тип константы.
Если значение типа sql_variant равно NULL, предполагается, что у него нет соответствующего базового типа. Это правило применяется даже в том случае, если значение NULL получено из столбца или переменной какого-либо определенного типа данных.
В следующем примере столбцу VariantCol присваивается значение NULL без какого-либо связанного типа данных, даже если значение NULL получено из переменной типа int:
DECLARE @IntVar int
SET @IntVar = NULL
UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123
При присваивании объекта sql_variant объекту другого типа данных значение sql_variant должно быть явно приведено к этому типу, так как при присваивании значения sql_variant объекту другого типа неявные преобразования не поддерживаются.
Для совместимости с другими типами данных объекты каталога (например, функция DATALENGTH) возвращают длину объектов sql_variant по фактической длине данных. Длина метаданных, которые содержат объект sql_variant, не возвращается.
Столбцы типа sql_variant всегда работают в режиме ANSI_PADDING ON. Если значения типа char, nchar, varchar, nvarchar или varbinary присваиваются из источника, для которого установлен режим ANSI_PADDING OFF, эти значения не дополняются.
Обновление одного столбца у подписчика ведет к изменению базового типа в другом столбце sql_variant. Это иллюстрирует следующая процедура.
Создайте публикацию или подписку слиянием. Опубликованная таблица должна иметь столбец sql_variant и столбец c1. Добавьте данные в столбец sql_variant. Базовым является тип данных datetime.
После первичной синхронизации у подписчика базовым типом данных остается datetime.
Обновите столбец c1 у подписчика.
Тип данных в столбце sql_variant у издателя изменился на datetime2.
Тип sql_variant в таблицах
Столбцы типа sql_variant могут использоваться в индексах и уникальных ключах, если длина данных в ключевом столбце не превышает 900 байт.
Столбцы типа sql_variant не поддерживают свойство IDENTITY, но для столбцов типа sql_variant допускается участие в первичном или внешнем ключе.
Столбцы типа sql_variant не могут использоваться в вычисляемых столбцах.
Инструкция ALTER TABLE позволяет изменить тип данных любого столбца, кроме text, ntext, image, timestamp или sql_variant, на sql_variant. Все существующие значения преобразуются в значения sql_variant, имеющие тот же базовый тип, который столбец имел до выполнения инструкции ALTER TABLE. Инструкция ALTER TABLE не позволяет изменить тип данных столбца sql_variant на какой-либо другой, так как неявные преобразования из sql_variant в другие типы данных не поддерживаются.
Параметры сортировки
Предложение COLLATE не может быть указано для назначения параметров сортировки столбцу sql_variant. Символьные значения (char, nchar, varchar и nvarchar) в столбце sql_variant могут иметь любые параметры сортировки, и поэтому один и тот же столбец sql_variant может одновременно содержать значения с различными параметрами сортировки.
При присваивании значения экземпляру типа sql_variant сохраняются и значение, и базовый тип данных. Если исходное значение имеет параметры сортировки, они также будет сохранены. Если присваиваемое значение относится к определяемому пользователем типу данных, сохраняется базовый тип, а не сам определяемый пользователем тип. Экземпляр типа sql_variant не наследует ни правила, ни значения по умолчанию, привязанные к определяемому пользователем типу данных. Если экземпляру типа sql_variant присваивается значение столбца со свойством IDENTITY, sql_variant принимает базовый тип исходного столбца, но не наследует свойство IDENTITY. Присваивание значения типа text, ntext или image экземпляру типа sql_variant приводит к ошибке. Для присваивания объекту типа sql_variant значений других типов данных поддерживается неявное преобразование.
Сравнение данных типа sql_variant
Столбцы типа sql_variant могут содержать значения в различных базовых типах и параметрах сортировки, поэтому для типа sql_variant применяются специальные правила при сравнении операндов, то есть в следующих операциях:
Операторы сравнения языка Transact-SQL
ORDER BY, GROUP BY
Индексы
Статистические функции MAX и MIN
UNION (без ALL)
Выражения CASE
Для сравнения данных типа sql_variant иерархия типов данных SQL Server разбивается на семейства типов. Семейство sql_variant имеет наивысший приоритет.
Иерархия типов данных |
Семейство типов данных |
|---|---|
sql_variant |
sql_variant |
datetime |
Дата и время |
smalldatetime |
Дата и время |
Float |
Приблизительное числовое значение |
Real |
Приблизительное числовое значение |
decimal |
Точное числовое значение |
money |
Точное числовое значение |
smallmoney |
Точное числовое значение |
bigint |
Точное числовое значение |
int |
Точное числовое значение |
smallint |
Точное числовое значение |
tinyint |
Точное числовое значение |
bit |
Точное числовое значение |
nvarchar |
Юникод |
nchar |
Юникод |
varchar |
Юникод |
char |
Юникод |
varbinary |
Двоичный |
binary |
Двоичный |
uniqueidentifier |
Уникальный идентификатор |
При сравнении со значением типа sql_variant применяются следующие правила:
Если сравниваемые значения типа sql_variant имеют разные базовые типы и относятся к разным семействам, то значение, семейство типов которого находится выше в иерархии, считается большим.
Если сравниваемые значения типа sql_variant имеют разные базовые типы и относятся к одному семейству типов, значение, базовый тип которого находится ниже в иерархии, неявно преобразуется ко второму участвующему в сравнении типу, а затем выполняется сравнение двух значений.
Если сравниваются значения sql_variant типов данных char, varchar, nchar или varchar, они вычисляются по следующим критериям: код языка (LCID), версия кода языка, флаги сравнения и идентификатор сортировки. Сравнение выполняется по каждому из этих критериев в указанном порядке, критерии трактуются как целочисленные значения.
Эти правила могут выдавать при сравнении значений типа sql_variant результаты, отличные от результатов сравнения тех же значений в соответствующих базовых типах.
Операнд A |
Операнд B |
Результат сравнения в базовых типах |
Результат сравнения в sql_variant |
|---|---|---|---|
'123' char |
111 int |
A > B |
B > A |
50000 int |
5E1 float |
A > B |
B > A |
Поскольку значения для различных семейств типов должны быть явно приведены перед указанием в предикатах сравнения, результаты применения этих правил заметны только при сортировке результирующего набора по столбцу типа sql_variant. Значения в следующей таблице иллюстрируют применение правил с учетом приоритетов типов данных.
PriKey |
VariantCol |
|---|---|
1 |
50,0 (базовый тип float) |
2 |
5000 (базовый тип int) |
3 |
'124000' (базовый тип char(6)) |
В следующей таблице содержатся результаты выполнения инструкции: SELECT * FROM VariantTest ORDER BY VariantCol ASC.
PriKey |
VariantCol |
|---|---|
3 |
'124000' (базовый тип char(6)) |
2 |
5000 (базовый тип int) |
1 |
50,0 (базовый тип float) |
Значения в следующей таблице иллюстрируют применение правил с учетом очередности параметров сортировки.
IntKey |
VariantCol |
|---|---|
1 |
qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
2 |
abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
3 |
qrs (varchar SQL_Latin1_General_CP1_CS_AS) |
4 |
17,5 (десятичное) |
5 |
abc (varchar SQL_Latin1_General_CP1_CS_AS) |
6 |
klm (varchar SQL_Latin1_General_CP1_CS_AS) |
7 |
1,2 (десятичное) |
В следующей таблице содержатся результаты выполнения инструкции: SELECT * FROM CollateTest ORDER BY VariantCol. В таблице содержатся значения семейства точных числовых типов данных и значения varchar, сгруппированные по соответствующим параметрам сортировки.
IntKey |
VariantCol |
|---|---|
5 |
abc (varchar SQL_Latin1_General_CP1_CS_AS) |
6 |
klm (varchar SQL_Latin1_General_CP1_CS_AS) |
3 |
qrs (varchar SQL_Latin1_General_CP1_CS_AS) |
2 |
abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
1 |
qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS) |
7 |
1,2 (десятичное) |
4 |
17,5 (десятичное) |
Функции и данные типа sql_variant
Следующие функции языка Transact-SQL поддерживают параметры типа sql_variant и возвращают значения типа sql_variant, если указан параметр типа sql_variant:
COALESCE |
MIN |
MAX |
NULLIF |
Следующие функции поддерживают ссылки на столбцы и переменные типа sql_variant, но не возвращают значения типа sql_variant:
COL_LENGTH |
DATALENGTH |
TYPEPROPERTY |
COLUMNPROPERTY |
ISNULL |
Следующие функции языка Transact-SQL не поддерживают параметры типа sql_variant:
AVG |
RADIANS |
STDEV[P] |
IDENTITY |
ROUND |
SUM |
ISNUMERIC |
SIGN |
VAR[P] |
POWER |
Функции CAST и CONVERT поддерживают тип sql_variant.
Новая функция SQL_VARIANT_PROPERTY() предназначена для получения сведений о свойствах значения типа sql_variant (типа данных, точности или масштаба).
Другие элементы языка Transact-SQL и данные типа sql_variant
Столбцы типа sql_variant не поддерживаются в предикате LIKE.
Столбцы типа sql_variant не поддерживаются в полнотекстовых индексах. Столбец типа sql_variant не может быть указан в полнотекстовых функциях, таких как CONTAINSTABLE и FREETEXTTABLE.
Следующие инструкции языка Transact-SQL поддерживают указание sql_variant в тех же синтаксических позициях, в которых указываются целочисленные типы данных:
ALTER PROCEDURE
ALTER TABLE
CREATE PROCEDURE
CREATE TABLE
DECLARE variable
Компоненты каталога SQL Server выдают сведения о столбцах типа sql_variant.
Результатом выражения CASE является значение типа sql_variant, если одно из входных или результирующих выражений имеет значение типа sql_variant. Базовый тип результата — такой же, как и у результата вычисления выражения на стадии выполнения.
Операнды операторов объединения строк или чисел не могут иметь тип sql_variant. Например, следующий код вызывает ошибку:
SELECT VariantCol + @CharacterVar
FROM MyTable
Но после приведения операнда типа sql_variant к другому типу эта операция может быть выполнена:
SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar
FROM MyTable
Приложения и данные типа sql_variant
Если приложение запрашивает результирующий набор, в котором один из столбцов возвращает данные типа sql_variant, относящиеся к одному и тому же базовому типу данных, приложение может пользоваться функциями CAST и CONVERT в инструкциях языка Transact-SQL, чтобы вернуть sql_variant в базовом типе данных. В этом случае приложение трактует их как столбец результирующего набора, относящийся к базовому типу данных.
Поставщик OLE DB для собственного клиента SQL Server для работы со столбцами и параметрами типа sql_variant поддерживает собственный тип OLE DB DBTYPE_SQLVARIANT.
Драйвер ODBC для собственного клиента SQL Server SQL Server для работы со столбцами и параметрами типа sql_variant поддерживает собственный тип данных ODBC SQL_SS_VARIANT.
SQL Server преобразует значения типа sql_variant в nvarchar(4000) при работе с приложениями, подключаемыми через следующие интерфейсы:
Поставщик OLE DB для SQL Server версии 7.0.
Драйвер ODBC SQL Server от SQL Server 7.0.
Если длина строки результата превышает 4000 символов, то SQL Server возвращает первые 4000 символов.
SQL Server преобразует тип данных sql_variant в тип varchar(255) при работе с приложениями, которые соединяются через следующие интерфейсы.
- Драйверы ODBC SQL Server от SQL Server версии 6.5 и более ранних.
Если длина строки результата превышает 255 символов, то SQL Server возвращает первые 255 символов.