Microsoft SQL Server 2008 For Dummies

From Microsoft SQL Server 2008 For Dummies by Mike Chapple

When designing your database in Microsoft SQL Server 2008, your main task is selecting the appropriate data types for each of the attributes in your database. Check out the various numeric data types and choose the appropriate type that tells SQL Server how to interpret the data stored in each column.

Data Types Found in SQL Server 2008

The following charts show the data types supported by Microsoft SQL Server 2008. Numeric data types, including types capable of storing both integers and decimal numbers, save information used in mathematical computations in SQL Server. Also included in SQL Server 2008 are data types specifically designed for storage of date and time, text in a Microsoft SQL Server database (character string data), any type of data represented in binary form, and other data types for special tasks, such as storing whole xml documents.

Numeric Data Types
Data Type	Description	Length
int	Stores integer values ranging from -2,147,483,648 to 2,147,483,647	4 bytes
tinyint	Stores integer values ranging from 0 to 255	1 byte
smallint	Stores integer values ranging from -32,768 to 32,767	2 bytes
bigint	Stores integer values ranging from -2⁵³ to 2⁵³-1	8 bytes
money	Stores monetary values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	Stores monetary values ranging from -214,748.3648 to 214,748.3647	4 bytes
decimal(p,s)	Stores decimal values of precision p and scale s. The maximum precision is 38 digits	5–17 bytes
numeric(p,s)	Functionally equivalent to decimal	5–17 bytes
float(n)	Stores floating point values with precision of 7 digits (when n=24) or 15 digits (when n=53)	4 bytes (when n=24) or 8 bytes (when n=53)
real	Functionally equivalent to float(24)	4 bytes

Date and Time Data Types
Data Type	Description	Length	Example
date	Stores dates between January 1, 0001, and December 31, 9999	3 bytes	2008-01-15
datetime	Stores dates and times between January 1, 1753, and December 31, 9999, with an accuracy of 3.33 milliseconds	8 bytes	2008-01-15 09:42:16.142
datetime2	Stores date and times between January 1, 0001, and December 31, 9999, with an accuracy of 100 nanoseconds	6–8 bytes	2008-01-15 09:42:16.1420221
datetimeoffset	Stores date and times with the same precision as datetime2 and also includes an offset from Universal Time Coordinated (UTC) (also known as Greenwich Mean Time)	8-10 bytes	2008-01-15 09:42:16.1420221 +05:00
smalldatetime	Stores dates and times between January 1, 1900, and June 6, 2079, with an accuracy of 1 minute (the seconds are always listed as “:00”)	4 bytes	2008-01-15 09:42:00
time	Stores times with an accuracy of 100 nanoseconds	3–5 bytes	09:42:16.1420221

Character String Data Types
Data Type	Description	Length
char(n)	Stores n characters	n bytes (where n is in the range of 1–8,000)
nchar(n)	Stores n Unicode characters	2n bytes (where n is in the range of 1–4,000)
varchar(n)	Stores approximately n characters	Actual string length +2 bytes (where n is in the range of 1–8,000)
varchar(max)	Stores up to 2³¹–1 characters	Actual string length +2 bytes
nvarchar(n)	Stores approximately n characters	2ⁿ(actual string length) +2 bytes (where n is in the range of 1–4,000)
nvarchar(max)	Stores up to ((2³¹–1)/2)–2 characters	2ⁿ(actual string length) +2 bytes

Binary Data Types
Data Type	Description	Length
bit	Stores a single bit of data	1 byte per 8 bit columns in a table
binary(n)	Stores n bytes of binary data	n bytes (where n is in the range of 1–8,000)
varbinary(n)	Stores approximately n bytes of binary data	Actual length +2 bytes (where n is in the range of 1–8,000)
varbinary(max)	Stores up to 2³¹–1 bytes of binary data	Actual length +2 bytes

Other Data Types
Data Type	Description	Length
cursor	Stores a reference to a cursor	N/A (cannot be used in a table)
sql_variant	May store any data type other than sql_variant, text, ntext, image, and timestamp	Up to 8,000 bytes
table	Stores a temporary table (such as a query result)	N/A (cannot be used in a table)
rowversion	Stores a value of the database time (a relative number that increments each time you insert or update data in a database. It is not related to calendar/clock time)	8 bytes
uniqueidentifier	Stores a globally unique identifier	2 bytes
xml	Stores formatted XML documents	Up to 2GB