Introduction
Each column in a SQL Server Table can only contain one specific predefined type of data, such as characters or numbers. This declaration is called a Data Type. In this article, we’ll compare and contrast the various SQL Server 2008 Data Types. In addition, we’ll explore which Data Types are the best solutions for specific situations. There are over thirty-five different Data Types in SQL Server 2008.
Categories
Microsoft classifies the various Data Types into the following seven broader categories: Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
Numbers
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. The other is to ensure your consuming application receives only the data size it expects so buffer overruns are avoided.
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.
Strings
This section will begin with a discussion about Character Stings: 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. Think of a Char as holding anything you can type into Notepad–and just like Notepad, if you enter numbers into a Char column, math cannot be performed on them. They are treated as text characters, not numbers. Typically, Char columns are used when the numbers of characters entered into each row are approximately the same, such as a zip code or phone number. 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. In the following example, a Table was created with one Char column and one VarChar column. Both were set to a maximum size of 50.
Identical test data was entered into each.
The following TSQL returns the amount of physical disk byte space used by each column and each row.
SELECT DATALENGTH(ColChar) AS CharSize, DATALENGTH(ColVarChar) AS VarSize
FROM table1
So the Char column uses 50 bytes per entry regardless of how small or large the data entered is.
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. It’s currently included in SQL Server 2008 for backwards compatibility only and will be discontinued at some future date.
Unicode
Unicode is a standard method that allows applications to record characters from languages other than our own. Unicode comes in handy when you’re building multilingual applications or international web sites. The Char and VarChar data types can both be configured to allow Unicode by prefacing them with the letter ‘n’, as in nChar and nVarChar. The price of this flexibility is increased disk space usage. As a ballpark figure, plan on Unicode to be double in disk space. Full details of Unicode can be found on the Unicode Consortium home page: http://unicode.org/ .
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. There is also a legacy data type called Image, but it will be discontinued in a future version of SQL Server.
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.
Dates and Times
SQL Server 2008 includes new date and time Data Types. These can be reviewed in the Database Journal article “SQL 2008 Date Types” located at https://www.databasejournal.com/ms-sql/sql-2008-date-types/ .
Conclusion
There are over thirty-five different data types in SQL Server 2008. If your application needs to compare or manage columns that are different Data Types, the functions Cast and Convert can be used. For example a Char column that contains numbers can be converted into an Int and then have math preformed. For a description of Cast and Convert, see the Database Journal article at: https://www.databasejournal.com/ms-sql/sql-2008-date-types/ .
