Every column in your table must have a "data type," which is simply a property that defines what type of data is stored in that column. In addition, a data type will reject data that is not of the correct type (i.e. attempting to store a letter in a data type designed for numbers). SQL Server has over 25 different data types -- some with more options than others. Let's look at the different data types and the options for each:

 Data Type Size Description Integers bigint 8 bytes Holds integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). int 4 bytes Holds integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). smallint 2 bytes Holds integer data from 2^15 (-32,768) through 2^15 - 1 (32,767). tinyint 1 byte Holds integer data from 0 through 255. bit 1 byte for up to 8 bit columns Holds integer data with either a 1 or 0 value. Each set of up to 8 bit columns requires 1 byte. So if there are anywhere from 1 to 8 bit columns in a table, the storage space you will need is 1 byte. If there are anywhere from 9 to 16 bit columns in a table, the storage space you will need is 2 bytes. And so on... Decimal decimal Anywhere from 5 to 17 bytes depending on the precision Holds fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The Precision specifies 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 38. The Scale specifies 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 Precision. Examples: if precision is set to 10 and scale is set to 3 the smallest (other than 0)/ largest number we could store would be 0.001 / 9999999.999 if precision is set to 8 and scale is set to 6 the smallest (other than 0)/ largest number we could store would be 0.000001 / 99.999999 numeric - Same as decimal data type Money money 8 bytes Holds monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with accuracy to one ten-thousandth of a monetary unit smallmoney 4 bytes Holds monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Approximate float(n) Anywhere from 4 to 8 bytes depending on the precision Holds floating precision number data from -1.79E + 308 through 1.79E + 308. The value n is the number of bits used to store the mantissa of the float number and can range from 1 to 53 real 4 bytes Holds floating precision number data from -3.40E + 38 through 3.40E + 38. Real is the same as float(24). Date and Time datetime 8 bytes Holds date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds. smalldatetime 4 bytes Date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute. Strings - non-Unicode char(n) n bytes Holds fixed-length non-Unicode character data with length of n characters, where n is a value from 1 through 8000. If less than n number of characters are entered n bytes are still required because unused character spaces get padded to the end to make them n bytes long. varchar(n) Depends on the length of data entered - 1 byte per character Holds variable-length non-Unicode character data with a length of n characters, where n is a value from 1 through 8000. The storage size is the actual length in bytes of the data entered, not n bytes. text 16 bytes for the pointer Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters. A text column entry can hold up to 2^31 - 1 characters. It is a pointer to the location of the data value, the data is stored separately from the table data. Strings - Unicode nchar(n) 2 bytes * n Holds fixed-length Unicode character data of n characters, where n is a value from 1 through 4000. Unicode characters use 2 bytes per character and can support all international characters. If less than n number of characters are entered n bytes are still required because unused character spaces get padded to the end to make them n bytes long. nvarcher(n) Depends on the length of data entered - 2 byte per character Holds variable-length Unicode data of n characters, where n is a value from 1 through 4000. Unicode characters use 2 bytes per character and can support all international characters. The storage size is the actual length in bytes * 2 of the data entered, not n bytes. ntext 16 bytes for the pointer Holds variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. The column entry for ntext is a pointer to the location of the data. The data is stored separately from the table data Binary binary(n) n + 4 bytes Holds fixed-length binary data of n bytes, where n is a value from 1 through 8000. Use binary when column data entries are consistent in size. varbinary(n) Depends on the length of data entered + 4 bytes Holds variable-length binary data of n bytes, where n is a value from 1 through 8000. Use varbinary when column data entries are inconsistent in size. image 16 bytes for the pointer Used for variable-length binary data longer than 8000 bytes, with a maximum of 2^31 - 1 bytes. An image column entry is a pointer to the location of the image data value. The data is stored separately from the table data Other sql_variant size varies A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values. The only types of values that cannot be stored using sql_variant are text, ntext, image, timestamp, and sql_variant. timestamp 8 bytes Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. Each table can have only one timestamp column. uniqueidentifier 16 bytes Stores a 16-byte binary value that is a globally unique identifier (GUID).

Page 3: Data Types (Continued)