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.



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.

List of data types in sql server

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

Tiny Int data type in sql server


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

Small int data type in sql server

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

Int data type in sql server

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

big int data type in sql server

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

decimal data types in sql server


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

charter or char data type in sql server

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

varchar data type in sql server


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

nchar data type in sql server

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

nvarchar data type in sql server


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

Time datatype in sql server

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

date datatype in sql server

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

small date time datatype in sql server

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

date time datatype in sql server

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

date time 2 data type in sql server

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.

Move Top