Использование данных типа 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 2005, кроме 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 могут использоваться в индексах и уникальных ключах, если длина данных в ключевом столбце не превышает 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 2005 выдают сведения о столбцах типа 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 для работы со столбцами и параметрами типа sql_variant поддерживает собственный тип OLE DB DBTYPE_SQLVARIANT.
Драйвер ODBC для собственного клиента SQL Server SQL для работы со столбцами и параметрами типа 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 символов.
См. также
Другие ресурсы
Функции CAST и CONVERT (Transact-SQL)
sql_variant (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)
Типы данных (Transact-SQL)