Поделиться через

  • Статья

Использование данных типа 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. Это иллюстрирует следующая процедура.

    1. Создайте публикацию или подписку слиянием. Опубликованная таблица должна иметь столбец sql_variant и столбец c1. Добавьте данные в столбец sql_variant. Базовым является тип данных datetime.

    2. После первичной синхронизации у подписчика базовым типом данных остается datetime.

    3. Обновите столбец c1 у подписчика.

    4. Тип данных в столбце 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 символов.