Javatpoint Logo
Javatpoint Logo

SQL Server Data types

Data types can be divided in three types:

  • String Types
  • Number Types
  • Date Types

String Types

Data Type Description
char(n) It is a fixed width character string. It can store maximum 8,000 characters.
varchar(n) It is a variable width character string. It can store maximum 8,000 characters.
varchar(max) It is a variable width character string. It can store maximum 1,073,741,824 characters.
text It is a variable width character string. It can store maximum 2GB of text data.
nchar It is a fixed width unicode string. It can store maximum 4,000 characters.
nvarchar It is a variable width unicode string. It can store maximum 4,000 characters.
nvarchar(max) It is a variable width unicode string. It can store maximum 536,870,912 characters.
ntext It is a variable width unicode string. It can store maximum 2GB of text data.
bit It allows 0, 1, or NULL values.
binary(n) It is a fixed width binary string. It can store maximum 8,000 bytes
varbinary It is a variable width binary string. It can store maximum 8,000 bytes
varbinary(max) It is a variable width binary string. It can store maximum 2GB.
image It is a variable width binary string. It can store maximum 2GB.

Number Types

data type description storage
tinyint allows whole numbers from 0 to 255 1 byte
smallint allows whole numbers between -32,768 and 32,767 2 bytes
int allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s) fixed precision and scale numbers.allows numbers from -10^38 +1 to 10^38 ?1.the p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. default is 18.the s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. default value is 0 5-17 bytes
numeric(p,s) fixed precision and scale numbers.allows numbers from -10^38 +1 to 10^38 ?1.the p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. default is 18.the s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. default value is 0 5-17 bytes
smallmoney monetary data from -214,748.3648 to 214,748.3647 4 bytes
money monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) floating precision number data from -1.79e + 308 to 1.79e + 308.the n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. default value of n is 53. 4 or 8 bytes
real floating precision number data from -3.40e + 38 to 3.40e + 38 4 bytes

Date Types

Data Type Description Storage
datetime It specifies a date and time from january 1, 1753 to december 31, 9999 with an accuracy of 3.33 milliseconds. 8 bytes
datetime2 It specifies a date and time from january 1, 0001 to december 31, 9999 with an accuracy of 100 nanoseconds. 6-8 bytes
smalldatetime It specifies a date and time from january 1, 1900 to june 6, 2079 with an accuracy of 1 minute. 4 bytes
date It is used to store a date only. It specifies a date from january 1, 0001 to december 31, 9999. 3 bytes
time It is used to store a time only to an accuracy of 100 nanoseconds. 3-5 bytes
datetimeoffset It is the same as datetime2 with the addition of a time zone offset. 8-10 bytes
timestamp It stores a unique number that gets updated every time a row gets created or modified. the timestamp value is based upon an internal clock and does not correspond to real time. each table may have only one timestamp variable.





Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


B.Tech / MCA