Data Types in SQL Server with examples - WebNetGuru.com

Data Types in SQL Server

While dealing with table structure in SQL Server we have to understand the basic data types in SQL Server and there difference. To use the data type first need to understand there exact use as per software application requirement. Below explain data types in SQL Server with examples.

All below are basic data types in SQL server 2008 onward. Each data types in SQL Server has there own role and use, we can create our own user define data types in SQL Server.

Table of Contents

List of important data types in SQL Server.

CHAR
NCHAR
VARCHAR
NVARCHAR
TEXT
NTEXT
NUMERIC(N,S)
REAL
FLOAT
DECIMAL(N,2)
TINYINT
SMALLINT
INT
BIGINT

DATE
DATETIME
DATETIME2
SMALLDATETIME
TIME
TIMESTAMP
NUMERIC(N,S)
XML
SMALLMONEY
MONEY
BIT
VARBINARY(SIZE)

Below image show the bifurcation of basic data types.

Examples of data types in SQL Server

Numerical data types in SQL Server

All numerical data types in SQL Server is used to store the non-decimal value. The main difference between the numeric data type is range of values, and the size taken by value to store in memory. I use the variable to explain the data type difference.

TINYINT: Range : 0 to 255.
Use to store small number of data like age, roll number etc.
Memory 1 Byte.
Can not store negative value in billing.

Example

                     DECLARE  @tiniInt  TINYINT  = 10
                    
                     SELECT  @tiniInt  AS  Value,
                    
                        LEN
                     (@tiniInt)  AS  ValueLength,
                    DATALENGTH(@tiniInt)  AS  DataTakeInMemory

Output

Check below example, if we overflow the range of TINY INT. This overflow case is applicable in all data types, if data type range exceed then it will give an error.

Example

                     DECLARE  @tiniInt  TINYINT  = 256
                    
                     SELECT  @tiniInt  AS  Value,
                    
                        LEN
                     (@tiniInt)  AS  ValueLength,
                    DATALENGTH(@tiniInt)  AS  DataTakeInMemory

Output

Arithmatic overflow error for data type tiniint

SMALLINT: Range : -32,768 to 32,768.
Use to store medium range of number data etc.
Memory 2 Byte.
It allow negative value.

Example

                     DECLARE  @smallINT  SMALLINT  = 100
                    
                     SELECT  @smallINT  AS  Value,
                    
                        LEN
                     (@smallINT)  AS  ValueLength,
                    DATALENGTH(@smallINT)  AS  DataTakeInMemory

Output

INT: Range : -2,147,483,648 to 2,147,483,648.
Use to store large range of number data etc.
Memory 4 Byte.
It allow negative value.

Example

                     DECLARE  @int  INT  = 123456
                    
                     SELECT  @int  AS  Value,
                    
                        LEN
                     (@int)  AS  ValueLength,
                    DATALENGTH(@int)  AS  DataTakeInMemory

Output

BIGINT: Range : -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808.
Use to store extra large range of number data etc.
Memory 8 Byte.
It allow negative value.

Example

                     DECLARE  @bigInt  BIGINT  = 123456789
                    
                     SELECT  @bigInt  AS  Value,
                    
                        LEN
                     (@bigInt)  AS  ValueLength,
                    DATALENGTH(@bigInt)  AS  DataTakeInMemory

Output

Below table show the What is difference between TINYINT, SMALLINT, INT and BIGINT numerical data type?

DATA TYPE	TINYINT	SMALLINT	INT	BIGINT
Size	1 Byte	2 Byte	4 Byte	8 Byte
Range	0 to 255	-32,768 to 32,768	-2,147,483,648 to 2,147,483,648	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,808

Decimal data types in SQL Server

DECIMAL data type we used to store the decimal / floating data which having precision. In SQL Server NUMERIC also same work as DECIMAL. We always need to assign decimal value in P, S, like Decimal (P, S) or Numeric (P, S) P is precision and S is scale. Storage size of decimal is depend on precision, same you can see in below table.

Precision	Size
1 - 9	5 Byte
10 - 19	9 Byte
20 - 28	13 Byte
29-38	17 Byte

When we set DECLARE @dec DECIMAL(5,3) = 12.220
It means in decimal variable, we can store total 5 digit value. In 5 digit 3 is scale and remaining (5-3) = 2 digits are the precision. Find the below example.It means in decimal variable, we can store total 5 digit value. In 5 digit 3 is scale and remaining (5-3) = 2 digits are the precision. Find the below example.

Example

                     DECLARE  @dec  BIGINT (5,3) = 12.2203
                    
                     SELECT  @dec  AS  Value,
                    
                        LEN
                     (@dec)  AS  ValueLength,
                    DATALENGTH(@dec)  AS  DataTakeInMemory

Output

In above image it work fine because P(precision) is 2 digits, Now try to increase the P(precision) by one digit that is 3 and gives overflow error.

Example

                     DECLARE  @dec  DECIMAL(5,3)  = 112.2203
                    
                     SELECT  @dec  AS  Value,
                    
                        LEN
                     (@dec)  AS  ValueLength,
                    DATALENGTH(@dec)  AS  DataTakeInMemory

Output

arithmatic overflow error converting numeric to data type numeric

What is a difference between DECIMAL and FLOAT?

DECIMAL allow only 38 precision, if need to use more than 38 precision then go for FLOAT.
DECIMAL Range : -10E38 +1 to 10E38 -1
FLOAT Range : - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
DECIMAL also called Non-Approximate numeric data types, where float is called Approximate numeric data types because some cases decimal convert the value to it's near round number (You can see this in below example).

Example

                     DECLARE  @dec  DECIMAL(5,2)  = 112.999,  
                    @float  FLOAT (5,2) = 112.999

                     SELECT  @dec  AS  DecimalValue,
                     SELECT  @float  AS  FloatValue

Output

diffrence between decimal and float datatype in sql server

String data types in SQL Server

SQL Server provides large range of string data types in SQL Server. CHAR, VARCHAR, NCHAR, NVARCHAR are string equivalent data type in SQL Server.
Below comparison will give you idea regarding what data type exactly we must choose to store string.

Let see What is difference between CHAR and VARCHAR data types in SQL Server?

CHAR (Size): Memory : 1 byte for 1 char.
Length : 8000 char.
It not allows to store alpha-numeric character.
It take the given size in memory, if we assign less character then extra space get wastage.

In below example variable size is 8000 but value assigned to variable is 12 character so, it takes 8000 Bytes to store value in memory it means rest size get wastage. Check below example.

Example

                     DECLARE  @char  CHAR (8000) =  'dot net guru' 
                    
                     SELECT  @char  AS  Value,
                    
                        LEN
                     (@char)  AS  CharLenght,
                    DATALENGTH(@char)  AS  MemoryAllocation

Output

VARCHAR (Size): Memory : 1 byte for 1 char.
Length : 8000 char.
It not allows to store alpha-numeric character.
It take the exact space in memory which is equal to value and remain it get release.

In below example variable size is 8000 but value assigned to variable is 12 character so, it takes 12 Bytes to store value in memory and rest it get release. Check below example.

Example

                     DECLARE  @char  VARCHAR  (8000) =  'dot net guru' 
                    
                     SELECT  @char  AS  Value,
                    
                        LEN
                     (@char)  AS  CharLenght,
                    DATALENGTH(@char)  AS  MemoryAllocation

Output

If you check above both string data types the main difference is VARCHAR takes required memory and rest memory it releases, but in the case of CHAR it takes assigned memory.

Let see What is difference between NCHAR and NVARCHAR data types in sql server?

NCHAR (Size): Memory : 2 byte for 1 char.
Length : 4000 char.
It allows to store alpha-numeric character.
It take the given size in memory, if we assign less character then extra space get wastage.

In below example variable size is 4000 but value assigned to variable is 12 character so, it takes 8000 Bytes to store value in memory it means rest size get wastage. Check below example.

Example

                     DECLARE  @nchar  NCHAR  (4000) =  'dot net guru' 
                    
                     SELECT  @nchar  AS  Value,
                    
                        LEN
                     (@nchar)  AS  CharLenght,
                    DATALENGTH(@nchar)  AS  MemoryAllocation

Output

NVARCHAR (Size): Memory : 2 byte for 1 char.
Length : 4000 char.
It allows to store alpha-numeric character.
It take the exact space in memory which is equal to value and remain it get release.

In below example variable size is 4000 but value assign to variable is 12 character so it take 24 Byte to store value in memory and rest it get release. Check below example.

Example

                     DECLARE  @nVarchar  NVARCHAR   (4000) =  'dot net guru' 
                    
                     SELECT  @nVarchar  AS  Value,
                    LEN(@nVarchar) AS  CharLenght,
                    DATALENGTH(@nVarchar) AS  MemoryAllocation

Output

If you check above both string data types the main difference is NVARCHAR takes required memory and rest memory it releases, but in case of NCHAR it takes assigned memory.

Below table shows what is exact difference between CHAR, VARCHAR, NCHAR AND NVARCHAR?

DATA TYPE	CHAR	VARCHAR	NCHAR	NVARCHAR
Size	1 byte for 1 char	1 byte for 1 char	2 byte for 1 char	2 byte for 1 char
Max Ragent	8000 byte	8000 byte	4000 byte	4000 byte
Alpha-Numeric	Not Allow	Not Allow	Allow	Allow
Memory Allocation	take assign space	take space as value assigned	take the assign space	take space as value assigned

DateTime data types in SQL Server

SQL Server provides us verity range of date time data types. To store date time in SQL Server we use the DATE, DATETIME, DATETIME2, SMALLDATETIME and TIME and all valid data types in SQL Server 2008 on-word.

Below are examples of data types in SQL Server use to explain all date time data types with there exact difference . Most of the time interview ask the difference between all MS SQL date time data types.

TIME: Memory : 5 byte.
Format : hh:mm:ss[.nnnnnnn].
Range : 00:00:00.0000000 to 23:59:59.9999999.
It will not allow to store date.
The length of Date is always 16.

In below example we will assign the system time by GETDATE() method. GETDATE() use for to get server current date and time in SQL Server

Example

                     DECLARE  @time  TIME  =  GETDATE() 
                    
                     SELECT  @time  AS  Value,
                    
                        LEN
                     (@time)  AS  DataLenght,
                     DATALENGTH (@time)  AS  MemoryAllocation

Output

DATE: Memory : 3 byte.
Format : YYYY-MM-DD.
Range : 0001-01-01 to 9999-12-31.
It store only date not time.
The length of Date is always 10.

In below example we will assign the system time by GETDATE() method.

Example

                     DECLARE  @date  DATE =  GETDATE() 
                    
                     SELECT  @date  AS  Value,
                    
                        LEN
                     (@date)  AS  DataLenght,
                    DATALENGTH(@date)  AS  MemoryAllocation

Output

SMALLDATETIME: Memory : 4 byte.
Format : YYYY-MM-DD hh:mm:ss.
Range : 1900-01-01 00:00:00 to 2079-06-06 23:59:59.
It will not allow to store more than 2079 year data.
The length of Date is always 19.
Difference between SMALLDATETIME and DATETIME of nano-second and date range.

In below example we will assign the system time by GETDATE() method.

Example

                     DECLARE  @smalldatetime  SMALLDATETIME  =  GETDATE() 
                    
                     SELECT  @smalldatetime  AS  Value,
                    
                        LEN
                     (@smalldatetime)  AS  DataLenght,
                     DATALENGTH (@smalldatetime)  AS  MemoryAllocation

Output

DATETIME: Memory : 8 byte.
Format : YYYY-MM-DD hh:mm:ss[.nnn].
Range : 1753-01-01 to 9999-12-31.
It store date and time, but this data type not allow store date less than 1753.
The length of Date is always 19.

In below example we will assign the system time by GETDATE() method.

Example

                     DECLARE  @datetime  DATETIME  =  GETDATE() 
                    
                     SELECT  @datetime  AS  Value,
                    
                        LEN
                     (@datetime)  AS  DataLenght,
                     DATALENGTH (@datetime)  AS  MemoryAllocation

Output

DATETIME2: Memory : 8 byte.
Format : YYYY-MM-DD hh:mm:ss[.nnnnnnn].
Range : 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999.
Main difference between DATETIME AND DATETIME2 is that DATETIME2 is more accurate than DATETIME.
Microsoft alway recommends to use the DATETIME2 instead of DATETIME.
The length of Date is always 27.

In below example we will assign the system time by GETDATE() method.

Example

                     DECLARE  @datetime  DATETIME2  =  GETDATE() 
                    
                     SELECT  @datetime  AS  Value,
                    
                        LEN
                     (@datetime)  AS  DataLenght,
                     DATALENGTH (@datetime)  AS  MemoryAllocation

Output

Below table shows What is exact difference between TIME, DATE, SMALLDATETIME, DATETIME AND DATETIME2?

DATA TYPE	TIME	DATE	SMALLDATETIME	DATETIME	DATETIME2
Memory	5 byte	3 byte	4 byte	8 byte	8 byte
Date / Time	TIME	DATE	DATE / TIME	DATE / TIME	DATE / TIME
Format	hh:mm:ss[.nnnnnnn]	YYYY-MM-DD	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss[.nnn]	YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Range	00:00:00.0000000 to 23:59:59.9999999	0001-01-01 to 9999-12-31	900-01-01 00:00:00 to 2079-06-06 23:59:59	1753-01-01 to 9999-12-31	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999

Find list of SQL Server data types in details on Microsoft.

WebNetGuru provides the developer to learn and understand the technical concepts. We try to provide solution in easy and step by step way. We continuously keep updating content and topics, so we cannot warrant full correctness of all contents.
WebNetGuru team will try to keep updating new technology tutorial.

Current Tutorials

SQL Server

Upcoming Tutorials

ASP.NET MVC
Angular JS
Mongo DB

contact@webnetguru.com

WORK WITH US

Write For Us

SQL Server

Data Types In SQL Server

Types of tables

SQL Keywords

SQL Constraints

SQL Condition and Loop Statements

SQL Joins

SQL Functions

Data Types in SQL Server

List of important data types in SQL Server.

Examples of data types in SQL Server

Numerical data types in SQL Server

Decimal data types in SQL Server

String data types in SQL Server

DateTime data types in SQL Server