There are over thirty-five different Data Types in SQL Server 2008.
Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
There are two categories of numbers, Exact Numbers and Approximate Numbers.
Approximate number include the types Real and Float. In general terms, think of Approximate Numbers to be used when Scientific Notation is needed. Scientific Notation is a way to describe very large or very small numbers using powers of ten (also called Exponential Notation).
Exact Numbers include Decimals, Integers, and Money amounts.
An Integer is a counting number with no decimal point or fractional piece. All negative numbers, positive numbers, and zero are integers. SQL Server breaks integers into four sizes:
BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Int: -2,147,483,648 to 2,147,483,648
SmallInt: -32,768 to 32,767
TinyInt: 0 to 255
There are two primary reasons for trying to “right size” the Int type you select rather than just setting everything as a BigInt. The first is physical disk space. A BigInt takes up eight byes per row while a regular Int only uses two.
Exact Numbers with decimal places include the Data Types Decimal, Numeric, Money, and SmallMoney. The types Decimal and Numeric are functionally the same thing. Meaning, they will work, calculate and behave identically, the only difference is in the mathematical definition and not in the way SQL Server utilizes them. Most SQL Server applications I run into use Decimal. A Decimal can be up to 38 digits in length. When the Decimal is defined, its total length and maximum number of decimal places to the right are configured. The larger the number of digits defined the more physical disk space used on each row.
Money and SmallMoney are really Decimals with a fixed amount of four decimal places to the right. SmallMoney can be valued from - 214,748.3648 to 214,748.3647 while the range of Money is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. One of the reasons for using Money rather than a Decimal includes the display option of dollar signs and commas after three digits.
Binary Stings
Binary stings are used for saving non-character data, such as images and audio. There are two data types used for this: Binary and VarBinary. They configure like Char and VarChar. VarBinary(Max) can be used to store files of unlimited size.
Strings
Char, VarChar, and Text. A Char (Character) Data Type can hold letters, numbers, and keyboard characters. When a Char is defined, its max length, up to 8,000, is also fixed. If the length of the data will vary from row to row, such as an email address, then use VarChar. A VarChar is a Char of variable (Var) length. When a VarChar is created, its max length is also defined. A main reason to use a VarChar rather than a Char is the amount of physical disk space used by each.Another useful feature of a VarChar is the ability to specify an unlimited maximum size. This is done with the “Max” key word, as in VarChar(Max). Max means the size may exceed 8,000 bytes. In addition, the size is unlimited.
The last Character String is the Data Type Text. Text was similar to a VarChar(Max) column.
Other Data Types
There are seven Other Data Types including Cursor, HierachyID, SQL Variant, Table, TimeStamp, UniqueIdentifier, and XML. The data type TimeStamp has been replaced by RowVersion. UniqueIdentifier is a unique GUID. The data type SQL Variant can be used when you don’t know what type of data to expect. It’s basically 8,000 bytes of anything goes storage. If you’re using XML, use the actual XML data type rather than a VarChar. The XML type allows binding to collections.
