T-SQL - Data Types


Advertisements


SQL Server data type is an attribute that specifies types of data of any object. Each column, variable and expression has related data type in SQL Server. These data types can be used while creating tables. You can choose a particular data type for a table column based on your requirement.

SQL Server offers seven categories including other category of data types for use.

Exact Numeric Types

Type From To
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

Numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate Numeric Types

Type From To
Float -1.79E + 308 1.79E + 308
Real -3.40E + 38 3.40E + 38

Date and Time Types

Type From To

datetime(3.33 milliseconds accuracy)

Jan 1, 1753 Dec 31, 9999

smalldatetime(1 minute accuracy)

Jan 1, 1900 Jun 6, 2079

date(1 day accuracy. Introduced in SQL Server 2008)

Jan 1, 0001 Dec 31, 9999

datetimeoffset(100 nanoseconds accuracy. Introduced in SQL Server 2008)

Jan 1, 0001 Dec 31, 9999

datetime2(100 nanoseconds accuracy. Introduced in SQL Server 2008)

Jan 1, 0001 Dec 31, 9999

time(100 nanoseconds accuracy. Introduced in SQL Server 2008)

00:00:00.0000000 23:59:59.9999999

Character Strings

Sr.No Type & Description
1

char

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

2

varchar

Variable-length non-Unicode data with a maximum of 8,000 characters.

3

Varchar (max)

Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005).

4

text

Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters

Unicode Character Strings

Sr.No Type & Description
1

nchar

Fixed-length Unicode data with a maximum length of 4,000 characters.

2

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters.

3

Nvarchar (max)

Variable-length Unicode data with a maximum length of 230 characters (Introduced in SQL Server 2005).

4

ntext

Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Sr.No Type & Description
1

binary

Fixed-length binary data with a maximum length of 8,000 bytes.

2

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

3

varbinary(max)

Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005).

4

image

Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Other Data Types

  • sql_variant − Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.

  • timestamp − Stores a database-wide unique number that gets updated every time a row gets updated.

  • uniqueidentifier − Stores a globally unique identifier (GUID).

  • xml − Stores XML data. You can store XML instances in a column or a variable (Introduced in SQL Server 2005).

  • cursor − A reference to a cursor.

  • table − Stores a result set for later processing.

  • hierarchyid − A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).



Advertisements