SQL database will store the data in different forms, such as text, numbers, decimals, dates, and other forms, and these are known as data types. The data is stored in the columns of a table. There are around 30 different types of data types you can find in the SQL database. Using the data types is easier for the developers, but one should remember the SQL data type. However, not all the data types are used in the applications.
The widely used ones are text, Integer, and dates. It is simple for people to understand the data types.
Define SQL data type
The data type is crucial for you to define the type of data that a column would have. If you are collecting the customer information in the application, the details would have the name of the customer, date of birth, weight, number of children, and other data.
For each of these attributes, the data type would differ. You cannot use the same data type for all attributes. Say, for example, the character data type cannot be used for the number of children. You can store the names as text and number of children Integer and weight as a decimal value.
The data type would let you define the data characteristics that you can store in the column of a table in the database. The data type would give the set of values that are accepted. In the SQL server, the data type, INT, would allow only the whole numbers, and it won’t take the decimals.
Example of data type
You can see the data types examples in the following table with two columns: the customer name and the other is the age of the customer.
When you store the age in text format and Integer, the data would be inconsistent. It becomes challenging to calculate the data.
The SQL Server data types would allow you to use the particular type of data in the column to get accurate results when a customer query for the information.
The name of the customer has characters, and the ages are using the INT data type. When you define the data type for the age as Integer, the customer would be allowed to enter only the numeric values without which it throws an error.
When you store the age in integer values, it takes up lesser space compared to the age that is stored in the text form. The system must learn the data type to sort the data with ease and perform the calculations.
Various reasons to use SQL data types
Here is the list of reasons why you must use the SQL data type
- Data can be stored in a consistent format
- When you know the data type, it becomes easier to use the right calculations in the column
- Data types have an impact on storage space. There are a few values that take up more space compared to another. Say, for example, the age of the customer, when stored in the Integer data type, space is less than the character data type.
- Data types have an impact on the performance
Data types examples
You can define the SQL data type, which you can store in the columns of a table. The columns can have text or numeric data. The data type of the column would decide the type of data you can store. There are around 30 different types of data types available. Each one would have a specific purpose. The most often used data types in the SQL server would be discussed below
This data type would only store the whole number and not the decimals. Example: 1, 2, 3, and 889, and so on. You can set the maximum and minimum number that can be allowed for that particular column with the data type as Int.
The Int data type can store the values ranging from -2,147,483,648 to 2,147,483,647. You can use this to store the age of the person and customer ID. It is tough to track the data that is consumed by this type of data type when it is in trillions. If the INT is big, you must use BIGINT
VARCHAR and NVARCHAR
These are used to store the text that varies in its length. The VARCHAR is the variable-length character. You must specify the number of characters that are allowed in the text column using the VARCHAR or NVARCHAR.
The best part of the VARCHAR is that the characters that are less than 50 would occupy less space. Moreover, the CHAR data type would allocate a specified length for the text. With the VARCHAR data type, you can store around 8000 characters.
The NVARCHAR would be used to store the Unicode text. The UNICODE text would take twice the space over the VARCHAR. Using the NVARCHAR data type, you can store 4000 characters.
The best part of NVARCHAR is that you can store Unicode text, which is impossible with the VARCHAR. You can store the characters that are used in other languages.
Use this data type to store date and time. The best example of the date-time data type is 09-09-1966 1:55:23. It is how the data is stored in the tables for Sep 09, 1966, at 1.55 am. The time is very accurate, and it also saves the seconds.
The DATETIME data type can store the dates ranging from Jan 1, 1753, through Dec 31, 9999. Moreover, it allows you to keep track of the date. The data types that are supported by SQL include:
It is another data type that is used to store decimal values. Example: weight. It stores with precision. The precision determines the total digits you can store, and the scale would determine the number of digits after the decimal point.
The data type for decimal would be indicated as DECIMAL (precision, scale)
This data type can store only 38 digits. For example, the column name as the weight of a product can be defined as DECIMAL (8, 2). The first digit is the precision, and the second digit is the scale.