Data Types in SQL Server

The estimated reading time for this post is 9 minutes

To write effective queries in T-SQL, you should understand how SQL server stores different types of data. This is especially important if your queries not only retrieve data from tables, but also perform comparisons, manipulate data, and implement other operations. In this blog I want to show you built-in systems data types specifications, similarities and differences within categories of data types. SQL server defines a set of system data types for storing data in columns, holding values temporarily in variables, operating on data in expressions, and passing parameters to stored procedures.

Data types specify the type, length, precision and scale of data.

There are seven categories data types as below;

Data Types CategoriesIncluded
Exact Numericbigint - bit - decimal - int - money - numeric - smallint - smallmoney - tinyint
Approximate Numericfloat - read
Date and Timedate - datetime - datetime2 - datetimeoffset - smalldatetime - time
Character Stringschar - varchar - text
Unicode Character Stringsnchar - nvarchar - ntext
Binary Stringsbinary - varbinary - image
Other Date Typescursor - hierarchyid - sql_variant - spatial Geometry types - spatial Geography types - table - rowversion - uniqueidentifier - xml

In SQL server, based on their storage characteristics, some data types are designated as belonging to the following groups;

  • Large value data types: varchar(max) and nvarchar(max)
  • Large object data types: text, ntext, image, varbinary(max) and xml

Exact Numeric

These data types store data with precision, either as:

Integers: whole numbers with varying degrees of capacity.

Decimal: decimal numbers with control over both the total number of digits stored and the       number of digits to the right of the decimal place.

Date TypeRangeStorage (bytes)
tinyint0 to 2551
smallint-32,768 to 32,7682
int-2,147,483,648 to 2,147,483,6474
bigint-9,283,372,036,854,775,808 to 9,283,372,036,854,775,808
bit (is used to store Boolean values)1,0 or NULL
1 for true and 0 for false
(-10^38)+1 through (10^38)-1
when maximum precision is used
precision (1-9)= 5
precision (10-19)= 9
precision (20-28)= 13
precision (29-38)= 17
money -922,337,203,685,447.5808 to 922,337,203,685,447.58078
smallmoney-214,748.3648 to 214,748.36474

Decimal is the ISO standards compliant name for the data type, numeric is used for backward compatibility with earlier versions of SQL server. Numeric is functionally equivalent to decimal.

(P) is precision, the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

(s) is scale, the number of decimal digits that will be stored to the right of the decimal point. Scale can be specified only if precision is specified. Default scale is 0; therefore, 0<=s=<p

Approximate Numeric

These data types allow inexact values to be stored, typically for use in scientific calculations.

Date TypeRange Storage (byte)
float [(n)]-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308if 1<=n=<24
precision is 7 digits
storage is 4 bytes
if 25<=n=<53
precision is 15 digits
storage is 8 bytes
real-3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+384 bytes

(n) is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size.

(n) in float data type is optional and default value for (n) is 53.

The real data type is a synonym for a float data type with a mantissa value of 24(that is, float(24)).

Date and Time

These data types allow date and time data to be stored. To use date and time data in your queries, it is recommended that you use a neutral format as below for consistency and to avoid issues with language or nationality interpretation.

Date TypesStorage (byte)Date Range (Gregorian Calendar)AccuracyRecommended Entry Format
datetime8January 1,1753 to
December 31,9999
Rounded to increments of
.000, .003, or .007 seconds
smalldatetime4January 1,1900 to
June 6,2079
1 minuteYYYYMMDD
datetime26 to 8January 1,0001 to
December 31,9999
100 nanosecondsYYYYMMDD
date3January 1,0001 to
December 31,9999
time3 to 5n/a – time only100 nanosecondshh:mm:ss[.nnnnnnn]
datetimeoffset8 to 10January 1,0001 to
December 31,9999
100 nanosecondsYYYY-MM-

SQL server will implicitly convert the string literals to date and time values.

If you don’t provide all elements of date and time data types, they will show MID NIGHT (00:00:00.000) for missed time information and will show first date of their range as missed date.

SELECT CASE('20171221 12:00' AS DATETIME) AS [DATE and TIME]

If you want to retrieve data from date and time column in specific date, you should use WHERE clause with BETWEEN or use WHERE clause with CAST or CONVERT in your select statement.

SELECT index_advantage AS [INDEX Advantage], CAST(last_user_seek AS DATE) AS [LAST USER Seek]FROM ['Missing indexes$'] WHERE CAST(last_user_seek AS DATE) = '2017-11-07' 
ORDER BY last_user_seek

SELECT index_advantage AS [INDEX Advantage], last_user_seek AS [LAST USER Seek]
FROM ['Missing indexes$'] WHERE '2017-11-07'<= last_user_seek and last_user_seek<'2017-11-08
Order by last_user_seek

SELECT index_advantage AS [INDEX Advantage], CAST(last_user_seek AS DATE) AS [LAST USER Seek]FROM ['Missing indexes$'] WHERE CAST(last_user_seek AS DATE) Between '2017-11-07'and'2017-11-09'
ORDER BY last_user_seek

Date and Time Functions

There are many functions to:

  • Get system date and time values: GETDATE, GETUTCDATE, SYSDATETIME
  • Get date and time parts: DATENAME, DATEPART
  • Get date and time values from their parts: DATETIME2FROMPARTS , DATEFROMPARTS
  • Get date and time difference: DATEDIFF, DATEDIFF_BIG
  • Modify date and time values: DATEADD, EOMONTH
  • Validate date and time values: ISDATE

SELECT DATENAME(MONTH,'2017-12-22 11:55:10.1234567 +08:00') AS [DATENAME]
SELECT DATETIME2FROMPARTS(2017,12,22,12,35,45,0,0) AS [Datetime2fromparts]
SELECT DATEDIFF(DAY,'20171212','20171222') AS [DATEDIFF]

To find more functions please visit

Character String

Working with character data in SQL server can be more complicated. This is because you need to consider multiple language, character sets, accented characters, sort rules and case sensitivity, and capacity and storage. Each of these factors might have an impact on which character data types you encounter when writing queries.

Character data types in SQL server are categorized by two characteristics, support for either fixed-width or variable-width data:-

  • Fixed-width data is always stored at a consistent size, regardless of the number of characters in the character data. Any unused space is filled with padding.
  • Variable-width data is stored at the size of the character data, plus a small overhead.
  • Support for either a single-byte character set or a multi-byte character set:
  • A single-byte character set supports up to 256 different characters, stored as one byte per character. By default, SQL Server uses the ASCII character set to interpret this data.
  • A multi-byte character set supports more than 65,000 different characters by storing each character as multiple bytes—typically two bytes per character, but sometimes more. SQL Server uses the UNICODE UCS-2 character set to interpret this data.

The four available character data types support all possible combinations of these characteristics:

Date TypeFixed width ?Variable Width ?Single-Byte Characters ?Multi-Byte Characters ?

Definitions for columns or variables take an optional value which defines the maximum length of the character data to be stored. You will almost always need to specify a value for the string length; if the maximum length value is not supplied, the default value is one character.

The varchar and nvarchar data types support the storage of very long strings of character data by using max for this value. Use of varchar(max) and nvarchar(max) replaces the use of the deprecated text and ntext types.

Data TypeRangeStorage
1-8000 Characters
1-4000 Characters
n bytes, padded
2*n bytes, padded
1-8000 Characters
1-4000 Characters
Actual length + 2 bytes
Up to 2 GBActual length + 2 bytes

All character data is delimited with single quotation marks.

Single-byte character data is indicated with single quotation marks alone—for example ‘SQL Server’. Multi-byte character data is indicated by single quotation marks with the prefix N (for National)— for example N’SQL Server’. The N prefix is always required, even when inserting the data into a column or variable with a multi-byte type.


In addition to character byte count and length, SQL Server character data types are assigned a collation.

A collation is a collection of properties which determine several aspects of character data, including:  Language or locale, from which is derived:

  • Character set
  • Sort order
  • Case sensitivity
  • Accent sensitivity

A default collation is configured during the installation of SQL Server, but can be overridden on a per-database or per-column basis. As you will see, you might also override the current collation for some character data by explicitly setting a different collation in your query.

When querying, it is important to be aware of the collation settings for your character data—for example, whether it is case-sensitive.

The following query will return different results, depending on whether the column being tested in the WHERE clause is case-sensitive or not. If the column is case-sensitive, this query will return results. Note that the case of the search term matches the case of the data as stored in the database.

Case-Sensitivity Example (1)

SELECT empid, lastname
FROM HR.employees
WHERE lastname = N’Funk’;

Amending the search term, so that the case no longer matches the data as stored in the database, would result in no rows being returned:

Case-Sensitivity Example (2)

SELECT empid, lastname
FROM HR.employees
WHERE lastname = N’funk’;

The COLLATE clause can be used to override the collation of a column and force a different collation to be applied when the query is run. This example forces a case-sensitive and accent-sensitive comparison using the Latin1_General sort rules and character table by adding a COLLATE clause to the WHERE clause:

Using COLLATE in the WHERE Clause

SELECT empid, lastname
FROM HR.employees
WHERE lastname COLLATE Latin1_General_CS_AS = N’Funk’;

Note that database-level collation settings apply to database object names (such as tables and views) as well as to character data.  For example, in a database with a case-sensitive default collation, the table names “HR.Employees” and “HR.employees” would refer to two different objects. In a database with a case-insensitive collation, the table names “HR.Employees” and “HR.employees” would refer to the same object.

Character String Functions


This function takes at least two (or more) data values as arguments and returns a string value with the input values concatenated together. If any of the input data values is not of a character data type, it will be implicitly converted to a character data type. Any NULL values will be converted to an empty string.

SELECT Concat ( 'Fard' , '-' , null , 'Solutions' ) AS [Concated String]
SELECT Concat ( 'Fard' , '-' , null + 'Solutions' ) AS [Concated String]

It was same as below example before SQL server 2012

SELECT 'Fard' + '-' + 'Solutions'  AS [Concated String]
SELECT  'Fard' + '-' + null + 'Solutions'  AS [Concated String]


Allows you to format an input value to a character string based on a .NET format string, with an optional culture parameter.

DECLARE @m MONEY = 120.595
SELECT @m AS [Unformatted VALUES],
Format ( @m , 'c' , 'zh-cn' ) AS [zh_cn Currency],
Format ( @m , 'c' , 'en-us' ) AS [en_us Currency],
Format ( @m , 'c' , 'de-de' ) AS [de_de Currency]

These are some other functions for characters string:

SELECT SUBSTRING('Saeed Jabarpourfard',7,9) AS [SUBSTRING]
 SELECT LEFT('Saeed Jabarpourfard',5) AS [LEFT]
 SELECT RIGHT('Saeed Jabarpourfard',13) AS [RIGHT]
 SELECT LEN('Saeed Jabarpourfard') AS [LEN]
 SELECT DATALENGTH('   Saeed Jabarpourfard     ') AS [Datalength]
 SELECT CHARINDEX('a','Saeed Jabarpourfard',1) AS [CHARINDEX]
 SELECT REPLACE('Saeed Jabarpourfard','Jabarpour','J.') AS [REPLACE]
 SELECT UPPER('saeed jabarpourfard') AS [UPPER]

Binary String

These data types allow binary data to be stored, such as byte streams or hashes, to support custom applications.

Date TypeRangeStorage (byte)
binary(n)1 to 8000 bytesn bytes
varbinary(n)1 to 8000 bytesn bytes +2
varbinary(max)1 to 2.1 billion (approx.) bytesn bytes + 2


The image data type is also a binary string type but is marked for removal in a future version of SQL server. Varbinary(max) should be used instead.

I will discuss about other data types category in other blog. Hope this blog post was informative for you.

Saeed Jabarpourfard

Saeed is a Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer, with extensive experience in Database Development and Administration. He has been working with SQL Server since year 2010, and he used SQL Server 2008 R2 as his first RDBMS.

More Posts - Website

Leave a Comment

Be the First to Comment!

Notify of