Recent Changes - Search:

add Resources page







Visitor's book Site map pmwiki-2.3.25

Sql Server Data Types

Data types in SQL server

Data types

Deprecated[1] data types are marked by strikethrough.

Best practices

  • use variable length data types
  • avoid tiny and small data types
  • use exact numerics unless otherwise required

Exact numerics [2] [3]

bigint-2^63 -9,223,372,036,854,775,8082^63-1 9,223,372,036,854,775,807[4]
int-2^31 -2,147,483,6482^31-1 2,147,483,647
smallint-2^15 -32,7682^15-1 32,767
decimal-10^38 +110^38 –1[6]
numeric-10^38 +110^38 –1
  • numeric and decimal are Fixed precision and scale data types and are functionally equivalent.
  • money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Approximate numerics

 1.18E-383.40E + 38

Date and time [9]

time2008hh:mm:ss[.nnnnnnn]]00:00:00.000000023:59:59.9999999100 nanoseconds[10]
date2008yy-mm-dd0001-01-019999-12-311 day[11]
datetimeyyyy-mm-dd hh:mm:ss[.nnn]]1753-01-019999-12-313.33 milliseconds[12]
smalldatetimeyyyy-mm-dd hh:mm:ss1900-01-012079-06-061 minute[13]
datetime22008yyyy-mm-dd hh:mm:ss[.nnnnnnn]]0001-01-01 00:00:00.0000000009999-12-31 23:59:59.9999999100 nanoseconds[14]
datetimeoffset2008yyyy-mm-dd hh:mm:ss[.nnnnnnn]] [+|-]hh:mm0001-01-01 00:00:00.0000000009999-12-31 23:59:59.9999999100 nanoseconds[15]

Character Strings

charFixed8,000non-Unicode data[16]
varcharVariable8,000non-Unicode data
varchar(max)Variable2^31-1non-Unicode data
textVariable2^31-1non-Unicode data, use varchar(max)[17]

Unicode Character Strings

nvarcharVariable4,000Unicode data
nvarchar(max)Variable2^31-1Unicode data
ntextVariable2^30 - 1Unicode data, use nvarchar(max)[19]

Binary Strings

binaryFixed8,000binary data[20]
varbinaryVariable8,000binary data
varbinary(max)Variable2^31-1binary data
imageVariable2^31-1binary data, use varbinary(max)[21]

Spacial [22]

Geometry2008used to store planar (flat-earth) data, generally as XY coordinates that represent points, lines, and polygons in a two-dimensional space
Geography2008used to store ellipsoidal (round-earth) data, that is latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface

Other Data Types

sql_variant8016values of various SQL Server-supported data types, except varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, timestamp, sql_variant, geography, hierarchyid, geometry, User-defined types[23]
rowversion automatically generated, unique binary numbers within a database (or timestamp)[24]
uniqueidentifier16a globally unique identifier (GUID)[25]
xml2GBXML documents and fragments[26]
cursor reference to a cursor[27]
table Stores a result set for later processing[28]
hierarchyid2008variablerepresent position in a hierarchy[29]
filestream2008variableallows unstructured data to be stored in the file system instead of inside the SQL Server database[30]

Alias Data Types

Alias types are based on the system data types in SQL Server. Alias types can be used when several tables must store the same type of data in a column and you have to make sure that these columns have identical data type, length, and nullability. For example, an alias type called postal_code could be created based on the char data type.

When an alias data type is created, supply the following parameters:

  • Name
  • System data type upon which the new data type is based
  • Nullability (whether the data type allows null values). When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.

Data type synonyms [31]

Included in SQL Server for ISO compatibility.

SynonymSQL Server system data type
Binary varyingvarbinary
char varyingvarchar
character( n )char(n)
character varying( n )varchar(n)
Double precisionfloat
float[(n)] for n = 1-7real
float[(n)] for n = 8-15float
national character( n )nchar(n)
national char( n )nchar(n)
national character varying( n )nvarchar(n)
national char varying( n )nvarchar(n)
national textntext

Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable.

tahi Page last modified on 2009 May 12 12:48

Edit - History - Recent Changes - WikiHelp - Search - email page as link -> mailto:?Subject=KiwiWiki: Sql Server Data Types&Body=From KiwiWiki: Sql Server Data Types (http://kiwiwiki [period] co [period] nz/Resources/SqlServerDataTypes) - Information about data types and their usage