Recent Changes - Search:

add Resources page

hide

Resources


hide

Topics


show

Changed

Visitor's book Site map pmwiki-2.3.30

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]

TypeFromToRef
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
tinyint0255
bit01[5]
decimal-10^38 +110^38 –1[6]
numeric-10^38 +110^38 –1
money-922,337,203,685,477.5808+922,337,203,685,477.5807[7]
smallmoney-214,748.3648+214,748.3647
  • 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

TypeFromToRef
float-1.79E+308-2.23E-308[8]
 00
 2.23E-3081.79E+308
real-3.40E+38-1.18E-38
 00
 1.18E-383.40E + 38

Date and time [9]

TypeFormatFromToAccuracyRef
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

TypeLengthMaximumDescriptionRef
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

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

Binary Strings

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

Spacial [22]

TypeDescription
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

TypeLengthDescriptionRef
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
characterchar
characterchar(1)
character( n )char(n)
character varying( n )varchar(n)
Decdecimal
Double precisionfloat
float[(n)] for n = 1-7real
float[(n)] for n = 8-15float
integerint
national character( n )nchar(n)
national char( n )nchar(n)
national character varying( n )nvarchar(n)
national char varying( n )nvarchar(n)
national textntext
timestamprowversion

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=