Decimal и numeric (transact-sql)decimal and numeric (transact-sql)

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

Из встроенных типов данных Oracle и пользовательских типов можно строить типы данных произвольной сложности, которые с большой точностью отражают структуру и поведение данных в конкретных системах. В базе данных Oracle они получили еще название абстрактных типов данных.

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL… 4370 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Управление приложениями PL/SQL… 2157 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48

Символьные функции и аргументы… 7348 просмотров Анатолий Wed, 23 May 2018, 18:54:01

Тип данных RAW в PL/SQL 3549 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Author: Дэн

Другие статьи автора:

Using SQL Decimal in the Tables

Let’s quickly create a new table, named Patients, that makes use of decimal data type for columns height and weight. We will insert a few rows using an INSERT clause as shown below for the demo purposes.

Once the data is populated in the table, we can query this data using SELECT statement as shown below. The decimal values can be seen in the height and weight attributes.

Let’s figure out what happens if we try to insert values that exceed the specified precision or scale values while defining the Height and Weight columns. For this demo, we will insert 2 more rows into this table (shown below).

1. 1	INSERTINTOPATIENTSVALUES(‘Largest’,’M’,’10.9′,88.5)

2. 1	INSERTINTOPATIENTSVALUES(‘Hulk’,’M’,’9.9′,1000.45)

It encounters the below error saying arithmetic overflow error and the SQL Server terminated the statements.

Let’s get to the root of this issue:

• Height Decimal (3, 2) means the value can have 3 digits overall and 2 digits to the right of the decimal point. In the first line of code above, the value 10.9 (considered as 10.90 = 4 digits overall) exceeds the specified range (3, 2) and causes the overflow
• Weight Decimal (5,2) means the total number of digits cannot exceed 5 and 2 digits can be placed to the right of the decimal. However, the value 1000.45 in the second line of code above exceeds the specified range of (5, 2) since it means 6 digits in total and throws an overflow error

Quick note – In the above error message, if you have noticed, “data type numeric” is stated instead of data type decimal, the reason is that the Decimal and the Numeric data type are exactly the same, both are fixed-precision data types and can be used interchangeably

Resolving the error

One of the easiest workarounds is to increase the precision level of the columns to store bigger numbers. We can alter the data type of the columns without dropping the table or column with the below code.

Once altered, execute the Insert queries to insert these rows into the table.

We can see the rows being added to the table.

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

Defining SQL Decimal Data type

Let’s work with a very popular mathematical constant – π, aka, Pi that has a value equal to 3.14159 (22/7 in a fraction). Copy and paste the below query in a new query window and execute it.

The above result set shows how SQL Server treats each combination of precision and scale as a different data type. Like here, decimal (6, 0) behaves differently from data types decimal (6,5) and decimal (3,1) and are considered as three different types. This way we can tweak the parameters in the SQL Decimal type to achieve desired results.

Now that we know how to create this Decimal data type in SQL Server, let’s explore it with numerous examples.

Numeric Data Types

MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL from a different database system, these definitions will look familiar to you.

The following list shows the common numeric data types and their descriptions −

• INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.

• TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.

• SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.

• MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

• BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

• FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.

• DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.

• DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

ENUM

Значения строк может быть одним из заранее известных значений (например, список стран). Если список фиксирован (новые страны не появляются каждый день), удобно будет использовать тип ENUM. Он позволяет задать определенный список значений и использовать только их в строках таблицы.

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

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

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

В PL/SQL поддерживаются как вещественные, так и целочисленные типы данных. Тип давно был основным типом для работы с числовыми данными; он может применяться для работы с целыми и вещественными данными как с фиксированной, так и с плавающей запятой. Пример типичного объявления : 

/* Файл в Сети: numbers.sql */
DECLARE
salary NUMBER(9,2); -- фиксированная точка, семь цифр в целой части,
-- две в дробной части
raise_factor NUMBER; -- дробное с плавающей запятой
weeks_to_pay NUMBER(2); -- целое число
BEGIN
salary := 1234567.89;
raise_factor := 0.05;
weeks_to_pay := 52;
END;

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

До выхода Oracle10g тип был единственным числовым типом данных PL/SQL, напрямую соответствовавшим типу столбцов базы данных. В этом нетрудно убедиться, изучив содержимое пакета . Данная особенность стала одной из причин, по которым тип так широко применялся в программах PL/SQL.

В Oracle10g появились два двоичных типа с плавающей запятой: и . Как и , эти двоичные типы данных поддерживаются и в PL/SQL, и в базах данных. Однако в отличие от , эти типы хранят значение в двоичном виде, а следовательно, при работе с ними могут возникнуть погрешности округления. Типы и поддерживают специальные значения (Not a Number, «не является числом»), положительную и отрицательную бесконечность. В некоторых типах приложений эти типы обеспечивают огромный выигрыш в быстродействии, так как вычисления с этими двоичными типами по возможности выполняются на аппаратном уровне.

В Oracle11g появились еще две разновидности вещественных типов. Типы и являются аналогами и , но они не поддерживают и не инициируют исключение в случае переполнения.

PL/SQL поддерживает несколько числовых типов и подтипов, не имеющих прямого соответствия среди типов баз данных, но все равно полезных. Особого внимания заслуживают и . Операции с целочисленным типом реализуются на аппаратном уровне. В частности, счетчики циклов реализуются в формате . Тип , появившийся в Oracle11g, обладает тем же диапазоном значений, что и , но не поддерживает и не инициирует исключение в случае переполнения. , как и с , работает невероятно быстро — особенно с откомпилированным кодом.

Тип данных больших объектов

Тип данных LOB (Large OBject — большой объект) используется для хранения объектов данных размером до 2 Гбайт. Такие объекты обычно применяются для хранения больших объемов текстовых данных и для загрузки подключаемых модулей и аудио- и видеофайлов. В языке Transact-SQL поддерживаются следующие типы данных LOB:

• VARCHAR(MAX);

• NVARCHAR(MAX);

• VARBINARY(MAX).

Начиная с версии SQL Server 2005, для обращения к значениям стандартных типов данных и к значениям типов данных LOB применяется одна и та же модель программирования. Иными словами, для работы с объектами LOB можно использовать удобные системные функции и строковые операторы.

В компоненте Database Engine параметр MAX применяется с типами данных VARCHAR, NVARCHAR и VARBINARY для определения значений столбцов переменной длины. Когда вместо явного указания длины значения используется значение длины по умолчанию MAX, система анализирует длину конкретной строки и принимает решение, сохранять ли эту строку как обычное значение или как значение LOB. Параметр MAX указывает, что размер значений столбца может достигать максимального размера LOB данной системы.

Хотя решение о способе хранения объектов LOB принимается системой, настройки по умолчанию можно переопределить, используя системную процедуру sp_tableoption с аргументом LARGE_VALUE_TYPES_OUT_OF_ROW. Если значение этого аргумента равно 1, то данные в столбцах, объявленных с использованием параметра MAX, будут сохраняться отдельно от остальных данных. Если же значение аргумента равно 0, то компонент Database Engine сохраняет все значения размером до 8 060 байт в строке таблицы, как обычные данные, а значения большего размера хранятся вне строки в области хранения объектов LOB.

Начиная с версии SQL Server 2008, для столбцов типа VARBINARY(MAX) можно применять атрибут FILESTREAM, чтобы сохранять данные BLOB (Binary Large OBject — большой двоичный объект) непосредственно в файловой системе NTFS. Основным достоинством этого атрибута является то, что размер соответствующего объекта LOB ограничивается только размером тома файловой системы.

DDL – Data Definition Language (язык описания данных)

• Табельный номер – целое число
• ФИО – строка
• Дата рождения – дата
• E-mail – строка
• Должность – строка
• Отдел – строка

Значение
Обозначение в MS SQL
Описание
Строка переменной длины
varchar(N)
и
nvarchar(N)
При помощи числа N, мы можем указать максимально возможную длину строки для соответствующего столбца. Например, если мы хотим сказать, что значение столбца «ФИО» может содержать максимум 30 символов, то необходимо задать ей тип nvarchar(30).
Отличие varchar от nvarchar заключается в том, что varchar позволяет хранить строки в формате ASCII, где один символ занимает 1 байт, а nvarchar хранит строки в формате Unicode, где каждый символ занимает 2 байта.
Тип varchar стоит использовать только в том случае, если вы на 100% уверены, что в данном поле не потребуется хранить Unicode символы. Например, varchar можно использовать для хранения адресов электронной почты, т.к. они обычно содержат только ASCII символы.
Строка фиксированной длины
char(N)
и
nchar(N)
От строки переменной длины данный тип отличается тем, что если длина строка меньше N символов, то она всегда дополняется справа до длины N пробелами и сохраняется в БД в таком виде, т.е. в базе данных она занимает ровно N символов (где один символ занимает 1 байт для char и 2 байта для типа nchar). На моей практике данный тип очень редко находит применение, а если и используется, то он используется в основном в формате char(1), т.е. когда поле определяется одним символом.
Целое число
int
Данный тип позволяет нам использовать в столбце только целые числа, как положительные, так и отрицательные. Для справки (сейчас это не так актуально для нас) – диапазон чисел который позволяет тип int от -2 147 483 648 до 2 147 483 647. Обычно это основной тип, который используется для задания идентификаторов.
Вещественное или действительное число
float
Если говорить простым языком, то это числа, в которых может присутствовать десятичная точка (запятая).
Дата
date
Если в столбце необходимо хранить только Дату, которая состоит из трех составляющих: Числа, Месяца и Года. Например, 15.02.2014 (15 февраля 2014 года). Данный тип можно использовать для столбца «Дата приема», «Дата рождения» и т.п., т.е

в тех случаях, когда нам важно зафиксировать только дату, или, когда составляющая времени нам не важна и ее можно отбросить или если она не известна.
Время
time
Данный тип можно использовать, если в столбце необходимо хранить только данные о времени, т.е. Часы, Минуты, Секунды и Миллисекунды

Например, 17:38:31.3231603
Например, ежедневное «Время отправления рейса».
Дата и время
datetime
Данный тип позволяет одновременно сохранить и Дату, и Время. Например, 15.02.2014 17:38:31.323
Для примера это может быть дата и время какого-нибудь события.
Флаг
bit
Данный тип удобно применять для хранения значений вида «Да»/«Нет», где «Да» будет сохраняться как 1, а «Нет» будет сохраняться как 0.

• ID – Табельный номер (Идентификатор сотрудника)
• Name – ФИО
• Birthday – Дата рождения
• Email – E-mail
• Position – Должность
• Department – Отдел

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

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

Storage considerations with Decimal Data Type in SQL Server

Data type SQL Decimal requires the following storage bytes for the specified precision as provided by Microsoft below:

The space consumption of SQL Decimal data type is based on the column definition and not on the size of the value being assigned to it. For e.g. Decimal (12, 4) with value of 888.888 takes 9 bytes on disk and Decimal (22, 2) value of 9999.99 consumes 13 bytes on disk. This is why this data type falls under fixed-length columns.

As a SQL developer myself, I always try to use SQL Decimal data type as decimal (9, 2) which consumes the least storage, 5 bytes on disk and offers better performance.