1. Що таке тип даних в SQL Server?
2. Опис типів даних в T-SQL
3. точні числа
4. Приблизні числа
5. Символьні рядки
6. Символьні рядки в Юникоде
7. дата та час
8. двійкові дані
9. Інші типи даних
10. Пріоритети типів даних в T-SQL
11. Синоніми типів даних в Microsoft SQL Server
12. Поширені помилки при виборі типу даних в T-SQL

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

Якщо Ви тільки починаєте вивчати T-SQL, то на нашому сайті Ви можете знайти корисні матеріали для новачків на дану тему, наприклад статті « Довідник Transact-SQL »І« Основи програмування на T-SQL », Де ми розглядали основні моменти даного мови.

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

Отже, давайте починати.

Що таке тип даних в 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 або шляхом перетворення рядка xxxxxxxxxxxx-xxxxxxxx-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 ", хай щастить!