# 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   |
-----------------------------------------------```