Numeric Data Types

In SQL Server, numeric data types are used to store numeric values. These types can be broadly categorized into exact numeric types and approximate numeric types. Each type is designed for specific use cases, depending on the precision and scale required. Here's a detailed explanation of each numeric data type, with examples and key differences:

Exact Numeric Data Types

1. INT

• Description: Stores integer values.
• Range: -2,147,483,648 to 2,147,483,647.
• Storage Size: 4 bytes.
• Example: Used for counting items or storing whole numbers without decimal places.

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

Quantity INT NOT NULL

);

2. BIGINT

• Description: Stores large integer values.
• Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• Storage Size: 8 bytes.
• Example: Suitable for large datasets like transaction IDs in large systems.

CREATE TABLE Transactions (

TransactionID BIGINT PRIMARY KEY,

Amount BIGINT NOT NULL

);

3. SMALLINT

• Description: Stores small integer values.
• Range: -32,768 to 32,767.
• Storage Size: 2 bytes.
• Example: Used when storage space is a concern, and the values fit within the range.

CREATE TABLE Employees (

EmployeeID SMALLINT PRIMARY KEY,

Age SMALLINT NOT NULL

);

Unlock New Opportunities - Join Our SQL Server Developer Course at TrainingHub.io!

4. TINYINT

• Description: Stores very small integer values.
• Range: 0 to 255.
• Storage Size: 1 byte.
• Example: Suitable for storing values that are always within the small range, like status codes.

CREATE TABLE StatusCodes (

StatusCode TINYINT PRIMARY KEY,

Description NVARCHAR(50) NOT NULL

);

5. DECIMAL (or NUMERIC)

• Description: Stores fixed-point numbers with a specified precision and scale.
• Range: Depends on precision. Maximum precision is 38.
• Storage Size: Varies based on precision.
• Example: Suitable for financial data where precision is crucial.

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

Price DECIMAL(10, 2) NOT NULL

);

6. MONEY and SMALLMONEY

MONEY

• Description: Stores monetary values.
• Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
• Storage Size: 8 bytes.

SMALLMONEY

• Description: Stores smaller monetary values.
• Range: -214,748.3648 to 214,748.3647.
• Storage Size: 4 bytes.

Example: Suitable for financial applications.

CREATE TABLE Salaries (

EmployeeID INT PRIMARY KEY,

AnnualSalary MONEY NOT NULL,

Bonus SMALLMONEY

);

Join TrainingHub.io’s Data Analyst courses in Toronto to network with other data professionals and expand your career prospects.

Approximate Numeric Data Types

1. FLOAT

• Description: Stores approximate numeric values with floating-point precision.
• Range: Depends on the number of bits specified for the mantissa.
• Storage Size: Varies (4 bytes for 1-24 bits of precision, 8 bytes for 25-53 bits of precision).
• Example: Suitable for scientific calculations where precision is less critical.

CREATE TABLE Measurements (

MeasurementID INT PRIMARY KEY,

Value FLOAT NOT NULL

);

2. REAL

• Description: Stores approximate numeric values with floating-point precision.
• Range: -3.40E+38 to 3.40E+38.
• Storage Size: 4 bytes.
• Example: Used when a smaller storage size is needed compared to FLOAT.

CREATE TABLE Temperatures (

TemperatureID INT PRIMARY KEY,

Value REAL NOT NULL

);

Key Differences

• Storage Size: Different data types use different amounts of storage. For instance, INT uses 4 bytes, while BIGINT uses 8 bytes.
• Range: The range of values that can be stored varies between types. For example, TINYINT can store values from 0 to 255, whereas BIGINT can store much larger values.
• Precision and Scale: DECIMAL and NUMERIC allow for precise control over the number of digits and decimal places, while FLOAT and REAL provide approximate values.
• Use Cases: Choose the data type based on the requirements of your application. Use INT for counting, DECIMAL for financial calculations, and FLOAT for scientific data.

Learn SQL Server Inside Out - Enroll in Our Developer Course Now! Enroll at TrainingHub.io, the best IT training institute in Canada, and benefit from our extensive network of industry connections.