Numeric data types. Review. Examples
Contents
Search other resources:
1. Numeric data types overview
Microsoft SQL Server supports the following numeric data types:
- bit – integer data type that can take 1, 0, or NULL;
- decimal or numeric – type with fixed precision and scale;
- float or real – types of approximate numerical data;
- int, bigint, smallint, tinyint – integer data types;
- money, smallmoney – types representing monetary (currency) values.
⇑
2. The bit data type. Example
The bit data type defines data that can take 0, 1, or Null. Null values are treated as 0 (zero), and any non-zero values are treated as 1. The Null value is not a null value, that is, it is not equal to 0.
-- Create table with data type bit CREATE TABLE [Flags] ( Flag1 BIT Not Null, Flag2 BIT Not Null, Flag3 BIT Not Null ); INSERT INTO [Flags] VALUES ( 5, 0, 15 ); INSERT INTO [Flags] VALUES ( 0, 0, 11 ); INSERT INTO [Flags] VALUES ( 1, 100, 1 ); SELECT * FROM [Flags]
Result
------------------------ Flag1 | Flag2 | Flag3 | ------------------------ 1 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1 | ------------------------
As you can see from the example, when you try to write negative values or values greater than 1, the resulting value is still 1.
⇑
3. Data types DECIMAL or NUMERIC. Example
The DECIMAL and NUMERIC data types define digits with fixed precision and scale.
The general form of declaring these data types is as follows:
DECIMAL[(p[,s])] NUMERIC[(p[,s])]
here
- p – precision. This is the maximum total number of decimal places. The value of p can range from 1 to 38. By default, p = 18;
- s – scale. This is the maximum number of decimal places placed to the right of the decimal point. The scale must have a value between 0 and p, i.e. 0 ≤ s ≤ p. By default s=0.
Example.
-- Create a table with DECIMAL and NUMERIC data types CREATE TABLE [Numbers] ( Column1 DECIMAL(10,0), Column2 DEC(10,2), -- it is synonymous with DECIMAL Column3 NUMERIC(8,4), Column4 DECIMAL, Column5 NUMERIC ); INSERT INTO [Numbers] VALUES ( 1.23, 1.234, 1234.567, 12.34, 12345 ); SELECT * FROM [Numbers];
Result
--------------------------------------------------- Column1 | Column2 | Column3 | Column4 | Column5 | --------------------------------------------------- 1 | 1.23 | 1234.5670 | 12 | 12345 | ---------------------------------------------------
⇑
4. Data types float and real. Example
The float and real types are approximate data types used for floating point numeric data. These types of data are considered approximate and cannot be accurately displayed. The general declaration of types according to the syntax is the following
float[(n)] real
Here n is the number of bits used to represent the mantissa of a float number. This value displays the precision of the data and the storage format. The value of n can be between 1 and 53.
According to the value of n, the precision is set. All this is shown in the following table.
|
The value of n |
Precision |
Memory size |
|
1..24 |
7 places |
4 bytes |
|
25..53 |
15 places |
8 bytes |
The real type is a synonym for the type
float(24)
Example. The example creates a table containing float and real type declarations
-- Create table with FLOAT and REAL data types CREATE TABLE [FloatNumbers] ( Column1 FLOAT, Column2 REAL, -- this is a synonym for FLOAT(24) Column3 FLOAT(1), -- minimal precision, 4 bytes Column4 FLOAT(53), -- maximum precision, 8 bytes Column5 FLOAT(10), -- 4 байта ); INSERT INTO [FloatNumbers] VALUES ( 1234.56789, 1234.56789, 1234.56789, 12.3456789, 12.3456789 ); SELECT * FROM [FloatNumbers];
Result
----------------------------------------------------------- Column1 | Column2 | Column3 | Column4 | Column5 | ----------------------------------------------------------- 1234.56789 | 1234.568 | 1234.568 | 12.3456789 | 12.34568 | -----------------------------------------------------------
⇑
5. Data types int, bigint, smallint, tinyint. Example
The data types int, bigint, smallint, tinyint are integer data types. The characteristics of the types are defined according to the following table.
|
Data type |
Range | Range representation |
Memory, bytes |
|
bigint |
-9 223 372 036 854 775 808 . . 9 223 372 036 854 775 807 |
-263..263-1 |
8 |
|
int |
-2 147 483 648 . . 2 147 483 647 |
-231..231-1 |
4 |
|
smallint |
-32 768 . . 32 767 |
-215..215-1 |
2 |
|
tinyint |
0 . . 255 |
-20-1..28-1 |
1 |
The base storage type for integer values is int.
Example.
-- Create table with integer data types CREATE TABLE [IntNumbers] ( Column1 BIGINT, -- 8 bytes Column2 INT, -- 4 bytes Column3 SMALLINT, -- 2 bytes Column4 TINYINT, -- 1 byte ); INSERT INTO [IntNumbers] VALUES ( 1234567890, 1234567, 12345, 123 ); SELECT * FROM [IntNumbers];
Result
------------------------------------------- Column1 | Column2 | Column3 | Column4 | ------------------------------------------- 1234567890 | 1234567 | 12345 | 123 | -------------------------------------------
⇑
6. Types money and smallmoney. Example
The money and smallmoney data types are used to represent monetary (currency) values. These types are accurate to one ten-thousandth of a currency unit.
The money data type occupies 8 bytes of memory and can supply numbers from the following range:
–922,337,203,685,477.5808 .. 922,337,203,685,477.5807
The smallmoney data type takes 4 bytes and can supply numbers from the range:
-214 748,3648 ... 214 748,3647
In these types, the symbol ‘ , ‘ (comma) is used to separate integer and fractional units.
Example.
-- Create a table with money data types CREATE TABLE [DemoMoney] ( Column1 MONEY, Column2 SMALLMONEY, Column3 MONEY, Column4 SMALLMONEY ); INSERT INTO [DemoMoney] VALUES ( $249500123.99, $9.58, 123456.78, 0.01 ); SELECT * FROM [DemoMoney];
Result
----------------------------------------------- Column1 | Column2 | Column3 | Column4 | ----------------------------------------------- 249500123.99 | 9.58 | 123456.78 | 0.01 | -----------------------------------------------
⇑
Related topics
- Microsoft Learn – Data types
- Creating tables. Statement CREATE TABLE. Examples
- Table modification. The ALTER TABLE statement
⇑