Exact Numeric Data Types in SQL Server Transact-SQL

Q

What are exact numeric data types supported in SQL Server Transact-SQL?

✍: FYIcenter.com

A Exact numeric data types are used to hold numeric values truncated to a specific precision and scale.

There are 8 different exact numeric data types supported in SQL Server Transact-SQL:

1. BIGINT - Used to hold big integers with 8-byte storages to store integers in the range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).

2. INT (or INTEGER) - Used to hold normal integers with 4-byte storages to store integers in the range of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

3. SMALLINT - Used to hold small integers with 2-byte storages to store integers in the range of -2^15 (-32,768) to 2^15-1 (32,767).

4. TINYINT - Used to hold tiny integers with 1-byte storages to store integers in the range of 0 to 255.

5. BIT - Used to hold single bit values: 0 and 1.

6. DECIMAL (or DEC, or NUMERIC) - Used to hold values with different precisions and scales specified in the format of DECIMAL(p,s), where p defines the precision and s defines the scale. For example, DECIMAL(9,2) can hold values of 9 digits with 2 digits in the fractional part. The maximum precision supported is 38.

7. MONEY - Used to hold large currency values with 8-byte storages to store values in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. So MONEY is similar to DECIMAL(19,4).

8. SMALLMONEY - Used to hold small currency values with 4-byte storages to store values in the range of - 214,748.3648 to 214,748.3647. So SMALLMONEY is similar to DECIMAL(7,4).

Here are some good examples of exact numeric values:

```PRINT 372036854775808; -- BIGINT
PRINT 147483648; -- INT
PRINT 2768; -- SMALLINT
PRINT 250; -- TINYINT
PRINT 1; -- BIT
PRINT 12748.3648; -- DECIMAL(9,2)
PRINT 337203685477.58; -- MONEY
PRINT 748.36; -- SMALLMONEY
```

2017-04-22, 700👍, 0💬