Классификация типов данных в SQL Server

В документации по SQL Server принято типы данных объединять в следующие группы:

1. Числовые данные (Numeric). Можно разделить на две подгруппы: точные числа, или числа с фиксированной точностью (Exact Numerics). Сюда включены типы данных:

• BIT;
• TINYINT;
• SMALLINT;
• INT;
• BIGINT;
• NUMERIC;
• DECIMAL;
• SMALLMONEY;
• MONEY.

1. Приближенные числа, или числа с плавающей точкой (Approximate Numerics, Float); включают типы данных:

• FLOAT;
• REAL.

2. Символьные данные (Character). Включает две подгруппы: обычные символьные строки (Character Strings). Сюда включены следующие типы данных:

• CHAR;
• VARCHAR;
• TEXT.

3. Символьные строки в Юникоде (Unicode Character Strings). Типы данных:

• NCHAR;
• NVARCHAR;
• NTEXT.

4. Дата и время (Date and Time). Содержит следующие типы данных:

• DATETIME;
• SMALLDATETIME;
• DATE;
• TIME;
• DATETIMEOFFSET;
• DATETIME2.

5. Двоичные строки (Binary Strings). В эту группу включены следующие типы данных:

• BINARY;
• VARBINARY;
• IMAGE.

6. Пространственные типы данных (Spatial Data Types). Относятся следующие типы данных:

• GEOMETRY;
• GEOGRAPHY.

7. Другие типы данных (Other Data Types). Сюда включены типы данных, не очень подходящие для других категорий. В эту группу включены такие типы данных:

SQL_VARIANT;

TIMESTAMP;

UNIQUEIDENTIFIER;

HIERARCHYID;

CURSOR;

TABLE;

XML.

Тип данных BIT

Множеством допустимых значений для битового типа данных BIT являются 0, 1 и NULL. Множество допустимых операций — это три логические побитовые операции: отрицание (~), дизъюнкция (|) и конъюнкция (&).

Если один из операндов NULL, то результатом является также NULL, даже если другой операнд истинный.

Типы данных NUMERIC и DECIMAL

Типы данных NUMERIC и DECIMAL являются одинаковыми, т. е. фактически это один и тот же тип данных. Для типа данных DECIMAL существует синоним DEC.

Вот синтаксис задания дробного типа данных с фиксированной точкой:

{ NUMERIC | DECIMAL }[(n[, m])]

При задании этого типа данных можно указать точность (параметр n) — общее количество десятичных цифр, включающее количество знаков целой и дробной части, — и масштаб (параметр m) — количество дробных цифр после десятичной точки.

Можно вообще не указывать ни точности, ни масштаба, тогда им присваивается значение по умолчанию. Можно указать точность, не указывая масштаба, масштабу в этом случае также присваивается значение по умолчанию.

Точность может быть указана в диапазоне от 1 до 38. Если точность явно не задана, то по умолчанию устанавливается значение 18.

Масштаб может принимать значение в диапазоне от 0 до установленного (явно или неявно) значения точности. По умолчанию ему присваивается значение 0.

Если не указаны ни точность, ни масштаб, то по умолчанию для определяемого объекта будут установлены значения (18, 0).

Типы данных SMALLMONEY и MONEY

В принципе это тот же тип данных NUMERIC (или DECIMAL) с предустановленными значениями точности и масштаба.

Задание типа данных SMALLMONEY соответствует NUMERIC(10, 4), он занимает 4 байта памяти, а MONEY — NUMERIC(19, 4), занимает 8 байтов.

Эти типы данных используются для работы с денежными (валютными) величинами.

Числа с плавающей точкой FLOAT, REAL

В Transact-SQL предусмотрено два типа приближенных чисел: FLOAT(N) и REAL.

После ключевого слова FLOAT можно в скобках указать количество битов, отводимых для хранения мантиссы числа. Допустимо указание значения от 1 до 53.

Если значение не указано, то принимается 53. Если значение задано в диапазоне от 1 до 24, то для хранения числа отводится 4 байта. Количество значащих цифр будет 7. При задании диапазона от 25 до 53 числу отводится 8 байтов. Количество значащих цифр — 15.

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

Эти типы данных позволяют хранить обычные, не Юникод, строки. Тип данных CHAR имеет фиксированную длину, VARCHAR — переменную. Каждый символ занимает один байт.

Синтаксис задания типов данных:

CHAR[(<целое>)]

VARCHAR[(<целое> | max)]

Для типа данных VARCHAR могут использоваться такие синонимы, как CHAR VARYING и CHARACTER VARYING.

Если при задании любого из указанных строковых типов данных параметр "целое" опущен, то значение устанавливается в 1.

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

Типы данных NCHAR и NVARCHAR позволяют хранить строки в кодировке Юникод. Тип данных NCHAR имеет фиксированную длину, NVARCHAR — переменную. Каждый символ занимает два байта. Такие данные используют набор символов UCS-2. Набор символов UCS (Universal Character Set, универсальный набор символов) определен на основании стандарта ISO 10646. Он позволяет кодировать до 65536 символов. Включает в себя письменность практически всех существующих "живых" языков.

Синтаксис задания типов данных:

NCHAR[(<целое>)]

NVARCHAR[(<целое> | max)]

Для типа данных NCHAR используются синонимы NATIONAL CHAR и NATIONAL CHARACTER. Для типа данных NVARCHAR могут использоваться синонимы NATIONAL CHAR VARYING и NATIONAL CHARACTER VARYING.

Параметр "целое" задает количество символов, которое может содержать строка. Параметр может принимать значение от 1 до 4000. Для типа данных NVARCHAR размер увеличивается на два байта.

Если при задании столбца указать вариант NVARCHAR(max), то, как и в случае типа данных VARCHAR(max), в нем можно хранить большой объем данных.

Если при задании строкового типа данных параметр "целое" опущен, то значение устанавливается в 1.

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

Тип данных DATE позволяет хранить данные в очень большом диапазоне.

Тип данных TIME хранит время с высокой точностью. Синтаксис задания типа данных TIME:

TIME[(<масштаб>)]

Здесь масштаб — число от 0 до 7. Масштаб задает количество дробных знаков секунд или, как сказано в документации, количество сотен наносекунд. Если масштаб не указан, то предполагается 7.

Тип данных DATETIME является объединением типов данных DATE и TIME с масштабом, который приблизительно можно назвать 3.

Тип данных SMALLDATETIME позволяет в очень компактном виде хранить дату и время, которые вполне могут вас устроить при обработке текущих данных, не связанных с большими промежутками времени.

Тип DATETIME2 является расширенным вариантом типа данных DATETIME. Синтаксис задания DATETIME2:

DATETIME2[(<масштаб>)]

<масштаб> — число от 0 до 7. Задает количество дробных знаков секунд. Если не указан, то устанавливается 7.

Тип данных DATETIMEOFFSET похож на тип данных DATETIME2, при этом он еще позволяет учитывать и часовой пояс. Синтаксис задания типа данных DATETIMEOFFSET:

DATETIMEOFFSET[(<точность>, <масштаб>)]

<точность> — целое число от 26 до 34. Задает общее количество знаков;

<масштаб> — число от 0 до 7. Задает количество дробных знаков секунд.

Если точность и масштаб не указаны, то предполагается (34, 7). Помимо даты и времени этот тип данных также содержит величину смещения часового пояса — от –14:00 до +14:00.

Все типы данных, содержащие время, представляют время в 24-часовом формате.

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

Двоичные типы данных содержат произвольные (именно двоичные) данные. Существует три типа таких данных: BINARY, VARBINARY и IMAGE. Тип данных IMAGE будет удален из системы в какой-нибудь из следующих версий. Вместо него следует использовать тип данных VARBINARY(MAX).

Наибольший интерес представляет тип данных VARBINARY. Его синоним — BINARY VARYING. Синтаксис задания типа данных:

VARBINARY[({<размер> | max})}

Размер задает количество байтов, которое отводится для хранения данных. Он может изменяться от 1 до 8000. Если задано ключевое слово max, то данное может занимать до 231 – 1 байтов или около 2 Гбайт.

Этот тип данных может использоваться для хранения любых данных: текстов в различных форматах, изображений, звука, видео, выполняемых файлов.

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

Пространственные (spatial) типы данных появились еще в SQL Server 2008. Международным консорциумом Open Geospatial Consortium (OGC, открытый картографический консорциум) разработаны спецификации OpenGIS (Open Geographic Information Systems, открытые географические информационные системы), определяющие характер интерфейса и функциональность в работе с пространственными данными. Эти спецификации положены в основу работы с подобными даннымив SQL Server.

К пространственным типам данных в SQL Server относятся GEOMETRY и GEOGRAPHY.

С их помощью можно создавать различные геометрические фигуры — точки, линии, многоугольники или, как их еще называют в англоязычной литературе, полигоны. Эти типы данных позволяют задать собственно фигуру, ее внешний вид и ее местоположение в некоторой системе координат.

В базе данных оба типа данных хранятся в одинаковом формате — в виде потока двоичных данных. Размер поля, отводимого для хранения таких данных, является переменным.

Тип данных GEOMETRY применяется к плоским фигурам, имеет прямое отношение к Евклидовой геометрии, где все объекты располагаются на плоской поверхности.

Тип данных GEOGRAPHY используется для задания фигур, объектов, определения расстояний в условиях поверхности Земли, т. е. с учетом формы Земли, которая, какизвестно, является приплюснутым сфероидом. Здесь важны не только размеры фигур, но и их расположение на земной поверхности. Для объектов этого типа данных существует одно ограничение — размер объекта не должен выходить за пределы полусферы (именно половины сферы) Земли, независимо от того, где расположен объект. Это касается только расстояния между любыми точками географического объекта и никак не связано с тем, в каком, например, полушарии располагается объект.

Замечательной особенностью поддержки пространственных типов данных в SQL Server является возможность создания индексов для этих типов данных.

6. Операции над данными

Для целочисленных типов данных допустимы четыре классические арифметические операции: сложение (+), вычитание (-), умножение (*) и деление (/) и операция получения остатка от деления (%).

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

Для всех числовых типов данных, включая и тип данных BIT, поддерживаются следующие операции сравнения:

 = (равно);                                !=, <> (не равно);                  < (меньше);                           > (больше);

<=, !> (меньше или равно, не больше);                               >=, !< (больше или равно, не меньше).

Агрегатные функции

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

• SUM() — суммирует все значения списка.
• MIN() — отыскивает минимальное значение в списке.
• MAX() — отыскивает максимальное значение в списке.
• AVG() — находит среднее арифметическое значение чисел заданного списка.
• STDEV() и STDEVP() — возвращают статистическое стандартное и среднеквадратичное отклонение соответственно.
• VAR() и VARP() — возвращают статистическую дисперсию значений.

Тригонометрические функции

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

1. ACOS(<арифметическое выражение>) — арккосинус числа типа FLOAT. Возвращает значение угла в радианах. Как вы помните, значение передаваемого параметра должно находиться в диапазоне от –1 до +1.
2. ASIN(<арифметическое выражение>) — арксинус числа типа FLOAT. Возвращает значение угла в радианах. Значение передаваемого параметра также должно находиться в диапазоне от –1 до 1.
3. ATAN(<арифметическое выражение>) — арктангенс числа типа FLOAT. Возвращает значение угла в радианах.
4. ATN2(<арифметическое выражение 1>, <арифметическое выражение 2>) — возвращает значение угла в радианах между положительным направлением оси X и лучом, проведенным из начала координат в точку, задаваемую координатами, указанными в качестве параметров функции.
5. COS(<арифметическое выражение>) — косинус угла типа FLOAT, заданного в радианах.
6. COT(<арифметическое выражение>) — котангенс угла типа FLOAT, заданного в радианах.
7. SIN(<арифметическое выражение>) — синус угла типа FLOAT, заданного в радианах.
8. TAN(<арифметическое выражение>) — тангенс угла типа FLOAT, заданного в радианах.
9. RADIANS(<арифметическое выражение>) — возвращает значение угла в радианах для параметра, указанного в градусах.
10. DEGREES(<арифметическое выражение>) — возвращает значение угла в градусах для параметра, указанного в радианах.
11. PI() – возвращает значение числа π.

Логарифмические функции, возведение в степень, извлечение корня

1. EXP(<арифметическое выражение>) — возвращает экспоненту заданного числа.
2. LOG(<арифметическое выражение>) — возвращает натуральный логарифм для заданного числового параметра.
3. LOG10(<арифметическое выражение>) — возвращает десятичный логарифм для заданного параметра.
4. POWER(<арифметическое выражение>, <степень>) — возвращает значение числового параметра, возведенного в указанную степень. Степень может быть представлена не только целым, но и дробным числом — положительным или отрицательным.
5. SQUARE(<арифметическое выражение>) — возвращает квадрат заданного числового параметра.
6. SQRT(<арифметическое выражение>) — возвращает квадратный корень заданного числового параметра.

Другие математические функции

1. ABS(<арифметическое выражение>) — возвращает абсолютное значение заданного параметра, т. е. положительное, точнее неотрицательное, число.
2. CEILING(<арифметическое выражение>) — возвращает наименьшее целое число, большее или равное значению заданного параметра. Другое название функции — округление в большую сторону.
3. FLOOR(<арифметическое выражение>) — возвращает наибольшее целое число, меньшее или равное значению заданного параметра. Другое название — округление в меньшую сторону.
4. ROUND(<арифметическое выражение>, <точность> [, <признак>]) — выполняет округление числа с указанной точностью. Если параметр "признак" равен нулю или отсутствует, то выполняется обычное округление. Если этот параметр имеет положительное значение, то результат округляется до соответствующего количества знаков после десятичной точки. Если параметр отрицательный, то указанное количество знаков целого справа обнуляется, точнее, происходит округление числа до знака признак + 1.
5. RAND([<начальное значение>]) — возвращает псевдослучайное число в диапазоне от 0 до 1. Если начальное значение не задано, то используется случайное начальное значение. Функция при одном и том же заданном начальном значении всегда будет возвращать одинаковый результат в одном соединении с сервером.
6. SIGN(<арифметическое выражение>) — возвращает знак арифметического выражения: число +1, если выражение положительное, –1, если отрицательное, и 0, если выражение равно нулю.

Строковые функции

1. LEN() - возвращает количество символов/
2. DATALENGTH() — возвращает количество байтов.

Пример 1.

USE master;

GO

DECLARE @STR CHAR(20);

SET @STR = ' 123 ';

SELECT LEN(@STR) AS 'LEN', DATALENGTH(@STR) AS 'DATALENGTH';

* Оператор DECLARE объявляет локальную переменную. Имя такой переменной поправилам Transact-SQL должно начинаться с символа @. В операторе также указывается тип данных переменной. Оператор SET присваивает переменной строковое значение.

3. Функция LEFT() возвращает указанное количество первых (левых) символов строки. Синтаксис функции:

LEFT(<исходная строка>, <количество символов>)

4. Функция RIGHT() возвращает указанное количество правых символов строки. Синтаксис функции:

RIGHT(<исходная строка>, <количество символов>)

5. Функция SUBSTRING() позволяет выделить любую часть строки. Из исходной строки выделяется указанное количество символов, начиная с заданной позиции. Символы в исходной строке (строковом выражении) нумеруются, начиная с единицы. Ее синтаксис:

SUBSTRING(<исходная строка>, <начальная позиция>, <количество символов>)

6. LTRIM() - удаляет в заданной строке начальные (левые, left) пробелы,
7. RTRIM() — удаляет в заданной строке конечные (правые, right) пробелы.
8. Функция UPPER(<строковое выражение>) переводит все буквы строки в верхний регистр.
9. Функция LOWER(<строковое выражение>) переводит буквы в нижний регистр, преобразует их в строчные

Пример 2.

USE master;

GO

SET NOCOUNT ON;

DECLARE @string VARCHAR(20);

SET @string = 'Stupid students';

SELECT LEFT(@string, 6) AS 'LEFT',

RIGHT(@string, 8) AS 'RIGHT',

SUBSTRING(@string, 11, 99) AS 'SUBSTRING';

GO

SET NOCOUNT OFF;

Функции преобразования символов

1. Функция ASCII(<строковое выражение>) возвращает код (целое число) заданного первого символа в строке.
2. Функция STR() выполняет преобразование числа с плавающей точкой в строку символов. Синтаксис вызова функции:

STR(<числовое выражение> [, <длина> [, <количество десятичных знаков>]])

<числовое выражение> — преобразуемое число (числовое выражение). Это может быть целое, дробное число или число с плавающей точкой.

<длина> — задает количество символов, отводимых под результат. Учитывает цифры, десятичную точку, знак числа. Если длина не указана, то предполагается 10.

<количество десятичных знаков> - задает количество символов, отводимых для размещения дробных знаков преобразованного числа. Не может превышать 16.

3. Функция CHAR(<числовое выражение>). Параметром является целочисленное выражение. Преобразует число (целое) в символ.
4. Функция NCHAR(<числовое выражение>). Преобразует число (целое) в символ Юникода.
5. Функция UNICODE(<строковое выражение>). Возвращает код (целое число), соответствующего стандарту Юникода, первого символа в строке.

Функции поиска данных и изменения строки

1. Функция REPLICATE(<строковое выражение>, <целое>) повторяет исходную строку указанное количество раз. Если исходное строковое выражение не является строкой с типом данных VARCHAR(MAX) или NVARCHAR(MAX), то при необходимости результат усекается до размера 8000 байтов.
2. Функция REVERSE(<строковое выражение>) переставляет символы исходной строки в обратном порядке.
3. Функция REPLACE(<строковое выражение>, <заменяемое строковое выражение>, <заменяющее строковое выражение>) выполняет замену в исходном строковом выражении все вхождения конкретной подстроки (<заменяемое строковое выражение>) на новое значение (<заменяющее строковое выражение>).
4. Функция CHARINDEX(<отыскиваемое строковое выражение>, <исходное строковое выражение> [, <начальное значение>]) выполняет поиск подстроки в исходном строковом выражении. Поиск начинается с позиции, заданной параметром <начальное значение>. Если этот параметр отсутствует, то поиск начинается с первого символа.
5. Функция возвращает номер позиции в строке, с которой начинается первое вхождение искомой подстроки. Если заданный текст не найден, функция возвращает значение 0.

Функция поиска данных по шаблону PATINDEX()

Функция PATINDEX() позволяет задать весьма сложные условия поиска данных в строке по указанному шаблону.

Дело в том, что эту функцию можно использовать и в операторе сложной выборки данных из одной или более таблиц базы данных, в операторе SELECT, в предложении WHERE, в конструкции LIKE.

Синтаксис функции:

PATINDEX(<шаблон>, <строковое выражение>)

Функция нечувствительна к регистру (в нашем порядке сортировки), она правильно выполняется для обычных строк и для строк в кодировке Юникод.

Используемые шаблонные символы в функции PATINDEX() и в конструкции LIKE предложения WHERE оператора SELECT:

% Произвольная строка, содержащая ноль или любое количество символов

_ Любой один символ

[ ] Задание диапазона допустимых значений. Подходит любой символ из указанного диапазона. Символы диапазона указываются через дефис, например [a-z]. Это означает, что допустимыми являются все буквы латинского алфавита

[^] Задание диапазона недопустимых значений. Исключается любой символ из указанного диапазона. Указание [^a-z] означает, что все буквы латинского алфавита являются недопустимыми. В варианте [^abc] недопустимыми будут только перечисленные символы — a, b и c

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

Функции, возвращающие значение текущей даты, времени и смещения часового пояса

1. SYSDATETIME() — возвращает текущую дату и время, установленные на компьютере.
2. SYSDATETIMEOFFSET() — возвращает текущую дату, время и смещение часового пояса данного компьютера.
3. SYSUTCDATETIME() — возвращает текущую дату и время.
4. CURRENT_TIMESTAMP — возвращает текущую дату и время. Строго говоря, это не функция, а так называемая контекстная переменная. После контекстной переменной, в отличие от функций, круглые скобки не ставятся.
5. GETDATE() — возвращает текущую дату и время.
6. GETUTCDATE() — возвращает текущую дату и время.
7. TIMEFROMPARTS(<часы>, <минуты>, <секунды>, <дробь>, <точность>)

Параметр <точность>, как и в функции DATETIME2FROMPARTS(), определяет количество знаков после десятичной точки в дробной части времени. Не может быть меньше, чем количество значащих цифр (отличных от нуля), указанных в параметре <дробь>.

8. Функция DATENAME() позволяет выделить из заданной даты отдельные элементы. Синтаксис обращения к функции:

DATENAME(<выделяемая часть>, <дата>)

Первый параметр, "выделяемая часть", определяет, какая часть данных будет возвращена функцией и в каком виде. Значения параметра перечислены в табл.1.

Таблица 1. Значение параметра "выделяемая часть" функции DATENAME()

При вызове функции вы можете использовать как полное название выделяемой части, так и сокращение.

9. DATEADD(<часть даты>, <интервал>, <дата>) Для заданной даты указанная часть даты суммируется с интервалом. Полученная дата возвращается функцией. <часть даты> задается фиксированным текстом в диапазоне от года до наносекунд.
10. EOMONTH(<начальная дата> [, <добавляемые месяцы>]) Функция возвращает последний номер дня и время для заданного месяца. Если задан только первый параметр, то возвращается дата и время последнего дня указанного месяца. Например, для февраля будет возвращаться 28 либо 29, если год високосный. Второй параметр задает число, на которое нужно увеличить (или уменьшить, если число отрицательное) номер указанного месяца.

 Объявление локальных переменных

Оператор DECLARE позволяет объявить:

• обычную локальную переменную;
• курсор;
• табличную локальную переменную.

Синтаксис оператора DECLARE для объявления локальных переменных:

DECLARE <имя переменной> [AS] <тип данных> [ = <значение> ]

[, <имя переменной> [AS] <тип данных> [ = <значение> ] ] ... ;

Имя локальной переменной должно начинаться с символа @. Не следует начинать имена локальных переменных с двух символов @@, потому что с этих символов начинаются некоторые системные функции.

В объявлении должен быть указан тип данных после необязательного ключевого слова AS. Это может быть системный тип данных или тип данных, определенный пользователем.

В операторе объявления можно задать начальное значение переменной, которое присваивается переменной после ее объявления, указав после знака равенства литерал (константу).

Присваивание другого, нового значения локальной переменной в тексте пакета можно сделать при выполнении оператора SET. Его несколько упрощенный синтаксис (только для локальных переменных):

SET { <имя переменной> = <выражение>

| <имя переменной>

{+= | -= | *= | /= | %= | &= | ^= | |= } <выражение>

};

В этом операторе переменной можно присвоить конкретное значение, которое возвращает указанное в операторе выражение. Разумеется, тип данных значения, возвращаемого выражением, должен соответствовать типу данных локальной переменной.

• 2015 Февраль
• 2015 Май
• 2015 Июнь
• 2016 Май
• 2017 Май
• 2017 Ноябрь