Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 5, 2008

Data Types in SQL Server 2008

By Don Schlichting

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.

table with one Char column and one VarChar column

Identical test data was entered into each.

Identical test data was entered into each table

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

the amount of physical disk byte space used by each column and each row

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 http://www.databasejournal.com/features/mssql/article.php/3707856/SQL-2008-Date-Types.htm .

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: http://www.databasejournal.com/features/mssql/article.php/3707856/SQL-2008-Date-Types.htm .

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date