Типы данных в T-SQL (Microsoft SQL Server)

Материал сегодня будет посвящен рассмотрению типов данных языка T-SQL – это язык программирования, которой используется в СУБД Microsoft SQL Server. Мы поговорим о том, какие существуют типы данных, какие у них особенности, а также в каких случаях использовать тот или иной тип данных.

Если Вы только начинаете изучать T-SQL, то на нашем сайте Вы можете найти полезные материалы для новичков на данную тему, например статьи «Справочник Transact-SQL» и «Основы программирования на T-SQL», где мы рассматривали основные моменты данного языка.

В перечисленных выше материалах мы затрагивали типы данных, но подробно о них не разговаривали, поэтому сегодня мы рассмотрим данную тему более подробно, так как понимание типов данных поможет Вам правильно спроектировать БД или написать процедуру. Например, выбор оптимального (корректного) типа данных для столбца в таблице может в будущем отразиться на размере базы (если таблица будет интенсивно расти), а именно она будет значительно меньше, чем, если бы Вы выбрали какой-то некорректный тип данных, причем это разница может достигать несколько сотен мегабайт, а то и нескольких гигабайт!

Итак, давайте начинать.

Скриншот 1

Что такое тип данных в SQL Server?

Тип данных – это характеристика, определяющая, какого рода данные будут храниться в объекте. Например: целые числа, числовые данные с плавающей запятой, данные денежного типа, дата, время, текст, двоичные данные и так далее. У каждого столбца, выражения, переменной или параметра есть определенный тип данных. В Microsoft SQL Server существует набор системных типов данных, который и определяет все доступные по умолчанию типы данных для использования. У разработчиков также существует возможность создавать псевдонимы типов данных основанные на системных типах, а также собственные пользовательские типы данных, о том, как реализовать псевдоним типа данных, мы разговаривали в материале – «Создание псевдонима типа данных в Microsoft SQL Server на T-SQL».

Типы данных в MS SQL Server делятся на следующие категории:

  • Точные числа;
  • Приблизительные числа;
  • Символьные строки;
  • Символьные строки в Юникоде;
  • Дата и время;
  • Двоичные данные;
  • Прочие типы данных.

Описание типов данных в T-SQL

Сейчас давайте рассмотрим типы данных по категориям.

Точные числа

Наименование типа Хранилище Описание
bit Если в таблице до 8 bit-столбцов 1 байт, если от 9 до 16, то 2 байта и так далее. Может принимать значения 1, 0 или NULL. Часто используется как тип данных Boolean. Строковые значения TRUE и FALSE можно преобразовать в значения данного типа: TRUE преобразуется в 1, а FALSE в 0.
tinyint 1 байт Целые числа от 0 до 255
smallint 2 байта от -2^15 (-32 768) до 2^15-1 (32 767).
int 4 байта от –2^31 (–2 147 483 648) до 2^31-1 (2 147 483 647). Это основной целочисленный тип данных в Microsoft SQL Server.
bigint 8 байт от -2^63 (-9 223 372 036 854 775 808) до 2^63-1 (9 223 372 036 854 775 807).
numeric (p, s) и decimal (p, s) Точность: от 1 до 9 = 5 байт; от 10 до 19 = 9 байт; от 20 до 28 = 13 байт; от 29 до 38 = 17 байт. Тип числовых данных с фиксированной точностью и масштабом. numeric и decimal функционально эквивалентны. p (точность) — максимальное количество десятичных разрядов числа, которые будут храниться (как слева, так и справа от десятичной запятой). Точность может быть значением в диапазоне от 1 до 38, по умолчанию 18. s (масштаб) — максимальное количество десятичных разрядов числа справа от десятичной запятой. Максимальное число цифр слева от десятичной запятой определяется как p — s (точность — масштаб). Масштаб может быть значение от 0 до p, по умолчанию 0. Максимальный размер хранилища зависит от точности. Тип данных numeric и decimal может принимать значение от –10^38+1 до 10^38–1.
smallmoney 4 байта Тип данных для хранения денежных значений с точность до одной десятитысячной денежной единицы. Число от -214 748,3648 до 214 748,3647
money 8 байт Тип данных для хранения денежных значений с точность до одной десятитысячной денежной единицы. Число от -922 337 203 685 477,5808 до 922 337 203 685 477,5807

Приблизительные числа

Наименование типа Хранилище Описание
float (n) Зависит от значения n: От 1 до 24 (7 знаков) = 4 байта; От 25 до 53 (15 знаков) = 8 байт. Используется для числовых данных с плавающей запятой. n — это количество битов, используемых для хранения мантиссы числа в формате float при экспоненциальном представлении. n определяет точность данных и размер для хранения. Может принимать значение от 1 до 53, по умолчанию 53. Диапазон значений от –1,79E +308 до 1,79E+308.
real 4 байта Используется для числовых данных с плавающей запятой. real соответствует в ISO типу float(24). Диапазон значений от –3.40E+38 до 3.40E+38.

Не рекомендуется использовать столбцы с типами float и real в предложении WHERE, так как данные типы не хранят точных значений. Также не рекомендуется использовать float и real в финансовых приложениях, в операциях, связанных с округлением. Для этого лучше использовать decimal, money или smallmoney.

Символьные строки

Наименование типа Хранилище Описание
char (n) n байт Строка с фиксированной длиной не в Юникоде, где n длина строки (от 1 до 8000). По умолчанию n = 1, если значение n не указано при использовании функций CAST и CONVERT, длина по умолчанию равна 30.
varchar ( n | max ) Размер занимаемой памяти в байтах = количество введенных символов + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Строковые данные переменной длины не в Юникоде, где n длина строки (от 1 до 8000). По умолчанию n = 1, если значение n не указано при использовании функций CAST и CONVERT, длина по умолчанию равна 30.
text Размер занимаемой памяти в байтах = количество введенных символов. Максимальный размер 2^31-1 (2 147 483 647 байт, 2 ГБ). Строка переменной длины не в Юникоде. Является устаревшим типом данных, рекомендуется использовать varchar(max).

Символьные строки в Юникоде

Наименование типа Хранилище Описание
nchar (n) n * 2 байт Строка с фиксированной длиной в Юникоде, где n длина строки (от 1 до 4000). По умолчанию n = 1, если значение n не указано при использовании в функции CAST, длина по умолчанию равна 30.
nvarchar ( n | max ) Размер занимаемой памяти в байтах = количество введенных символов, умноженное на 2 + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Строка переменной длины в Юникоде, где n длина строки (от 1 до 4000). По умолчанию n = 1, если значение n не указано при использовании в функции CAST, длина по умолчанию равна 30.
ntext Размер занимаемой памяти в байтах = количество введенных символов, умноженное на 2. Максимальный размер 2^30 — 1 (1 073 741 823 байт, 1 ГБ). Строка переменной длины в Юникоде. Является устаревшим типом данных, рекомендуется использовать nvarchar(max).

Дата и время

Наименование типа Хранилище Диапазон Точность Описание
date 3 байта От 01.01.0001 до 31.12.9999 1 день Используется для хранения даты.
datetime 8 байт От 01.01.1753 00:00:00 до 31.12.9999 23:59:59,997 0,00333 секунды Используется для хранения даты, включая время с точностью до одной трехсотой секунды.
datetime2 От 6 до 8 байт (в зависимости от точности: менее 3 цифр = 6 байт, 3-4 цифры = 7 байт, более 4 цифр = 8 байт) От 01.01.0001 00:00:00.0000000 до 31.12.9999 23:59:59.9999999 100 наносекунд Расширенный вариант типа данных datetime, имеет более широкий диапазон дат и большую точность в долях секунды (до 7 цифр).
smalldatetime 4 байта От 01.01.1900 00:00:00 до 06.06.2079 23:59:00 1 минута Сокращенный вариант типа данных datetime, имеет меньший диапазон дат и не имеет долей секунд.
time [Точность] От 3 до 5 байт От 00:00:00.0000000 до 23:59:59.9999999 100 наносекунд Используется для хранения времени дня. Точность может быть целым числом от 0 до 7, по умолчанию 7 (100 наносекунд, 5 байт). Если указать 0, то точность будет до секунды (3 байта).
datetimeoffset [Точность] От 8 до 10 байт От 01.01.0001 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 100 наносекунд Используется для хранения даты и времени, включая смещение часовой зоны относительно универсального глобального времени. Точность определяет количество знаков в дробной части секунды, данное значение может быть от 0 до 7, по умолчанию 7 (100 наносекунд, 10 байт).

Двоичные данные

Наименование типа Хранилище Описание
binary (n) n байт Двоичные данные фиксированной длины. n — значение от 1 до 8000. Если не указывать n, то значение по умолчанию 1, если не указать в функции CAST, то 30. Данный тип лучше использовать в случаях, когда размер данных, которые будут храниться в столбце, можно заранее определить.
varbinary ( n | max ) Размер занимаемой памяти в байтах = фактический размер данных + 2 байта. Если указать MAX, то максимально возможный размер = 2^31-1 байт (2 ГБ). Двоичные данные с переменной длиной. n — значение от 1 до 8000. Если не указывать n, то значение по умолчанию 1, если не указать в функции CAST, то 30. Данным типом лучше пользоваться, если размер данных в столбце заранее определить трудно. Если размер данных превышает 8000 байт, необходимо использовать тип varbinary(max).
image Максимальный размер до 2^31-1 (2 147 483 647 байт, 2 ГБ). Двоичные данные с переменной длиной. Является устаревшим типом данных, рекомендуется использовать varbinary(max).

Прочие типы данных

Наименование типа Хранилище Описание
cursor Данный тип данных можно использовать в переменных или выходных параметрах хранимых процедур, которые содержат ссылку на курсор. Тип cursor не может быть использован в инструкции CREATE TABLE, т.е. для столбца в таблице. Может принимать значение NULL.
table Особый тип данных для переменных, который предназначен для хранения результирующего набора данных. Переменные с данным типом называют – табличные переменные. Подробней о переменных с типом table мы разговаривали в материале — «Табличные переменные в Microsoft SQL Server».
sql_variant Универсальный тип данных, который может хранить значения различных типов данных. Однако sql_variant может хранить значения не всех типов, которые есть в SQL сервере, например следующие типы нельзя сохранить при помощи типа данных sql_variant: varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, rowversion, hierarchyid, datetimeoffset, а также пространственные типы данных и определяемые пользователем типы. Тип sql_variant не может также иметь sql_variant в качестве базового типа.
rowversion (timestamp) 8 байт Тип данных rowversion представляет собой автоматически создаваемые уникальные двоичные числа. В таблице может быть определен только один столбец типа rowversion. После любого обновления строки или вставки новой строки в таблицу, которая содержит столбец типа rowversion, значение увеличенной rowversion вставляется в столбец с данным типом. Поэтому столбец с типом данных rowversion не рекомендуется использовать в ключе, особенно в первичном ключе. timestamp является синонимом типа данных rowversion, но данный синтаксис устарел и его использовать нежелательно.
xml Не более 2 ГБ. Используется для хранения XML-данных. Более детальную информацию Вы можете найти в статье «Transact-SQL — работа с XML».
uniqueidentifier 16 байт Глобальный уникальный идентификатор (GUID). Инициализировать столбец или переменную с типом uniqueidentifier можно с помощью функции NEWID или путем преобразования строки xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, где каждый x – это шестнадцатеричная цифра (0–9 или A–F).
hierarchyid Максимум 892 байта Тип данных используется для представления положения в древовидной иерархии.
Пространственные типы К пространственным типам относятся: geography – это географический пространственный тип данных, который используется для представления данных в системе координат круглой земли, geometry – это пространственный тип данных для представления данных в евклидовом пространстве (плоской системе координат).

Приоритеты типов данных в T-SQL

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

В MS SQL Server существует следующий приоритет типов данных:

  1. Определяемые пользователем типы данных (высший приоритет);
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (включая nvarchar(max));
  26. nchar
  27. varchar (включая varchar(max));
  28. char;
  29. varbinary (включая varbinary(max));
  30. binary (низший приоритет).

Синонимы типов данных в Microsoft SQL Server

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

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

Системный тип данных Синоним типа
varbinary Binary varying
varchar char varying
char character
char(1) character
char(n) character( n )
varchar(n) character varying( n )
decimal Dec
float Double precision
real float[(n)]; n = 1-7
float float[(n)]; n = 8-15
int Integer
nchar(n) national character( n )
nchar(n) national char( n )
nvarchar(n) national character varying( n )
nvarchar(n) national char varying( n )
ntext national text
rowversion timestamp

Распространенные ошибки при выборе типа данных в T-SQL

В начале статьи я говорил, что выбор неоптимального типа данных может сказаться на размере базы данных, так вот одной из самых распространенных ошибок при проектировании таблицы является выбор для столбца, который должен содержать тип данных Boolean (т.е. 0 или 1), тип SMALLINT или INT. Как Вы уже поняли, такого типа данных как Boolean в T-SQL нет, поэтому для этих целей разработчики используют похожие (подходящие) типы данных и в большинстве случаев их выбор неправильный. Если Вам нужно хранить только значения 0 или 1 (т.е. как Boolean), то в T-SQL существует специальный тип данных BIT, SQL сервер выделяет для хранения всего 1 байт, но в отличие от типа TINYINT, под который также отводится 1 байт, SQL сервер оптимизирует хранение бит столбцов. Если таблица содержит не больше 8 бит столбцов, столбцы хранятся как 1 байт, если таких столбцов от 9 до 16, то 2 байта и т.д.

Для сравнения давайте посмотрим на разницу.

Таблица 1

    
    --В строке 16 байт
    CREATE TABLE TestTable1 (
            Id INT NOT NULL, --4 байта
            IdProperty INT NOT NULL, --4 байта
            IsEnabled INT NOT NULL, --4 байта
            IsTest INT NOT NULL, --4 байта
    )

Таблица 2 (с использованием BIT столбцов)

    
    --В строке 9 байт
    CREATE TABLE TestTable2 (
            Id INT NOT NULL, --4 байта
            IdProperty INT NOT NULL, --4 байта
            IsEnabled BIT NOT NULL, --1 байта
            IsTest BIT NOT NULL, --0 байта
    )

Сравнение

Количество строк Размер в мегабайтах (MB)
Таблица 1 Таблица 2 (с использованием BIT столбцов) Разница
1 000 0,02 0,01 0,01
10 000 0,15 0,09 0,07
100 000 1,53 0,86 0,67
1 000 000 15,26 8,58 6,68
10 000 000 152,59 85,83 66,76
100 000 000 1525,88 858,31 667,57

Как видите, после добавления нескольких миллионов строк разница будет ощутимая, и это на простой, маленькой, тестовой таблице.

Про типы данных Microsoft SQL Server у меня все, надеюсь, материал был Вам полезен, для более детального изучения языка T-SQL и SQL сервера в целом, рекомендую почитать мою книгу «Путь программиста T-SQL», удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментариев: 1
  1. Аватар
    Колян Большой Сервак

    Очень полезная информация, спасибо!

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.