SQL Server data types

Preamble

The data type is defined as the type of data that any column or variable can store in MS SQL Server.

What is the data type?

When you create any table or variable, in addition to specifying a name, you also specify the data type that it will store.

How to use MS SQL data type

• It is necessary to define in advance the type of data that a column or a variable can store. The definition of the data type also limits the user from entering any unexpected or invalid data.
• You can effectively use memory by assigning the appropriate data type to a variable or column that will allocate only the necessary amount of system memory for the data in the corresponding column.
• MS SQL offers a wide category of data types according to user needs. Like a date, binary images, etc.

Why use DataTypes?

Let’s take a sample of a simple web site application registration page. Three input fields: First name, Last name, and Contact number.

Here it should be noted that in real-time:

• “The Name/Surname will always be alphabetical.
• The “Contact” will always be numeric.

• From the above figure, you should specify “Name/Surname” as a symbol and “Contact” as an integer.
  Obviously, in any application, all fields have one or the other data type. For example, numerical, alphabetic, date, and much more.

Also, note that different types of data have different memory requirements. So it makes sense to define a column or a variable with the type of data it will store for efficient memory usage.

The data type is available in MS SQL

MS SQL Server supports the following categories of data type:

• Exact number
• Approximate numerical
• Date and time
• Character strings
• Unicode Character Strings
• Binary rows
• Other types of data

Exact number

An exact number has nine types of data types.

Accurate numeric data types

Examples:

Request:

DECLARE @Datatype_Int INT = 2
PRINT @Datatype_Int

Exit: 2

Syntax: decimal (P, S)

Here you go,

• Accuracy
• S is the scale

Request:

DECLARE @Datatype_Decimal DECIMAL (3.2) = 2.31
PRINT @Datatype_Decimal

Output: 2.31

Approximate Numerical

The approximate numerical category includes floating-point numbers and actual values. They are mainly used in scientific calculations.

Approximate numeric data type

Syntax: FLOAT [(n)]

Here n is the number of bits that are used to store a mantissa with a floating-point in a scientific record. By default, n is 53.

When a user defines a data type such as float, n should be between 1 and 53.

SQL Server handles n as one of two possible values. If 1 <= n <= 24, n is treated as 24. If 25 <= n <= 53, n is treated as 53.

Example of a query:

DECLARE @Datatype_Float FLOAT(24) = 22.1234
PRINT @Datatype_Float

Exit: 22.1234

Date and time

This is where data like date and time are stored.

Date and Time Data type

Example of request:

DECLARE @Datatype_Date DATE = '2030-01-01'.
PRINT @Datatype_Date

Output: “2030-01-01”

Character strings

This category refers to the type of characters. It allows the user to define a character data type that can be fixed and variable in length. It has four types of data types.

Data type Character strings

Example of request:

DECLARE @Datatype_Char VARCHAR(30) = 'This is Character Datatype'.
PRINT @Datatype_Char

Conclusion: this is the character’s data type

Unicode Character Strings

This category stores the entire range of Unicode characters, which uses UTF-16 encoding.

Unicode character string data types

Example of request:

DECLARE @Datatype_nChar VARCHAR(30) = 'This is nCharacter Datype'.
PRINT @Datatype_nChar

Conclusion: this is nCharacter Datatype

Binary string

This category contains a binary string of fixed and variable lengths.

Binary String Data Types

Example of request:

DECLARE @Datatype_Binary BINARY(2) = 12;
PRINT @Datatype_Binary

Output: 0x000C

Other types of data

These are the other data types described below:

Interesting facts!

CHAR data type is faster than VARCHAR when receiving data.

Summary:

• Each column in a table is defined by its data type when creating the table.
• There are six main categories and one more category. The other different ones have nine subcategories of available data types.