Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Cheat Sheet

Microsoft SQL Server 2008 For Dummies

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 -253 to 253-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 231–1 characters Actual string length +2 bytes
nvarchar(n) Stores approximately n characters 2n(actual string length) +2 bytes (where n is in the range of 1–4,000)
nvarchar(max) Stores up to ((231–1)/2)–2 characters 2n(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 231–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
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.