Section 1.1: Key Exact Numeric Data Types

Integers

Integers are whole numbers devoid of any fractional or decimal parts. SQL Server provides several data types for integers, each with distinct ranges and storage characteristics:

1. INT (Integer): Represents signed integers with a range from -2,147,483,648 to 2,147,483,647, occupying 4 bytes (32 bits). Commonly used for general integer storage needs such as primary and foreign keys.

-- Create a table with an INT column

CREATE TABLE IntExample (

ID INT PRIMARY KEY,

Quantity INT

);

-- Inserting values into the INT column

INSERT INTO IntExample (ID, Quantity)

VALUES (1, 100000), (2, -2147483648), (3, 2147483647);

-- Querying data from the table

SELECT * FROM IntExample;

2. SMALLINT (Small Integer): Represents signed integers ranging from -32,768 to 32,767, using 2 bytes (16 bits). Ideal for smaller integers where INT's range is unnecessary, optimizing storage space.

-- Create a table with a SMALLINT column

CREATE TABLE SmallIntExample (

ID INT PRIMARY KEY,

Quantity SMALLINT

);

-- Inserting values into the SMALLINT column

INSERT INTO SmallIntExample (ID, Quantity)

VALUES (1, 1000), (2, 32767), (3, -100);

-- Querying data from the table

SELECT * FROM SmallIntExample;

3. TINYINT (Tiny Integer): Represents unsigned integers between 0 and 255, stored in 1 byte (8 bits). Suitable for small integers or flags confined to a small range.

-- Create a table with a TINYINT column

CREATE TABLE TinyIntExample (

ID INT PRIMARY KEY,

Quantity TINYINT

);

-- Inserting values into the TINYINT column

INSERT INTO TinyIntExample (ID, Quantity)

VALUES (1, 100), (2, 50), (3, 255);

-- Querying data from the table

SELECT * FROM TinyIntExample;

4. BIGINT (Big Integer): Represents signed integers with a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, using 8 bytes (64 bits). This type is useful for very large integer values like unique identifiers or timestamps.

Use Cases for BIGINT:

• Storing large numerical identifiers.
• Calculating very large numbers, such as timestamps or quantities in extensive datasets.

Performance Considerations for BIGINT: Though BIGINT offers an expansive range, it can affect performance and memory usage. It is recommended to use the smallest suitable data type to optimize storage and processing efficiency.

CREATE TABLE BigIntExample (

ID INT PRIMARY KEY,

Quantity BIGINT

);

-- Inserting values into the BIGINT column

INSERT INTO BigIntExample (ID, Quantity)

VALUES (1, 10000000000), (2, -9223372036854775808), (3, 9223372036854775807);

-- Querying data from the table

SELECT * FROM BigIntExample;

Section 1.2: Fixed-Point Numerics

Fixed-point numerics, also known as fixed-point data types, allow for precise storage of decimal values with a fixed number of digits before and after the decimal point. Unlike floating-point types, which can sacrifice some precision for a broader range, fixed-point numerics ensure exactness in decimal representation.

DECIMAL/NUMERIC:

These are fixed-point numeric types in SQL that support precise decimal value storage with user-defined precision and scale. They are functionally equivalent in SQL Server, allowing developers to choose based on preference or standards.

Unlike floating-point types like FLOAT or REAL, which are approximate and can introduce rounding errors, DECIMAL and NUMERIC guarantee exact representation without losing precision.

Syntax and Parameters:

DECIMAL(precision, scale)

NUMERIC(precision, scale)

• Precision: Total number of digits that can be stored (1 to 38).
• Scale: Number of digits to the right of the decimal point.

Example Code:

CREATE TABLE ProductPrices (

ProductID INT PRIMARY KEY,

Price DECIMAL(10, 2), -- Total digits: 10, Decimal places: 2

Discount NUMERIC(5, 2) -- Total digits: 5, Decimal places: 2

);

DECIMAL/Numeric vs. FLOAT: Let's examine how DECIMAL and NUMERIC differ from FLOAT, an approximate numeric type.

1. Precision and Exactness:

   DECIMAL and NUMERIC provide exact precision based on their specified parameters. For instance, DECIMAL(10, 2) can accurately store values like 12345.67.

2. Avoiding Rounding Errors:

   DECIMAL and NUMERIC are not susceptible to rounding errors, unlike FLOAT.

Conclusion:

When dealing with numeric data in SQL Server, selecting the right data type is vital for maintaining precision and accuracy in calculations. While FLOAT offers flexibility, it compromises precision, making it less suitable for scenarios requiring exact decimal representation. DECIMAL and NUMERIC types provide a solid foundation for accurately handling decimal values, ensuring data integrity and precision in numeric calculations within databases.