SQL Datatypes:

SQL Datatypes

Introduction to SQL Datatypes

Welcome to InsightNinja your one stop destination for SQL and data analytics.

When I started learning programming languages, I found the section on datatypes to be particularly frustrating, just because of plain explanation of datatype and their ranges. To make it easier for beginners to understand, I have explained each SQL Server datatype using real-world scenarios in this blog.

Basically SQL Server datatypes are used to define the type of data that can be stored in a column of a table in a SQL Server database. The various datatypes supported by SQL Server include:

1.       Numeric Data Type

2.       Date and Time Data Types

3.       Character and String Data Types

4.       Binary Data types 

Numeric Data Types

1.       Integer Data types:

·         Int

It is a 32-bit(4 byte) data type that can store integer values ranging from -2,147,483,648 to 2,147,483,647.

If you look closely to border values you will understand that storing upto 9 digit number (positive/negative) in an int data type is alright. It is ill adviced to use int as a datatype for whole number having 10 or more digits, in that case you better go with bigint. 

DECLARE @myInt INT = 123456789;

SELECT @myInt;

Output: 123456789 

·         Tinyint:

Can store whole numbers from 0 to 255 and requires only 1 byte of storage. It can be useful for storing flags or small numbers that don't require the larger storage space of an int.

 DECLARE @myTinyInt TINYINT = 127;

SELECT @myTinyInt;

Output: 127  

·         Smallint:

smallint can store whole numbers from -32,768 to 32,767 and requires 2 bytes of storage. It is often used for smaller numbers that don't require the range of an int, such as representing small counts or indexes. 

DECLARE @mySmallInt SMALLINT = -12345;

SELECT @mySmallInt;

Output: -12345 

·         Bigint : 

      It is a SQL Server data type that can store very large integer values. It is an 8-byte integer data type, which means it can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. 

It is adviced to use bigint in case your integer length exceeds 9 digits.

As bigint takes double the space than that of int datatype. 

DECLARE @myBigInt BIGINT = -987654321111;

SELECT @myBigInt;

Output: -987654321111

 

Freebie:  Assume you were storing the age of a person in a database, TINYINT would be sufficient since the maximum age of a person is typically less than 120. However, if you were storing the number of atoms in the universe, BIGINT would be necessary since this number is much larger than the maximum value of INT.

 

2.       Decimal data types:

·         Decimal/Numeric:

These data types are used for exact numeric values with a fixed precision and scale. The precision specifies the total number of digits in the value, while the scale specifies the number of digits to the right of the decimal point. DECIMAL/NUMERIC can store up to 38 digits of precision and up to 38 digits to the right of the decimal point.

For example, a DECIMAL(7, 2) data type has a precision of 7, with 2 digits to the right of the decimal point. 

DECLARE @price DECIMAL(7, 2) = 1234.56;

SELECT @price;

O/P:  1234.56 

·         Float:

These data types are used for approximate numeric values with a floating-point precision. FLOAT can store up to 15 digits of precision

DECLARE @myFloat FLOAT = 12345678.12;

SELECT @myFloat;

Output: 12345678.12 

·         REAL:

Similar to FLOAT, REAL can store up to 7 digits of precision.

DECLARE @myFloat REAL(10) = 1234.5678;

SELECT @myFloat;

Output: 1234.5678 

·         MONEY/SMALLMONEY:

These data types are used for monetary values. MONEY can store values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807, while SMALLMONEY can store values between -214,748.3648 and 214,748.3647 

DECLARE @myMoney MONEY = 12345555555555.67;

SELECT @myMoney;

Output: 12345555555555.67              

  

Date and Time Data Types

In SQL Server, there are several data types related to date and time, including:

1. DATE: 

This data type stores the date value in YYYY-MM-DD format. The range of values for `DATE` is from January 1, 0001, to December 31, 9999.

DECLARE @myDate DATE = '2023-05-15';

SELECT @myDate;

Output: 2023-05-15 

2. TIME: 

This data type stores the time value in HH:MM:SS format. The range of values for `TIME` is from 00:00:00.0000000 to 23:59:59.9999999. 

DECLARE @myTime TIME = '12:34:56.789';

SELECT @myTime;

Output: 12:34:56.7890000 

3. DATETIME: 

This data type stores both the date and time values. The range of values for `DATETIME` is from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. 

DECLARE @myDateTime DATETIME = '2023-05-15 12:34:56.789';

SELECT @myDateTime;

Output: 2023-05-15 12:34:56.790

4. DATETIME2: 

This data type is similar to `DATETIME`, but has a higher accuracy of up to 100 nanoseconds and a larger range of values. The range of values for `DATETIME2` is from January 1, 0001, to December 31, 9999. 

DECLARE @myDateTime2 DATETIME2(7) = '2023-05-15 12:34:56.7891234';

SELECT @myDateTime2;

Output: 2023-05-15 12:34:56.7891234 

5. SMALLDATETIME: 

This data type stores both the date and time values, but with a lower accuracy of 1 minute. The range of values for `SMALLDATETIME` is from January 1, 1900, to June 6, 2079. 

DECLARE @mySmallDateTime SMALLDATETIME = '2023-05-15 12:34:00';

SELECT @mySmallDateTime;

Output: 2023-05-15 12:34:00

 

Character and String Data Types

1. CHAR: 

This data type stores a fixed-length string with a maximum length of 8,000 characters. If the specified length is less than 8,000, SQL Server pads the string with spaces to the specified length. 

DECLARE @myChar CHAR(10) = 'Hello';

SELECT @myChar;

Output: Hello 

2. VARCHAR: 

This data type stores a variable-length string with a maximum length of 8,000 characters. The actual length of the string is stored along with the data. 

DECLARE @myVarChar VARCHAR(10) = 'Hello';

SELECT @myVarChar;

Output: `Hello` 

3. TEXT: 

This data type stores a variable-length string with a maximum length of 2,147,483,647 characters.

DECLARE @myText TEXT = 'This is a long text string.';

SELECT @myText;

Output: `This is a long text string.`          

 

4. NCHAR: 

This data type stores a fixed-length Unicode string with a maximum length of 4,000 characters. If the specified length is less than 4,000, SQL Server pads the string with spaces to the specified length.

DECLARE @myNChar NCHAR(10) = N'こんにちは';

SELECT @myNChar;

Output: `こんにちは `

 

5. NVARCHAR: 

This data type stores a variable-length Unicode string with a maximum length of 4,000 characters. The actual length of the string is stored along with the data.

DECLARE @myNVarChar NVARCHAR(10) = N'こんにちは';

SELECT @myNVarChar;

Output: `こんにちは`

 

Binary Data Types

A binary object is a data type used to store binary data, such as images, audio, video, and other non-textual data. A binary object is represented as a sequence of bytes and can have a maximum size of 2^31-1 bytes (or 2GB) depending on the data type used.

1. BINARY: 

This data type is used to store a fixed-length binary data with a maximum length of 8,000 bytes. If the specified length is less than 8,000 bytes, SQL Server pads the data with zeros to the specified length.

DECLARE @myBinary BINARY(4) = 0x12345678;

SELECT @myBinary;

Output: 0x12345678 

2. VARBINARY:

 This data type is used to store a variable-length binary data with a maximum length of 8,000 bytes. The actual length of the data is stored along with the data.

DECLARE @myVarBinary VARBINARY(4) = 0x12345678;

SELECT @myVarBinary;

Output: 0x12345678

3. IMAGE: 

This data type is used to store a variable-length binary data with a maximum length of 2,147,483,647 bytes. 

DECLARE @myImage IMAGE = 0x12345678;

SELECT @myImage;

Output: 0x12345678

4. BLOB: 

This data type is used to store a large binary object with a maximum length of 2,147,483,647 bytes. BLOBs are used to store data such as images, audio, and video files.      

Choosing the Right Data Type:

Choosing the right data type is an important consideration when designing a SQL Server database, as it can have a significant impact on the database's performance, storage requirements, and scalability. Here are some guidelines for choosing the right data type:

1. Choose the smallest data type possible: The smaller the data type, the less storage space it requires. For example, if you know that a column will only contain integers between 0 and 255, you can use the `TINYINT` data type instead of `INT`, which can save a significant amount of storage space.

2. Consider the range of possible values: Choose a data type that can accommodate the range of values that the column will contain. For example, if a column will contain dates, use the `DATE` or `DATETIME` data type instead of a string data type, which can improve performance and ensure data consistency.

3. Consider the precision and scale of numeric data types: Numeric data types such as `DECIMAL` and `NUMERIC` allow you to specify the precision and scale of the column. The precision specifies the total number of digits that can be stored in the column, while the scale specifies the number of digits to the right of the decimal point.

4. Consider the size of text data: If a column will contain text data, use a `VARCHAR` or `NVARCHAR` data type instead of a fixed-length data type like `CHAR`. Also, consider using the `VARCHAR(MAX)` data type if the column may contain large amounts of text data.

5. Consider the performance implications of large objects: If a column will contain large objects such as images, audio, or video files, consider storing them in a separate table or using the `FILESTREAM` feature to improve performance. 

Conclusion:

Hope you have enjoyed this blog. Please share your feedback/questions in comment section.

Happy  learning.