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

Системные типы данных SQL Сервера используются при определении типа данных столбца таблицы в операторах create table (создать таблицу) и alter table (изменить таблицу), типа переменной в операторе declare (объявить), или типа параметра в операторе create procedure (создать процедуру). Эти операторы будут описаны в следующих главах этого руководства. Пользователь также может создавать свои типы данных и использовать их в этих операторах.
В этой главе рассматриваются следующие темы:·Общий обзор типов данных;·Системные типы данных, предоставляемые SQL Сервером;·Как преобразовывать типы данных между собой;·Как смешанная арифметика выполняется в иерархии типов данных;·Как пользователь может определить свой тип данных;·Как получить информацию о типах данных.
Что такое типы данных в Transact-SQL ?
В языке Transact-SQL типы данных характеризуют тип информации, которая располагается в столбцах таблицы, передается как параметр сохраненной процедуре или хранится в локальной переменной. Например, тип данных int (целый) характеризует множество целых чисел в интервале от -231 до 231 , а тип данных tinyint (маленькое целое) указывает на числовой интервал от 0 до 255.
SQL Сервер предоставляет несколько системных типов данных и два типа данных, которые может определить пользователь timestamp и sysname. Пользователь может использовать системную процедуру sp_addtype для создания своего типов данных, который базируется на имеющемся системном типе данных. (Типы данных, определенные пользователем, будут рассматриваться в разделе “Создание типов данных, определенных пользователем”.)
Системные или заказные (пользовательские) типы данных должны быть уже определены, когда они используются для создания столбцов таблицы, локальных переменных или параметров. В следующем примере используются системные типы данных char (символьный), numeric (числовой) и money (денежный) для определения типов данных столбцов таблицы в операторе создания таблицы create table:
create table sales_daily
(stor_id char(4)
ord_num numeric(10,0)
ord_amt money)
В следующем примере используется системный тип данных bit (битовый) для определения локальной переменной в операторе declare:
declare @switch bit
В следующих разделах более детально описывается, как задавать типы данных в столбцах таблицы, локальных переменных и параметрах. С помощью системной процедуры sp_help можно узнать, какие типы данных используются в существующих столбцах таблиц базы данных.
Использование системных типов данных
В следующей таблице приведены системные типы данных, характеризующие различные виды информации, синонимы для названий этих типов, распознаваемые SQL Сервером, а также диапазон изменения данных и размеры памяти, используемой для хранения каждого типа данных. Системные типы данных указываются в этой таблице строчными буквами, хотя SQL Сервер позволяет указывать типы как строчными, так и прописными буквами. (Типы данных пользователя timestamp и sysname, как и все другие типы данных, определенные пользователем, являются чувствительными к регистру). Большинство названий системных типов данных SQL Сервера не являются зарезервированными словами, и их можно использовать для названия других объектов.
Таблица 6-1: Системные типы данных SQL Сервера
Категории
типов данных
Синонимы
Диапазон
Число байтов
Точные числовые: целые

tinyint
от 0 до 255
1
smallint
от 215-1 (32,767) до -215
(-32,768)
2
int
integer
от 231-1 (2,147,483,647) до
-231 (-2,147,483,648)
4
Точные числовые: десятичные

numeric (p,s)
от 1038-1 до -1038
от 2 до 17
decimal (p,s)
dec
от 1038-1 до -1038
от 2 до 17
Приближенные числовые

float precision
зависит от компьютера
4 или 8
double precision
зависит от компьютера
8
real
зависит от компьютера
4
Денежные

smallmoney
от 214,748.3647 до-214,748.3648
4
money
от 922,337,203,685,477.5807 до
- 922,337,203,685,477.5808
8
Дата\время

smalldatetime
с 1 Января 1990 по 6 Июня 2079
4
datetime
с 1 Января 1753 по 31 Декабря 9999
8
Символьные
char(n)
character
255 символов или меньше
n
varchar(n)
character varying ,
char varying
255 символов или меньше
длина введенной цепочки
nchar(n)
national char,
national character
255 символов или меньше
n*@@ncharsize
nvarchar(n)
nchar varying,
national char varying,
national character
varying
255 символов или меньше
@@ncharsize*
число символов
text
231-1 (2,147,483,647) байтов или меньше
0 или кратно 2K
Бинарные

binary
255 байтов или меньше
n
varbinary
255 байтов или меньше
входная длина
image
231-1(2,147,483,647) байтов или меньше
0 или кратно 2K
Битовые

bit
0 или 1
1 (один байт содержит до 8 битов)
Описание каждого типа данных приводится далее.
Точные числовые типы: Целые
SQL Сервер предоставляет три типа данных для задания целых чисел: tinyint (маленькое целое), smallint (малое целое) и int (целое). Эти типы данных являются точными числовыми, поскольку при выполнении арифметических операций с числами этих типов сохраняются все значящие цифры.
Выбор нужного типа для хранения целых чисел основывается на ожидаемой величине этих чисел. Число байтов памяти, выделяемой для хранения чисел, зависит от типа данных.
Таблица 6-2: Целые типы данных
Тип данных
Диапазон сохраняемых чисел
Число байтов памяти
tinyint
Все числа от 0 до 255, включительно. (Отрицательные значения не допускаются.)
1
smallint
Все числа от 215-1 до -215 (32,767 до -32,768), включительно.
2
int
Все числа от 231-1 до -231 (2,147,483,647 до
-2,147,483,648), включительно
4
Точные числовые типы: Десятичные числа
Для хранения дробных чисел SQL Сервер предоставляет два других числовых типа данных numeric (числовой) и decimal (десятичный). Данные, которые хранятся в табличных столбцах типа numeric и decimal упаковываются для экономии дисковой памяти и сохраняются после арифметических операций с точностью до наименьшего значимого разряда. Типы данных numeric и decimal (десятичный) одинаковы во всех отношениях за исключением того, что только тип numeric с нулевой шкалой может быть использован для столбца IDENTITY (счетчиковый).
В определении точных числовых типов можно использовать два необязательных параметра, precision (точность) и scale (шкала), которые заключаются в скобки и разделяются запятой:
datatype [(точность [, шкала])]
SQL Сервер воспринимает каждую комбинацию этих параметров как отдельный тип данных. Например, numeric(10,0) и numeric(5,0) являются двумя отдельными типами данных. Точность и шкала определяют диапазон значений, которые можно хранить в табличных столбцах типа decimal или numeric:
·Точность определяет максимальное число десятичных разрядов, которые могут храниться в столбце этого типа. Сюда включаются все разряды, расположенные как слева, так и справа от десятичной точки. Точность представления чисел можно выбрать из диапазона от 1 до 38 разрядов , или использовать точность в 18 разрядов, которая устанавливается по умолчанию;·Шкала определяет максимальное число разрядов, которые могут располагаться справа от десятичной точки. Заметим, что шкала должна быть меньше или равна точности. Шкалу можно задавать от 0 до 38 разрядов, или использовать по умолчанию нулевую шкалу.
Точные числовые значения со шкалой 0 выводятся без десятичной точки. Если в столбец таблицы вводится значение, которое превосходит либо точность, либо шкалу, установленную для этого столбца, то SQL Сервер укажет на ошибочное значение.
Объем памяти, выделяемый для хранения в столбце значений типа numeric и decimal, зависит от точности, указанной для этого типа. Минимальный объем памяти для одно- или двухразрядных типов составляет 2 байта. Объем требуемой памяти возрастает на 1 байт на каждые два дополнительных разряда вплоть до максимальной величины, составляющей 17 байтов.
Приближенные числовые типы данных
Для хранения числовых данных, которые округляются при выполнении арифметических операций, SQL Сервер предоставляет три приближенных числовых типа данных float (плавающий), double precision (двойная точность) и real (действительный). Приближенные типы следует применять для данных, которые могут изменяться в широком диапазоне значений. К этим типам данных могут применяться все аггрегирующие функции и арифметические операции, за исключением операции вычисления по модулю ( %).
Типы данных float и double precision построены на основе типов, содержащихся в операционной системе. Тип данных float допускает указание точности, которую нужно заключить в кавычки. Данные типа float с точностью от 1 до 15 разрядов храняться также как данные типа real, в то время как данные с более высокой точностью храняться также как данные типа double precision. Диапазон и точность представления данных этих трех типов зависят от используемого компьютера.
В следующей таблице показаны диапазон, точность представления и объем памяти, предусмотренные для каждого приближенного числового типа. Заметим, что процедура isql выводит только 6 значащих разрядов после десятичной точки, а остаток округляет.
Таблица 6-3: Приближенные числовые типы данных
Тип данных
Число байтов
float[(точность_по_умолчанию)]
4, если точность < 16,
8, если точность >= 16
double precision
8
real
4
Символьные типы данных
Символьные типы данных используются для хранения символьных строк, состоящих из букв, цифр, символов, и заключенных в простые или двойные кавычки. Для работы с данными этого типа можно использовать ключевое слово like для поиска строк, содержащих указанную комбинацию символов, а также встроенные строковые функции. Строки, состоящие из цифр, можно преобразовать в данные точного или приближенного числового типа с помощью функции convert (преобразовать), а затем выполнять арифметические операции.
Данные типа char(n) хранятся в массиве байтов фиксированной длины, а данные типа varchar(n) в массиве переменной длины, когда число символов алфавита не превосходит емкости одного байта, как например для строки на английском языке. “Национальные” двойники для данных этих типов, т.е. данные типа nchar(n) и nvarchar(n) хранятся соответственно в массивах фиксированной и переменной длины, но на хранения одного символа может выделяться несколько байтов, как например для строки на японском языке. Максимальное число символов в строке можно задать параметром n или по умолчанию ограничиться строкой в один символ. Для строк, длина которых превосходит емкость одного байта (т.е. 255), следует использовать тип данных text (текст).
Таблица 6-4: Символьные типы данных
Тип данных
Метод хранения
Число Байтов
char(n)
Массив байтов фиксированной длины, например, для телефонных номеров или почтовых кодов
n
varchar(n)
Массив байтов переменной длины, например, для имен и фамилий, изменяющихся в широких пределах
Текущая длина строки
nchar(n)
Массив фиксированной длины для строк в мультибайтовом алфавите
n*@@ncharsize
nvarchar(n)
Массив переменной длины для строк в мультибайтовом алфавите
Текущая длина строки*@@ncharsize
text
Связанный список страниц данных, достигающий 2,147,483,647 байтов, для хранения печатных символов
0, пока текст не инициализирован; по 2K байтов на страницу после инициализации
SQL Сервер укорачивает вводимые в столбец таблицы строки до ширины этого столбца, без специальных предупреждений и сообщений об ошибке, если не установлена опция string_truncation on. О команде установки опций set можно прочесть в Справочном руководстве SQL Сервера. Пустая строка (“” или “) хранится в виде одного пробела, а не как NULL (неопределенное значение). Следовательно, значением выражения “abc”+“”+“def” является строка “abc def “ , а не “abcdef”.
Хранение строк в столбцах с фиксированной и переменной длиной отличается друг от друга в следующих отношениях:
·Строки в столбцах фиксированной длины с конца дополняются пробелями до длины этого столбца. Для данных типа char отводится n байтов памяти; а для данных типа nchar число n умножается на значение переменной @@ncharsize, которое равно средней длине (в байтах) символа национального алфавита. Когда создается столбец типа char и nchar, в котором допускаются неопределенные значения, то SQL Сервер автоматически преобразует их в типы varchar и nvarchar соответственно и сохраняет значения в соответствии с этими типами данных. (Этого не происходит для переменных типа char и nchar, и параметров).·В строках, расположенных в столбцах переменной длиной, наоборот концевые пробелы удаляются, поэтому память затрачивается только на хранение значащих символов. Для столбцов типа varchar число байтов, использующихся для хранения данных, равно длине строки; а для столбцов nvarchar это число равно длине строки, умноженной на среднюю длину символов национального алфавита. Данные переменной длины могут требовать меньший объем памяти для хранения по сравнению с данными фиксированной длины, но доступ к ним происходит медленнее.
Данные типа text могут достигать длины до 2,147,483,647 печатных символов (байтов) и память для них выделяется отдельными страницами, которые храняться в виде связанного списка. Для экономии памяти, столбцы типа text следует определять с опцией NULL. При первой записи непустого текста в такой столбец операторами insert (вставить) или update (обновить), SQL Сервер заводит текстовый указатель и выделяет полную страницу памяти размером 2K байтов для хранения этого текста. Каждая страница может хранить до 1800 байтов текста. Для добавления данных без сохранения больших блоков текста в журнале транзакций, следует использовать процедуру writetext (запись текста). Об этом можно получить информацию в Справочном Руководство SQL Сервера.
Двоичные типы данных
Двоичные (бинарные) типы данных предназначены для хранения неформатированной двоичной информации, например, графических изображений в 16-ричном коде. Двоичные данные начинаются с символов вида ”0x” и могут содержать любую комбинацию цифр, а также строчные или прописные буквы от A до F.
Замечание: Обработка SQL Сервером двоичных данных зависит от реализации. Для данных в настоящем шестнадцатиричном коде следует использовать функции hextoint и inttohex. См. по этому поводу главу 10 “Использование встроенных функций в запросах”.
Типы binary(n) и varbinary(n) используются для хранения данных размером до 255 байтов. Каждый байт памяти содержит 2 шестнадцатиричные цифры. Длину данных, вносимых в столбец, можно определить числом n или использовать по умолчанию длину в один байт. Если в столбец таблицы ввести данные, длина которых превышает число n, то SQL Сервер укорачивает введенные данные до указанной длины без предупреждений и сообщений об ошибке.
·Тип данных с фиксированной длиной binary(n) следует использовать для хранения наборов данных, в которых все члены набора имеют примерно одинаковую длину. Поскольку данные в столбцах с этим типом дополняются нулями до длины столбца, то они могут потребовать для своего хранения несколько большего объема памяти , по сравнению с данными типа varbinary, но доступ к ним происходит немного быстрее;·Тип данных с переменной длиной varbinary(n) следует использовать для хранения наборов данных, в которых размеры отдельных членов набора могут сильно изменяться. Объем памяти, необходимый для хранения таких данных, определяется действительной длиной этих данных, а не длиной столбца. При этом нули на концах двоичных строк отбрасываются.
При создании столбца типа binary, в котором допускаются неопределенные значения, SQL Сервер автоматически преобразует его в тип varbinary и хранит данные в этом формате.
Можно проводить поиск в двоичных строках с помощью ключевого слова like, а также применять к ним встроенные функции. Поскольку точный вид внутреннего представления двоичных данных зависит от аппаратного обеспечения, то вычисления, включающие двоичные данные, могут выдавать различные результаты на разных платформах.
Двоичный тип данных image (изображение) следует использовать для хранения больших блоков двоичных данных на внешних страницах данных. В столбце типа image можно записать до 2,147,483,647 байтов информации, которая хранится в виде связанного списка страниц данных, отдельно от других табличных данных. При первой записи определенного значения в столбец типа image операторами insert или update, SQL Сервер заводит текстовый указатель и выделяет полную страницу данных размером 2К для хранения этого значения. Каждая страница может содержать до 1800 байтов информации.
Для экономии памяти, в столбцах типа image следует допускать неопределенное значение NULL. Следует также использовать процедуру writetext, чтобы при добавлении данных в столбец типа image в журнал транзакций не записывались большие блоки текста. Более детальную информацию по этому поводу можно посмотреть в Справочном руководство SQL сервера.
Таблица 6-5: Бинарные типы данных
Тип данных
Число байтов памяти
binary(n)
n
varbinary(n)
Действительная длина данных
image
0 , когда не инициализирован, кратно 2К после инициализации
Денежные типы данных
Денежные типы данных money (денежный формат) и smallmoney (малый денежный формат), предназначены для хранения денежных величин. Их можно использовать для хранения сумм в долларах США или в других валютах, хотя SQL Сервер не обеспечивает преобразования одного вида валют в другой. Над денежными величинами можно выполнять любые арифметические операции, кроме операции взятия модуля, и все аггрегирующие функции.
Данные типа money и smallmoney имеют точность до 1/10000 денежной единицы и округляются до двух знаков после десятичной точки при выводе. По умолчанию при печати после каждых трех цифр вставляется запятая.
В следующей таблице приведены диапазон сохраняемых значений и объем памяти, необходимый для хранения денежных типов данных:
Таблица 6-6: Денежные типы данных
Тип Данных
Диапазон
Число байтов
money
Денежные величины между +922,337,203,685,477.5807 и
-922,337,203,685,477.5808
8
smallmoney
Денежные величины между +214,748.3647 и
-214,748.3648
4
Типы данных для даты и времени
Данные типа datetime (дата, время) и smalldatetime (укороченная дата и время) используются для хранения информации о времени и датах в пределах от 1 Января 1753 года до 31 Декабря 9999 года. Даты, не попадающие в эти пределы, должны храниться как данные типа char и varchar.
·В столбцах типа datetime хранятся даты в пределах между 1 Января 1753 года и 31 Декабря 9999 года. Значения типа datetime имеют точность до 1/300 секунды на тех платформах, которые поддерживают этот уровень точности. Объем памяти для хранения равен 8 байтам: 4 байта для числа дней по отношению к базовой дате 1 Января 1900 года и 4 байта для времени дня.
·В столбцах типа smalldatetime хранятся даты от 1 Января 1900 года до 6 Июня 2079 года с точностью до минуты. Объем памяти, необходимый для их хранения, равен 4 байтам: 2 байта для числа дней после 1 Января 1900 года и 2 байта для числа минут после полуночи.
Значения даты и времени должна быть заключены в простые или двойные кавычки. Они могут вводиться как строчными, так и заглавными буквами и могут содержать пробелы между различными частями даты. SQL Сервер распознает много форматов дат, которые будут описаны в Главе 8.
Нулевые значения или значения вида 00/00/00 не являются датами и поэтому не воспринимаются при вводе.
По умолчанию даты и время выводятся в следующем формате: “Apr 15 1987 10:23PM”. Можно использовать функцию convert (преобразование) для изменения формата вывода для даты и времени. Над значениями типа datetime можно выполнять некоторые арифметические операции с помощью встроенных функций.
В следующей таблице показаны диапазоны сохраняемых значений и объемы памяти, необходимые для хранения данных этого типа:
Таблица 6-7: Данные типа даты и времени
Тип данных
Диапазон
Число байтов
datetime
от 1 Января 1753 года до 31 Декабря 9999
8
smalldatetime
от 1 Января 1900 года до 6 Июня 2079
4
Битовые типы данных (bit)
Битовый тип используется для хранения двузначных данных типа истина и ложь, или да и нет. Битовые данные могут принимать только два значения: 0 или 1. Здесь допустимы также целые значения, но все числа, отличные от 0 или 1, интерпретируются как 1. На хранения данных этого типа выделяется один байт. Массивы битов собираются в байты. Например, на хранение массива из 7 битов в столбце таблицы будет выделен 1 байт, а на хранение 9 битов - 2 байта.
Столбцы этого типа не могут содержать неопределенное значение NULL и не могут индексироваться. Столбец status в системной таблице syscolumns содержит уникальное смещение для столбцов этого типа.
Тип данных timestamp
SQL Сервер также предоставляет пользовательский тип данных timestamp (текущее время). Данные этого типа необходимы в таблицах, которые просматриваются в приложениях Open Client™ DB-Library™.
Каждый раз, когда в строку, содержащую поле timestamp, вставляются или обновляется данные, содержимое этого поля автоматически обновляется. Таблица может иметь только один столбец этого типа. Столбцу с названием timestamp автоматически присваивается системный тип данных timestamp, который определяется как varbinary(8) NULL.
Поскольку тип timestamp может определяться пользователем, его нельзя использовать для создания других типов данных. Этот тип данных следует указывать как “timestamp” и записывать строчными буквами.
Тип данных sysname
Тип данных sysname предназначается конечным пользователям и поставляется вместе с инсталяционной лентой SQL Сервера для использования в системных таблицах. Он определяется следующим образом:
varchar(30) “not null”
Этот тип данных нельзя использовать при создании таблиц. Он является базовым для создания новых типов данных пользователя. В свою очередь, созданный на его базе тип данных, уже можно использовать в таблицах как user-defined datatype (тип данных, определенный пользователем). Более подробная информация о типах данных пользователей будет приведена в разделе “Создание типов данных пользователя”.
Преобразования типов данных
Многие типы данных SQL Сервер автоматически преобразует друг в друга. Это называется неявным преобразованием. Пользователь может также явно выполнить преобразование типов с помощью функций convert (преобразовать), inttohex (целое в 16-ричное), hextoint (16-ричное в целое). Тем не менее некоторые типы данных нельзя ни явно, ни неявно преобразовать один к другому, из-за несовместимости этих типов.
Например, в то время как SQL Сервер автоматически преобразует данные типа char в данные типа datetime, чтобы они интерпретировались как даты, тем не менее пользователь должен использовать функцию convert для явного преобразования данных типа char в данные типа int. Точно также с помощью функции convert нужно явно выполнять и обратное преобразование целых чисел в символьные строки, чтобы, например, можно было применить к ним функцию like.
Функция convert вызывается следующим образом:
convert (тип_данных, выражение, [стиль])
Например, рассмотрим следующий оператор:
select title, total_sales
from titles
where convert (char(20), total_sales) like "2%"
Необязательный параметр стиль (style) используется для преобразования значения типа datetime в значения типа char или varchar для варьирования форматов представления дат.
В главе 10 дается подробная информация о функциях convert, inttohex, hextoint.
Смешанная арифметика и иерархия типов данных
При выполнении арифметических операций над данными различных типов, SQL Сервер должен определить тип результата, а также его точность и длину.
Каждый системный тип данных занимает определенное место в иерархии типов данных, которая хранится в системной таблице systypes. Типы данных, определенные пользователем, наследуют иерархию системных типов, на которых они основаны.
Следующий запрос отражает место (ранг), занимаемое каждым типом данных в этой иерархии. В дополнение к системной информации в результат этого запроса будет включаться информация о всех типах данных, определенных пользователями базы данных.
select name,hierarchy
from systypes
order by hierarchy
name hierarchy
---------- -----------
floatn 1
float 2
datetimn 3
datetime 4
real 5
numericn 6
numeric 7
decimaln 8
decimal 9
moneyn 10
money 11
smallmoney 12
smalldatetime 13
intn 14
int 15
smallint 16
tinyint 17
bit 18
varchar 19
sysname 19
nvarchar 19
char 20
nchar 20
varbinary 21
timestamp 21
binary 22
text 23
image 24
(Выбрано 28 строк)
Иерархия типов данных определяет тип результата при вычислениях с аргументами разного типа. Результату присваивается наивысший по рангу тип аргумента, т.е. имеющий меньший номер в иерархии.
В следующем примере данные из столбца qty таблицы sales умножаются на содержимое столбца royalty таблицы roysched. Столбец qty имеет тип данных smallint, который занимает в иерархии 16 ранг, а столбец royalty имеет тип int, который занимает 15 ранг. Следовательно, результат будет иметь тип int.
smallint(qty) * int(royalty) = int
Работа с денежными типами данных
Если данные денежного типа комбинируютя с константами (литералами) и переменными для получения результата, который также должен иметь денежный тип, то следует использовать константы и переменные денежного типа, как это показано в следующем примере:
select moneycol * $2.5 from mytable
При комбинировании данных денежного типа с данными типами float или numeric, следует использовать функцию convert:
select convert (money, moneycol * percentcol)
from debts, interest
Указание точности и шкалы значений
Для типов данных numeric и decimal любая комбинация значений точности и шкалы интерпретируется SQL Сервером как отдельный тип данных. При выполнении арифметических операций с двумя значениями типа numeric или decimal, одно из которых n1 имееет точность p1 и шкалу s1, а второе n2 - точностью p2 и шкалой s2, то SQL Сервер определяет точность и шкалу результата в соответствии со следующей таблицей.
Таблица 6-8: Точность и шкала результата арифметических операций
Операция
Точность
Шкала
n1+n2
max(s1,s2)+max(p1-s1,p2-s2)+1
max(s1,s2)
n1-n2
max(s1,s2)+max(p1-s1,p2-s2)+1
max(s1,s2)
n1*n2
s1+s2+(p1-s1)+(p2-s2)+1
s1+s2
n1/n2
max(s1+p2+1,6)+p1-s1+p2
max(s1+p2-s2+1,6)
Создание типов данных пользователя
Одним из расширений языка Transact-SQL по отношению к SQL является возможность определения пользователем своих типов данных (нестандартных типов). Типы данных пользователя определяются через системные типы. Пользователь может присвоить название часто встречающемуся типу данных, чтобы упростить процесс определения столбцов с нестандартными (несистемными) типами данных.
Замечание: Для использования нестандартного типа данных более чем в одной базе данных, необходимо создать его в модельной (model) базе данных. Такой тип данных становится доступным во всех вновь созданных базах данных.
Однажды определенный тип данных можно использовать в любом столбце таблицы базы данных. Например, тип данных tid используется в столбцах таблиц titles.title_id, titleauthor.title_id, roysched.title_id базы данных pub2.
Преимущество нестандартных типов данных состоит в том, что с ними можно связывать правила и значения по умолчанию, чтобы использовать их в нескольких таблицах. Более полная информация об этом будет дана в главе 12.
Для создания нестандартных типов данных используется системная процедура sp_addtype (добавить тип). В качестве обязательных параметров этой процедуре передается название создаваемого типа данных и системный тип данных, на котором базируется новый тип, а в качестве необязательных параметров для нее можно указать спецификации NULL, NOTNULL или IDENTITY.
Нестандартный тип данных может базироваться на любом системном типе данных, за исключением типа timestamp. Нестандартные типы данных получают те же ранги в иерархии, что и системные типы, на которых они основаны. В отличие от системных типов данных, в написании названий нестандартных типов различаются заглавные и строчные буквы.
Процедура sp_addtype вызывается следующим образом:
sp_addtype название_типа_данных,
ситемный_тип_данных [(длина) | (точность [, шкала])]
[, "identity" | вид_неопределенности]
Далее показано, как определяется тип данных tid:
sp_addtype tid, "char(6)", "not null"
Параметры процедуры должны заключаться в простые или двойные кавычки в том случае, если они содержат пробелы или другие знаки пунктуации, или если ключевым словом является слово, отличное от null (например, identity или sp_helpgroup). В этом примере, из-за наличия скобок, в кавычки заключен параметр char(6), а, из-за пробела, параметр NOT NULL. Для параметра tid кавычки не требуются.
Указание длины, точности и шкалы
При определении нестандартных типов данных необходимо указывать дополнительные параметры для следующих системных типов данных, на которых базируется новый тип:
·В типах данных char, nchar, varchar, nvarchar, binary и varbinary следует в скобках указать длину. Если этого не сделать, то SQL Сервер по умолчанию установит длину, равную одному символу;·Для типа данных float следует в скобках указать точность. В противном случае SQL Сервер по умолчанию установит точность, которая зависит от реализации;·Для типов данных numeric и decimal следует указать в скобках через запятую точность и шкалу. В противном случае SQL Сервер по умолчанию установит точность, равную 18, и шкалу, равную 0.
Нельзя изменять длину, точность и шкалу нестандартного типа данных при его использовании в операторе создания таблицы create table.
Указание параметра неопределенности (Null)
Параметр неопределенности указывает на возможность использования неопределенного значения в данных определяемого типа. В качестве этого параметра можно указать: “null”, “NULL”, “nonull”, “NONULL”, “not null” или “NOT NULL”. По определению, в данных типах bit и IDENTITY не допускаются неопределенные значения.
Если опустить этот параметр, то SQL Сервер установит текущий для данной базы данных вид неопределенности (по умолчанию NOT NULL). Для совместимости со стандартами SQL необходимо использовать системную процедуру sp_dboption для установки опции allow nulls by default (разрешить неопределенность по умолчанию) в состояние истина (true).
Возможность присутствия неопределенности в данных нестандартного типа можно переустановить, когда этот тип данных используется в операторе create table.
Связывание правил и умолчаний с нестандартными типами данных
После того как создан новый тип данных, можно использовать системные процедуры sp_bindrule и sp_bindefault для связывания с ним правил и значений по умолчанию. С помощью системной процедуры sp_help можно увидеть список правил, умолчаний и другую информацию, связанную с типом данных.
Создание правил и значений по умолчанию будет рассмотрено в главе 12. Более подробная информация о системных процедурах дается в Справочном Руководстве SQL Сервера.
Удаление нестандартных типов данных
Для удаления нестандартных типов данных следует выполнить процедуру sp_droptype:
sp_droptype название_типа_данных
Замечание: Нельзя удалять тип данных, который используется в какой-нибудь таблице.
Получение информации о типах данных
Для получения информации о системных и нестандартных типах данных следует выполнить системную процедуру sp_help. В результирующем отчете эта процедура указывает базовый тип данных, на основе которого был создан новый тип, допускаются ли для этого типа данных неопределенные значения, приводятся названия правил и умолчаний, связанных с новым типом данных и может ли этот тип данных использоваться для создания счетчиков (IDENTITY).
В следующем примере приводится информация о системном типе данных money и нестандартном типе данных tid:
sp_help money
Type_name Storage_type Length Prec Scale
--------------- ------------ ------ ------ -----
money money 8 NULL NULL
Nulls Default_name Rule_name Identity
----- ------------ --------- --------
1 NULL NULL 0

sp_help tid
Type_name Storage_type Length Prec Scale
---------- ------------ ------ ----- -----
tid varchar 6 NULL NULL
Nulls Default_name Rule_name Identity
----- ------------ --------- --------
0 NULL NULL 0