Data Types in sql server
In a Database, 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, data and time data, binary strings, and so
on.
Integer Types: To hold the
Integer values it provides with tinyint, smallint, int and bigint
data types with sizes 1, 2, 4 and 8 bytes respectively.
Boolean Type: To hold the
Boolean values it provides with bit data type that can take a value of
1, 0, or NULL.
Note: The string values TRUE and
FALSE can be converted to bit values: TRUE is converted to 1 and FALSE
is converted to 0.
Decimal Types: To hold the
decimal values it provides with the following types:
-decimal[
(p[ , s] )] and numeric[ (p[
, s] )]
p (precision)
The
maximum total number of decimal digits that can be stored, both to the left and
to the right of the decimal point. The precision must be a value from 1 through
the maximum precision of 38. The default precision is 18.
s (scale)
The
maximum number of decimal digits that can be stored to the right of the decimal
point. Scale must be a value from 0 through p. Scale can be specified
only if precision is specified. The default scale is 0.
Storage
sizes of Decimal and Numeric types vary, based on the precision.
Precision
|
Storage bytes
|
1 – 9
|
5
|
10-19
|
9
|
20-28
|
13
|
29-38
|
17
|
Note:
numeric is functionally equivalent to decimal.
-float [ ( n ) ] 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.
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.
n value
|
Precision
|
Storage size
|
1-24
|
7 digits
|
4 bytes
|
25-53
|
15 digits
|
8 bytes
|
Monetary or Currency Types: To hold the Currency values it
provides with the following types which takes a scale of 4 by default:
money
|
-922,337,203,685,477.5808 to
922,337,203,685,477.5807
|
8 bytes
|
smallmoney
|
- 214,748.3648 to 214,748.3647
|
4 bytes
|
Date and Time Values: To hold the Date and Time values
of a day it provides with the following types:
Data type
|
Range
|
Accuracy
|
datetime
|
January 1, 1753, through December 31, 9999
|
3.33 milliseconds
|
smalldatetime
|
January 1, 1900, through June 6, 2079
|
1 minute
|
Values
with the datetime data type are stored internally by the Microsoft SQL
Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the
number of days before or after the base date: January 1, 1900. The base
date is the system reference date. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight.
The
smalldatetime data type stores dates and times of day with less
precision than datetime. The Database Engine stores smalldatetime
values as two 2-byte integers. The first 2 bytes store the number of days after
January 1, 1900. The other 2 bytes store the number of minutes since midnight.
String
Values: To hold
the string values it provides with the following types:
char [ ( n ) ]
Fixed-length,
non-Unicode character data with a length of n bytes. n must be a
value from 1 through 8,000. The storage size is n bytes.
varchar [ ( n | max
) ]
Variable-length,
non-Unicode character data. n can be a value from 1 through 8,000. max
indicates that the maximum storage size is 2^31-1 bytes. The storage size is
the actual length of data entered + 2 bytes.
text
It
was equal to varchar(max) this data type will be removed in a future version of
Microsoft SQL Server. Avoid using these data types in new development work use
varchar(max) instead.
Unicode Data types for storing Multilingual Characters are
nchar, nvarchar and ntext where n stands for national.
nchar [ ( n ) ]
Fixed-length
Unicode character data of n characters. n must be a value from 1
through 4,000. The storage size is two times n bytes.
nvarchar [ ( n | max
) ]
Variable-length
Unicode character data. n can be a value from 1 through 4,000. max
indicates that the maximum storage size is 2^31-1 bytes. The storage size, in
bytes, is two times the number of characters entered + 2 bytes.
ntext
It
was equal to nvarchar(max) this data type will be removed in a future version
of Microsoft SQL Server. Avoid using these data types in new development work
use nvarchar(max) instead.
Binary Values: To hold the binary values likes
images, audio clips and video clips we use the following types.
binary [ ( n ) ]
Fixed-length
binary data with a length of n bytes, where n is a value from 1
through 8,000. The storage size is n bytes.
varbinary [ ( n | max)
]
Variable-length
binary data. n can be a value from 1 through 8,000. max indicates
that the maximum storage size is 2^31-1 bytes. The storage size is the actual
length of the data entered + 2 bytes.
Image
It
was equal to varbinary(max) this data type will be removed in a future version
of Microsoft SQL Server. Avoid using these data types in new development work
use varbinary(max) instead.
- Use char,
nchar, binary when the sizes of the column data entries are
consistent.
- Use varchar,
nvarchar, varbinary when the sizes of the column data entries vary
considerably.
- Use varchar(max),
nvarchar(max), varbinary(max) when the sizes of the column data
entries vary considerably, and the size might exceed 8,000 bytes.
Other Types: Apart from the above it provides
some additional types like -
timestamp: Is a data type that exposes
automatically generated, unique binary numbers within a database. The storage
size is 8 bytes. You can use the timestamp column of a row to easily
determine whether any value in the row has changed since the last time it was
read. If any change is made to the row, the timestamp value is updated. If no
change is made to the row, the timestamp value is the same as when it was
previously read.
Uniqueidentifier: Is a 16-byte GUID which is
initialized by using the newid() function or converting a string constant in
the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee
that rows are uniquely identified across multiple copies of the table.
Xml: Is the data type that stores XML
data. You can store xml instances in a column, or a variable of xml
type. The stored representation of xml data type instances cannot exceed
2 gigabytes (GB) in size.
