Типы данных в MS SQL. Transact-SQL

Согласно процедурному расширению языка SQL (Transact-SQL), для работы с MS SQL Server предусмотрены различные типы данных. Конкретный тип данных указывается для любого столбца во время создания таблицы и определяет, какие именно значения можно будет хранить, а также сколько места в памяти компьютера они будут занимать. Эта статья представляет собой обзор существующих типов данных для MS SQL Server, которые, в зависимости от поддерживаемых значений, делятся на группы.

Несколько слов о T-SQL

Transact-SQL является процедурным расширением SQL (structured query language), которое создано корпорацией Microsoft специально для баз данных Microsoft SQL Server и Sybase ASE. Известный язык программирования SQL был расширен рядом дополнительных возможностей:

• управляющими операторами,
• локальными и глобальными переменными,
• вспомогательными функциями для обработки строк, дат и пр.,
• поддержкой аутентификации Microsoft Windows.

Сегодня язык Transact-SQL — это, по сути, ключ к применению MS SQL Server. Таким образом, все программные приложения, взаимодействующие с экземпляром MS SQL Server, вне зависимости от реализации и UI, выполняют отправку серверу инструкций Transact-SQL.

Директивами сценария являются специфические команды, используемые лишь в MS SQL. Такие команды позволяют серверу определять правила работы с транзакциями и скриптом.

Типы данных (data types)

Как это принято и в других языках программирования, язык T-SQL поддерживает множество разных типов данных, используемых для хранения переменных в MS SQL. Эти типы данных можно разделить на группы:

• числовые. Служат для хранения числовых переменных;
• типы данных для дат. Обеспечивают хранение значений времени и даты;
• символьный. Нужен для символьных типов данных;
• двоичные. Обеспечивают хранение бинарных типов данных;
• большого объема. Этот тип данных (data type) нужен для хранения больших бинарных значений;
• специальные. Сюда входит широкий спектр типов данных: указатели, таблицы и пр.

Давайте рассмотрим некоторые из вышеописанных типов данных подробнее.

Числовые типы данных

К числовым типам данных относят:

• BIT: может храниться 0 либо 1. По сути, это аналог булевого типа в программировании. Занимает всего один байт;
• TINYINT: хранит целые числа в пределах 0-255. Тоже занимает 1 байт и часто используется в целях хранения небольших чисел;
• SMALLINT: сохраняет числа в пределах от –32 768 до 32 767. Занимает уже 2 байта;
• INT: диапазон хранения — от –2 147 483 648 до 2 147 483 647. 4 байта. Тип данных int чаще всего применяется для сохранения числовых значений;
• BIGINT: предназначен для очень больших значений (от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807) и занимает в памяти 8 байт;
• DECIMAL: для хранения переменных c фиксированной точностью. В зависимости от количества чисел после запятой переменная типа Decimal может занимать от 5 до 17 байт.

Также этот тип данных способен принимать 2 параметра — scale и precision: DECIMAL(precision, scale).

Параметр precision служит для представления наибольшего количества цифр, способных хранить числовое значение. По дефолту оно равняется 18-ти, а вообще должно располагаться в диапазоне 1-38.

Параметр scale служит для представления максимального количества цифр, которые способно содержать число после запятой. Такое значение должно быть в диапазоне от 0 до значения, которое указывается параметром precision. По умолчанию равно нулю;

• NUMERIC: этот тип данных аналогичен предыдущему;
• SMALLMONEY: сохраняет дробные значения в пределах от -214 748.3648 до 214 748.3647. Служит для хранения денежных величин. Эквивалентен DECIMAL(10,4), занимает 4 байта;
• MONEY: сохраняет дробные значения в диапазоне от -922 337 203 685 477.5808 до 922 337 203 685 477.5807. Служит для представления денежных величин, занимает 8 байт. Соответствует DECIMAL(19,4).
• FLOAT: для хранения значений от –1.79E+308 до 1.79E+308. В зависимости от дробной части может занимать от 4 до 8 байт.

Этот тип данных способен иметь форму определения в формате FLOAT(n), где n — это количество бит, используемых в целях хранения десятичной части числа (речь идет о мантиссе). По дефолту n равняется 53;

• REAL: сохраняет числа в диапазоне от –340E+38 to 3.40E+38. Соответствует FLOAT(24), занимает 4 байта.

Рассмотрим пример числового столбца с таким типом данных:

Тип данных для времени

Существуют типы данных, предназначенных для отображения даты и времени:

• DATE: сохраняет даты, начиная с 1 января 0001 года (0001-01-01), заканчивая 31 декабрём 9999 г. (9999-12-31). Занимает 3 байта;
• TIME: сохраняет время в диапазоне 00:00:00.0000000 — 23:59:59.9999999. Способен иметь форму TIME(n), причем n здесь представляет число цифр от 0 до 7 в дробной части секунд. Занимает 3-5 байт;

• DATETIME: обеспечивает сохранение объектов даты и времени в диапазоне 01/01/1753 — 31/12/9999. 8 байт;
• DATETIME2: сохраняет дату и время в диапазоне 01/01/0001 00:00:00.0000000 — 31/12/9999 23:59:59.9999999. Может занимать 6-8 байт, что зависит от временной точности. Способен иметь форму DATETIME2(n), причем n здесь — это число цифр в пределах 0-7 в дробной части секунд;

• SMALLDATETIME: сохраняет даты и время в пределах 01/01/1900-06/06/2079, то есть речь идет о ближайших датах. 4 байта;
• DATETIMEOFFSET: для дат и времени в диапазоне от 0001-01-01 до 9999-12-31. Обеспечивается сохранение детальной информации о времени, точность составляет 100 наносекунд. 10 байт;

Наиболее распространены следующие форматы дат:

В этом формате 2-значные числа в пределах 00-49 воспринимаются в качестве дат диапазона 2000-2049. А числовые значения 50-99 воспринимаются в качестве диапазона 1950-1999.

Распространены следующие временные форматы:

Строковый тип данных

Какие строковые типы данных бывают:

• CHAR: для строк длиной 1-8000 символов. На каждый символ выделяется 1 байт. Этот тип данных не подходит для многих языков, а все потому, что он сохраняет символы не в кодировке Unicode.

Число символов, которое способен хранить столбец, передается с помощью скобок. К примеру, для столбца CHAR(10) выделится 10 байт. Когда мы сохраняем в столбце строку размером меньше десяти символов, она дополнится пробелами;

• VARCHAR: может хранить строки, причем здесь тоже на каждый символ происходит выделение 1 байта. Для столбца можно указать определенную длину в диапазоне 1-8 000 символов, к примеру, VARCHAR(10). Когда строка должна иметь более 8000 символов, задается размер MAX, причем на хранение строки может выделять до 2 Гб: VARCHAR(MAX).

Этот тип данных также не подходит для многих языков и все по той же причине: хранит символы не в Unicode;

• NCHAR: для строки с кодировкой Unicode и длиной 1-4 000 символов. На каждый символ — 2 байта;
• NVARCHAR: сохраняет строку в Unicode. На каждый символ — 2 байта. Есть возможность задать конкретный размер в пределах 1-4 000 символов. Когда строка должна включать более 4000 символов, задается размер MAX, а на хранение строки выделяется до 2 Гб.

Также существуют такие типы данных, как TEXT и NTEXT. Они считаются устаревшими, поэтому их лучше не использовать. Вместо только что упомянутых типов данных применяют VARCHAR и NVARCHAR.

Рассмотрим примеры определения строковых столбцов:

О бинарных типах данных

Пришла очередь и двоичным данным:

• BINARY: для сохранения бинарных типов данных в качестве последовательности в пределах 1-8 000 байт;
• VARBINARY: для хранения бинарных типов данных в качестве последовательности в диапазоне 1-8 000 байт, либо до 2^31–1 байт в случае применения значения MAX (VARBINARY(MAX)).

Такой бинарный тип, как IMAGE, тоже устарел, поэтому вместо него желательно использовать тип данных VARBINARY.

Специальные типы данных

В этой группе перечислим оставшиеся типы данных:

• UNIQUEIDENTIFIER: уникальный идентификатор GUID (строка с уникальным значением), занимающий 16 байт;
• TIMESTAMP: некоторое число, хранящее номер версии строки в таблице. Может занимать 8 байт;
• CURSOR: для набора строк;
• HIERARCHYID: для представления позиции в иерархии;
• SQL_VARIANT: для хранения значений практически любого другого типа данных T-SQL;
• XML: для хранения документов XML либо фрагментов XML-документов. Занимает до двух гигабайт;
• TABLE: представляет определение таблицы;
• GEOGRAPHY: обеспечивает хранение географических данных (широты, долготы);
• GEOMETRY: для координат местонахождения на плоскости.

Примечание: для работы с русскими символами тип данных применяют, используя приставку «n» (nchar, nvarchar, ntext). Такие приставки кодируют символы 2-мя байтами. Если говорить иными словами, то для работы с кодировкой Unicode используют тип данных с «n» (вспоминаем слово national). Так же записываются и строковые константы.

Когда речь идет о типе данных для значения переменной длины, задействуют тип данных, имеющий приставку «var» (character varying). Тип данных без приставки «var» характеризуется фиксированной длиной области памяти, причем неиспользованная часть заполняется пробелами либо нулями.

По материалам:

— https://metanit.com/sql/sqlserver/3.3.php;

— https://ru.wikipedia.org/wiki/Transact-SQL.