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

  • Статья

Использование специальных типов данных

В данном разделе описываются особые типы данных, доступные в SQL Server. Типы специальных данных не подходят ни к одной из других категорий типов данных. В SQL Server присутствуют следующие особые типы данных: bit, hierarchyid, sql_variant, sysname, table, timestamp, а также псевдонимы типов данных.

Тип данных bit

Тип данных bit является численным типом данных, принимающим значения 1 или 0. Строковые значения true и false можно преобразовать в значения типа bit так, как это показано в следующем примере.

SELECT CONVERT (bit, 'true')
SELECT CONVERT(bit, 'false')

В данном примере значение true преобразуется в значение 1, а значение false преобразуется в значение 0. Данные типа bit не должны быть заключены в одинарные кавычки.

Тип данных hierarchyid

Тип данных hierarchyid используется для управления иерархическими данными и таблицами, имеющими иерархическую структуру. Для работы с иерархическими данными в коде Transact-SQL следует использовать функции hierarchyid. Дополнительные сведения см. в разделе Использование типов данных hierarchyid (компонент Database Engine).

Тип данных sql_variant

Тип данных sql_variant позволяет сохранять данные различных типов в одном столбце, параметре или переменной. В каждом экземпляре столбца типа sql_variant хранятся значения и метаданные, описывающие эти значения. Доступны следующие метаданные.

  • Базовый тип данных

  • Максимальный размер

  • Масштаб

  • Точность

  • Параметры сортировки

Для получения метаданных определенного экземпляра sql_variant следует использовать функцию SQL_VARIANT_PROPERTY.

В следующем примере во второй таблице хранится столбец sql_variant.

CREATE TABLE ObjectTable (
   ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY,
   ObjectName nvarchar(80),
   ObjectWeight decimal(10,3),
   ObjectColor nvarchar(20))
CREATE TABLE VariablePropertyTable (
   ObjectID int REFERENCES ObjectTable(ObjectID),
   PropertyName nvarchar(100),
   PropertyValue sql_variant,
   CONSTRAINT PKVariablePropertyTable
   PRIMARY KEY(ObjectID, PropertyName))

Тип данных sysname

Тип данных sysname используется в столбцах таблицы, переменных и параметрах хранимых процедур, содержащих названия объектов. Точное определение sysname зависит от установленных для идентификаторов правил именования. Таким образом, оно может быть различным для различных экземпляров SQL Server. Тип данных sysname функционально эквивалентен типу nvarchar(128), за исключением того, что по умолчанию sysname имеет значение NOT NULL. В более ранних версиях SQL Server тип sysname определен как varchar(30).

Важное примечаниеВажно!

В базах данных, учитывающих регистр или использующих двоичную сортировку, тип данных sysname определяется как системный тип данных SQL Server лишь тогда, когда он введен в нижнем регистре.

Тип данных table

Тип данных table используется для определения временных таблиц. В переменных этого типа сохраняются результирующие наборы для последующей обработки. Этот тип данных может использоваться только для определения локальных переменных типа table и значений, возвращаемых пользовательской функцией.

Определение табличной переменной или возвращаемого значения включает определения столбцов, типа данных, точности и размера каждого столбца, необязательных ограничений PRIMARY KEY, UNIQUE, NULL или CHECK. Определенная пользователем таблица не может быть использована в качестве типа данных.

Формат строк, хранимых в переменной table или возвращаемых определяемой пользователем функцией, должен быть определен при объявлении переменной или при создании функции. Синтаксис основан на синтаксисе инструкции CREATE TABLE, как показано в следующем примере.

DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
SELECT * FROM @TableVar
GO

Переменные table и пользовательские функции, которые возвращают table, можно использовать в определенных инструкциях SELECT и INSERT и в инструкциях UPDATE, DELETE и DECLARE CURSOR, поддерживающих таблицы. Переменные table и пользовательские функции, которые возвращают table, нельзя использовать в инструкциях Transact-SQL.

Индексы и другие применимые к таблице ограничения задаются как часть переменной DECLARE в инструкции CREATE FUNCTION. Они не могут быть применены позднее, потому что инструкции CREATE INDEX и ALTER TABLE не могут ссылаться на табличные переменные и определяемые пользователем функции.

Дополнительные сведения о синтаксисе, используемом для определения переменных и определяемых пользователем функций типа table, см. в разделах DECLARE @local_variable (Transact-SQL) и CREATE FUNCTION (Transact-SQL).

Тип данных timestamp

Тип данных timestamp не связан со временем или с датой. Значения timestamp являются двоичными числами, указывающими относительную последовательность, в которой происходило изменение данных в базе данных.

Никогда не используйте столбцы timestamp в качестве ключей, особенно первичных ключей, потому что значение timestamp изменяется всякий раз при изменении строки.

Чтобы записывать время, когда происходило изменение данных в таблице, используйте либо тип данных datetime2, либо smalldatetime для записи событий. Используйте триггеры для автоматического обновления значений всякий раз, когда происходят изменения.

Псевдонимы типов данных

Псевдонимы типов данных позволяют расширить базовые типы данных SQL Server (например, varchar) содержательным именем и форматом, который можно приспособить для конкретного использования. Например, следующая инструкция реализует определяемый пользователем тип данных birthday, основанный на типе данных datetime, допускающий значения NULL.

EXEC sp_addtype birthday, datetime, 'NULL'

Будьте внимательны при выборе базовых типов, применяемых для определяемых пользователем типов данных. Например, в США номера социального страхования (SSN) имеют формат nnn-nn-nnnn. Хотя номера социального страхования содержат числа, эти числа образуют идентификатор и к ним нельзя применять математические операции. Поэтому обычно определяется пользовательский тип данных varchar для номера социального страхования и создается следующее проверочное ограничение, обеспечивающее необходимый формат номера социального страхования.

EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage (EmployeeID int PRIMARY KEY, EmployeeSSN SSN, CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
)
GO

Если столбцы SSN используются в качестве ключевых столбцов в индексах, особенно в кластеризованных индексах, то размер ключей можно сократить с 11 байт до 4 байт, если определяемый пользователем тип данных SSN определен с использованием базового типа данных int. Уменьшение размера ключа улучшает извлечение данных. Улучшение эффективности извлечения данных и устранение необходимости проверочного ограничения CHECK обычно перевешивают издержки, связанные с преобразованием типа int к символьному формату при отображении или изменении значений SSN.