Class 2 - Data Types

DATA TYPES :
Exact Numerics
bigint
decimal
int
numeric
smallint
money
tinyint
smallmoney
bit

Approximate Numerics
float
real

Date and Time
datetime
smalldatetime

Character Strings
char
text
varchar

Unicode Character Strings
nchar
ntext
nvarchar

Binary Strings
binary
image
varbinary

Other Data Types
cursor
timestamp
sql_variant
uniqueidentifier
table
xml
See Also
New Datatypes :
Varchar(max)
Nvarchar(max)
Varbinary(max)
xml

Exact-number data types that use integer data.
Data type Range Storage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte

decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale.
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
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; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision. Precision Storage bytes 1 – 9 5 10-19 9 20-28 13 29-38 17 money and smallmoney (Transact-SQL) Data types that represent monetary or currency values. Data type Range Storage 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 bit (Transact-SQL) An integer data type that can take a value of 1, 0, or NULL. float and real (Transact-SQL) 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. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Date and Time (Transact-SQL) Are data types that are used for representing the date and the time of day. datetime and smalldatetime Represent the date and the time of day. 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 char and varchar (Transact-SQL) Are character data types of either fixed length or variable length. 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. The SQL-2003 synonym for char is character. 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. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying. nchar and nvarchar (Transact-SQL) Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set. 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. The SQL-2003 synonyms for nchar are national char and national character. 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. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying. ntext, text, and image (Transact-SQL) Important: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-2003 synonym for ntext is national text.

text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

binary and varbinary (Transact-SQL)
Binary data types of either fixed length or variable length.
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. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.

cursor (Transact-SQL)
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.
The operations that can reference variables and parameters having a cursor data type are:
The DECLARE @local_variable and SET @local_variable statements.
The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
Stored procedure output parameters.
The CURSOR_STATUS function.
The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures.

Sql_variant (Transact-SQL)
A data type that stores values of various SQL Server 2005-supported data types, except text, ntext, image, timestamp, and sql_variant.
uniqueidentifier (Transact-SQL)
Is a 16-byte GUID.
Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
By using the NEWID function.
By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

xml (Transact-SQL)
Updated: 5 December 2005
Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. For more information, see xml Data Type.
Select * from systypes