Numeric Data Types
SQL Server provides different numeric data types to store whole numbers, decimal values and approximate numeric values. Choosing the correct numeric data type helps store data accurately and efficiently.
What are Numeric Data Types?
Numeric data types are used to store numbers in SQL Server. They can be used for values such as age, quantity, marks, salary, price, percentage and other numerical information.
SQL Server provides several numeric data types depending on whether the value is a whole number or requires decimal precision.
Common Numeric Data Types
| Data Type | Description | Example |
|---|---|---|
| tinyint | Stores small whole numbers from 0 to 255. | 100 |
| smallint | Stores small-range whole numbers. | 15000 |
| int | Stores whole numbers and is commonly used for identifiers and quantities. | 50000 |
| bigint | Stores very large whole numbers. | 9000000000 |
| decimal | Stores exact numeric values with fixed precision and scale. | 12500.50 |
| numeric | Equivalent to decimal and stores exact numeric values. | 99.95 |
| float | Stores approximate floating-point numbers. | 123.456 |
| real | Stores approximate single-precision numbers. | 45.67 |
Integer Data Types
Integer data types are used when the value does not contain decimal places.
| Data Type | Storage | Example |
|---|---|---|
| tinyint | 1 byte | 100 |
| smallint | 2 bytes | 15000 |
| int | 4 bytes | 500000 |
| bigint | 8 bytes | 9000000000 |
Decimal and Exact Numeric Values
The decimal and numeric data types are used when exact decimal values are required. They are commonly used for financial and calculation-related values.
CREATE TABLE Products
(
ProductId INT,
ProductName VARCHAR(100),
Price DECIMAL(10,2)
);
In DECIMAL(10,2), the first value represents the total precision and the second value represents the number of digits after the decimal point.
Example: Using Numeric Data Types
The following example creates a Students table using different numeric data types.
CREATE TABLE Students
(
StudentId INT,
Age INT,
Marks DECIMAL(5,2),
Fees DECIMAL(10,2),
TotalStudents BIGINT
);
Here, integer values are stored using integer data types, while values requiring decimal precision are stored using DECIMAL.
Output
StudentId Age Marks Fees
----------------------------------
1 21 85.50 45000.00
2 22 91.25 50000.00
3 20 78.75 40000.00
The output shows how SQL Server stores whole numbers and decimal values using appropriate numeric data types.
Choosing the Correct Numeric Data Type
The numeric data type should be selected according to the size and type of value that needs to be stored.
- Use INT for commonly used whole numbers.
- Use BIGINT for very large whole numbers.
- Use DECIMAL for exact decimal values.
- Use FLOAT when approximate numeric values are acceptable.
Summary
In this section, we learned about SQL Server numeric data types such as TINYINT, SMALLINT, INT, BIGINT, DECIMAL, NUMERIC, FLOAT and REAL. We also learned how to choose an appropriate numeric data type according to the type and size of data being stored.