Data Types

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server.

Data Type Categories

data types
Data Types

Data types in SQL Server are organized into the following categories:

Exact numeric’sUnicode character strings
Approximate numeric’sBinary strings
Date and timeOther data types
Character strings 

Exact Numeric’s

int, bigint, smallint, and tinyint

Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person’s age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building’s age because a building can be more than 255 years old.

Data TypesLower LimitUpper LimitStorage
bigint-2^63 (-9,223,372,036,854,775,808)2^63-1 (9,223,372,036,854,775,807)8 Bytes
int-2^31 (-2,147,483,648)2^31-1 (2,147,483,647)4 Bytes
smallint-2^15 (-32,768)2^15-1 (32,767)2 Bytes
tinyint02551 Byte

decimal and numeric

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

Data TypeLower LimitUpper LimitStorage
decimal−10^38+110^381−15 to 17 bytes

bit

An integer data type that can take a value of 1, 0, or NULL.

Data typeLower LimitUpper LimitStorage
bit011 byte/8bit column

money and smallmoney

Data types that represent monetary or currency values.

Data TypesLower LimitUpper LimitStorage
money-922,337,203,685,477.5808922,337,203,685,477.58078 bytes
smallmoney214,748.3648214,748.36474 bytes

Approximate numeric’s

float and real

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).

Data TypesLower LimitUpper LimitStorage
float-1.79E+3081.79E+308Its storage depends upon value (n)
real-3.40E + 383.40E + 384 Bytes

Syntax
float [ (n) ] Where 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. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

Date and time

date

Defines a date in SQL Server.

Data TypeAccuracyLower LimitUpper LimitStorage
date1 day0001-01-019999-12-313 bytes, fixed
Example:
DECLARE @date date= '12-10-25';
DECLARE @datetime datetime= @date;
SELECT @date AS '@date', @datetime AS '@datetime';
--Result
--@date @datetime
--2025-12-10 2025-12-10 00:00:00.000
--(1 row(s) affected)

datetimeoffset

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Data TypesAccuracyLower LimitUpper LimitStorage
datetimeoffset100 nanoseconds0001-01-019999-12-3110 bytes
Example:
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';
--Result
--@datetimeoffset date
--2025-12-10 12:32:10.0000 +01:0 2025-12-10
--(1 row(s) affected)

datetime2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Data TypeAccuracyLower LimitUpper LimitStorage
datetime2100 nanoseconds0001-01-019999-12-316 bytes
Example:
DECLARE @date date = '12-21-16';
DECLARE @datetime2 datetime2 = @date;
SELECT @datetime2 AS '@datetime2', @date AS '@date';

smalldatetime

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Data TypeAccuracyLower LimitUpper LimitStorage
smalldatetimeOne minute1900-01-012079-06-064 bytes, fixed
Example:
DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @date date = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';
--Result
--@smalldatetime date
--1955-12-13 12:43:00 1955-12-13
--(1 row(s) affected)

datetime

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Data TypeAccuracyLower LimitUpper LimitStorage
datetimeRounded to increments of .000, .003, or .007 secondsJanuary 1, 1753December 31, 99998 bytes
Example:
DECLARE @date date = '12-21-16';
DECLARE @datetime datetime = @date;
SELECT @datetime AS '@datetime', @date AS '@date';
--Result
--@datetime @date
--2016-12-21 00:00:00.000 2016-12-21

time

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

Data TypeAccuracyLower LimitUpper LimitStorage
time100 nanoseconds00:00:00.000000023:59:59.99999995 bytes, fixed

Character strings

char and varchar

Character data types that are either fixed-size, char, or variable-size, varchar. The text data type can store non-Unicode data in the code page of the server.

Data TypeLower LimitUpper LimitStorage
char0 chars8000 charsn bytes
varchar0 chars8000 charsn bytes + 2 bytes
varchar (max)0 chars2^31 charsn bytes + 2 bytes
Example:
DECLARE @myVariable AS VARCHAR = 'abc';
DECLARE @myNextVariable AS CHAR = 'abc';
--The following returns 1
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);
GO

ntext, text, and image

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

Data TypeLower LimitUpper LimitStorage
text0 chars2,147,483,647 charsn bytes + 4 bytes

Unicode character string data types

Unicode character string data types store either fixed-length (nchar) or variable-length (nvarchar) Unicode character data.

Data TypeLower LimitUpper LimitStorage
nchar0 chars4000 chars2 times n bytes
nvarchar0 chars4000 chars2 times n bytes + 2 bytes
ntext0 chars1,073,741,823 char2 times the string length

Binary strings

binary and varbinary

Binary data types of either fixed length or variable length.

Date TypeLower LimitUpper LimitStorage
binary0 bytes8000 bytesn bytes
varbinary0 bytes8000 bytesThe actual length of data entered + 2 bytes
image0 bytes2,147,483,647 bytes*Avoid using this data type,
as it will be deprecated in
future SQL Server releases.
Example:
DECLARE @BinaryVariable2 BINARY(2);
SET @BinaryVariable2 = 123456;
SET @BinaryVariable2 = @BinaryVariable2 + 1;
SELECT CAST( @BinaryVariable2 AS INT);
GO
The final result is 57921, not 123457.

Other data types

cursor

A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor.

rowversion

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes.

hierarchyid

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.

uniqueidentifier

Is a 16-byte GUID.

sql_variant

A data type that stores values of various SQL Server-supported data types.

Syntax:
sql_variant
Example:
CREATE TABLE tableA(colA sql_variant, colB INT)
INSERT INTO tableA values ( CAST(46279.1 as decimal(8,2)), 1689)
SELECT SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',
SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'
FROM tableA
WHERE colB = 1689
Result:
Base Type Precision Scale
decimal 8 2
(1 row(s) affected)

xml

Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.

Syntax:
xml ([ CONTENT | DOCUMENT ] xml_schema_collection)
Example:
USE AdventureWorks;
GO
DECLARE @DemographicData XML (Person.IndividualSurveySchemaCollection);
SET @DemographicData = (SELECT TOP 1 Demographics FROM Person.Person);
SELECT @DemographicData;
GO

Spatial Types – geometry

The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system.

Example:
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

Spatial Types – geography

This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.

Example:
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeogCol1 geography,
GeogCol2 AS GeogCol1.STAsText() );
GO
INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326));
INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO

table

Is a special data type used to store a result set for processing at a later time. table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set.

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply