SQL Server Data Types
In this article, I am going to discuss the SQL Server Data Types with examples. As a developer, it is very important to understand SQL Server Data Types.
What is SQL Server Data Type?
The SQL Server Data Types are the attribute that specifies what types of data entered by the user such as integer, character, decimal, date time etc. In SQL Server Database, each column of a table, all the local variables, and parameters must have a data type. The SQL Server supports the following data types
- Integer data types
- Decimal data types
- Money / currency data types
- Date and Time data types
- Character data types
- Binary data types
- Special data types
The Integer data types:
Integer Data Types are allowed only to hold integer types of values and this data type can be applied on EmpId, ProductCode, BracnchCode columns etc.
These data types are classified into 4 types based on their range and memory size as shown in the below image
Decimal data types:
These data types are allowed decimal point values only.
Decimal Data Type contains two types those are
- Decimal (P, S)
- Numeric (P, S)
But both are same
Here P represent precision and S represent Scale and the default value of the Decimal data type is Decimal (18, 0) and also for Numeric (18, 0).
The Precision is nothing but the maximum number of digits that we can store both to the left side and right side of the decimal point. Precision should have a value from 1 to 38. That is minimum value is 1 and the maximum value is 38. The default value of precision is 18.
For example, if we have a decimal value such as 786.321 then the precision is 6 as we can see we have 3 digits in the left side of the decimal point as well as 3 digits at the right side of the decimal points.
Example: 285.21 here the precision is 5
The scale is nothing but it just indicates the maximum number of decimal digits that we can store to the right of the decimal point. The scale must have a value from 0 through p (i.e. precision). We can specify the scale only if the precision is specified. The default scale is 0.
Example1: 786.321 Here the scale is 3 as we have three digits that are present at the right side of the decimal point.
Example2: 285.21 Scale is 2
The most important point that you need to remember is the default value of a scale is 0 and the maximum value of the Scale depends on the precision value.
The Decimal and Numeric data types are applied on product price, cost of the product, rate of product etc.
Let us see an example:
Price Decimal(6,2) Here the Precision is 6 so we can store a maximum of 6 digits. The scale is 2 so we can store a maximum of 2 digits at the right side of the decimal points. Please have a look at the below table where you will see which values are accepted and which are rejected.
Decimal Data Type Size in SQL Server:
NOTE: numeric is functionally equivalent to decimal.
Money / Currency Data Type:
These data types are used to accept currency format values into a table column.
Money data type again classified into two types.
These data types are applying to employee salary, student fee columns etc.
Date and Time data types:
Date and Time data types are used to store a particular date and time information. These are applying on the date of joining, date of birth, hire date, order date columns etc.
Date and time data types again classified into 3 types, such as
Date: This data type will accept date format information only. The default format of the date data type is ‘YYYY/MM/DD’
Time: It allows time format information only. The default format of the time data type is ‘hh:mm:ss.ms’
DateTime: It allows date and time format information. The default format of DateTime data type is ‘YYYY/MM/DD hh:mm: ss.ms’.
Character Data Types:
Character data types are allowed characters and integer format values.
These data types can be applied on employee name, student name, and product name columns etc.
Character data types again classified into two types those are Unicode data types and Non-Unicode data types.
Non Unicode data types: Char (Size), varchar (size/max), Text
Unicode data types: nchar(size), nvarchar(size), ntext
- It is a fixed length data type (static data type).
- It will store the data type in Non-Unicode mechanism that means it will occupy 1byte for 1 character.
- The maximum length of the char data type is from 1 to 8000 bytes.
Disadvantages: memory wastage because size cannot be changed at runtime.
- It is a variable length data type (dynamic data type) and will store the character in non-Unicode manner that means it will take 1 byte for 1 character.
- The maximum length of the varchar data type is from 1 to 8000 bytes
- The text data type is old version data type of SQL Server and similar to varchar(max) data type.
NOTE: The above 3 data types come under the Non-Unicode mechanism and supported to localized data (English culture data only).
What are the differences between the fixed length data type and a variable length data type?
nchar(Size) data type:
- It is a fixed length data type and will stores the characters in the Unicode manner that means it will take 2bytes memory per a single character.
- The maximum length of nchar data type is from up to 4000bytes.
Nvarchar(size/max) data type:
- It is a variable length data type and will store the data type in the Unicode manner that means it will occupy 2bytes of memory per a single character.
- The maximum length of nvarchar data type is from up to 4000 bytes.
ntext data type:
- It is an old version data type of SQL Server and similar to nvaarchar(max/size) data type.
- Here ‘n’ represents national.
Binary data type:
These data types are used to store image files, audio files, and video files into a database location.
Binary data types again classified into three types, such as
- It is a fixed length data type and will store binary format information (0,1).
- The maximum length of binary data type is from up to 8000 bytes.
- It is a variable length data type and will store the information in the form of binary format.
- The maximum length of varbinary data is from up to 8000bytes (we can store 2gb information).
Image data type:
- It is an old version data type of SQL Server and similar to varbinary data type.
NOTE: Instead of text, ntext and image data type we use varchar(max), nvarchar(max) and varbinary(max) data types in latest versions SQL Server.
- To hold the Boolean values it provides with a bit data type that can take a value of 1, 0, or NULL.
Note: The string values TRUE and FALSE can be converted to bit values. TRUE is converted to 1 and FALSE is converted to 0.
Special data types:
SQL Server supports the following special data types:
Sql_varient data type:
This data type will support all data types data except text, ntext and image data type because these are old version data type of SQL Server.
|101 Pkr 32.68 13579 22/12/2112 101000110|
XML data type:
It is used for storing XML file information (Tag based programming information).
SQL Server Cursor Data Type:
The cursor is an object data type which will allocate a reference memory in server side for storing temporary table information.
Timestamp Datatype in SQL Server:
The Timestamp Is a data type in SQL Server that exposes automatically generated, unique binary numbers within a database. The size of the Timestamp data type is 8 bytes. In SQL Server, we generally use this timestamp on the column of a row to determine whether any data in the row has been changed since the last time it was read. If we do any changes to the data of the row, then the value of the timestamp column of that row is updated. If the data of the row is unchanged then the timestamp value is the same as when it was previously read.
The Unique Identifier is a 16-byte GUID. We can initialize this by using the newid() built-in SQL Server function. The most important point that you need to be remembered is that the GUID values are uniquely identified in a database. That means it is going to be unique within the database.
In the next article, I will discuss Constraint in SQL Server with examples.
In this article, I try to explain SQL Server Data Types step by step with examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.