SQL Server Reference Guide

In previous articles I've explained what tables are, the physical implementations and limitations of tables, and constraints used to ensure the data that the tables contain. In this article, I'll complete the study of tables by exploring data types. I won’t cover them all, because you won’t use all of them (at first, anyway) and because they change based on which version and even sometimes which edition you’re using. But I will cover the major types that you’ll see used most often.

Data types are the kinds of data you can store in a column of a SQL Server table. When you create a table with the CREATE TABLE command, you specify the columns and the type of data they can contain. Often, the data type will require more information in addition to what it is, such as how long or what precision the data will store. Here’s a sample statement:

CREATE TABLE test
(
FirstName VARCHAR(30)
)

You can see that I have the CREATE TABLE statement, followed by a parenthesis, then the name of a column (Firstname) and then its data type (VARCHAR). I’ll explain that data type in a moment – for now, focus on that format. In the case of VARCHAR, (which stands for Variable Characters) there is more information needed. The information VARCHAR wants is how long the string of characters can be.

You might wonder why you have to specify a data type. After all, what you and I normally see on the screen is just text. But the computer, of course, doesn’t store letters or any other symbol – it just stores a numeric representation of those characters. Defining exactly what you’re storing, whether that’s textual characters or numbers or ranges of dates allows SQL Server to define the space it needs more efficiently. Not only that, whenever you store a number or date, SQL Server can perform math very quickly if it knows that the data you’re sending won’t be a character.

So you need to specify the data type for SQL Server to optimize speed and storage. But that isn’t the only reason. Specifying the type of data a field can hold also helps to ensure the data you store for your organization is what they expect. It’s part of the integrity of the system. By not allowing a character to be stored as the price of an item, you ensure that the math used to calculate profit is accurate, and so on.

In yet another case, by specifying the data type you can even allow the field to hold specialized data such as Microsoft Word documents or pictures.

The use and meaning of some data types are fairly obvious. For instance, when someone asks you how much money you have in your pocket, you don't answer with a date.

You could probably guess a few of the kinds of data a database system might store, such as text strings, numbers, dates and so forth. But even within the number data type, there are times that a money data type might make more sense to use than a general number. These distinctions have to do with how much storage space is needed for the data and how fast the data is accessed.

A general number data type has to leave room in its storage space to hold any possible numeric value from 10^38 +1 through 10^38 -1. That's a lot of space! Now, you might be thinking that with today's hard drive prices, that space requirement shouldn't cause a problem. While that may be true, the storage of the number 1 in that space wastes valuable real estate. It also makes the search engine work harder to locate data.

The rest of this article will focus on what data types are available in SQL Server and how you can use them. As I mentioned, I won’t cover each and every one. For that, you can refer to the official Books Online page for each version:

SQL Server 2000 Data Types

SQL Server 2005 Data Types

SQL Server 2008 Data Types

I’ll explain the data types you’ll see most often. I’ll group them by kind of data that they store to keep them together. I won’t be as specific as the references above, since I’ll try and stay as version-generic as I can. If I specify a data type that is only available in a certain version, I’ll point that out.

Number Data Types

The first category of data stored in SQL Server I’ll explain are those that work with numbers. Numbers come in multiple types, depending largely on the sign of the number you want to work with (such as positive or negative) and the length of numbers you want to store and with what precision.

Precision is important to understand – it’s how many digits you want to store to the right of the decimal place in a number. Sometimes you don’t need any of that, such as when a unit of inventory you are recording doesn’t come in parts, only in whole numbers.

The Integer family: int, bigint, smallint, tinyint

An integer is a whole number between the negatives and positives. In other words, you don't want to set this type for anything that has a decimal.

The int (or more properly, integer) data types store integers of various sizes – the largest (bigint) being around + or - 9,223,372,036,854,775,808 and the smallest (tinyint) being 0 through 255 (check the links above for the exact numbers and ranges). The reason there are so many is that this arrangement allows you to choose exactly what you need – no more, no less.

Decimal and Numeric

The decimal or numeric data type store numbers with a decimal place. When you use this data type you specify the precision (how many numbers total) and scale (how many numbers to the right of the decimal). Here’s a sample:

CREATE TABLE TableName (numericolumn numeric(18, 4))

Money, Smallmoney

These are pretty obvious – they store decimal numbers which are normally represented as two decimal places. You might wonder why not just use decimal for money and be done with it? The reason is that storage space again, and the fact that the system will round the numbers for you automatically during calculations.

The decimal type enforces the precision and scale, and the money types are for the more common two-place decimal numbers. The smaller money type stores the type of money often stored in transaction tables.

Float, Real

These data types store floating precision number data of various lengths. These types of numbers are less precise than other numeric types.

Note that floating values are not easy to represent in a binary computer, so you’ll need to read and understand this data type a little more before you implement it.

Text and Character Data Types

One of the most common data types you’ll store in a database is text. For SQL Server, working with a single letter and working with a group of sentences is the same – it’s just the length that the system cares about.

Char, Nchar, Varchar, Nvarchar

The CHAR data type is stores fixed-length character data. You specify the size when you create the column, and if you don't use all the space when you add data in it, SQL Server will "pad" the length out to the end. You saw an example of the format this type uses at the top of this article.

The VARCHAR type doesn't waste that space; it only stores what you type.

The NCHAR and NVARCHAR types (as with all the N-prefixed data types) are used for Unicode data. What this does is halve the amount of data that can be stored, but what you get for that is and extra two-bytes for each character. Why is that useful? You can use that extra size to store more than one representation of a character – say English or Turkish. That way, your data can be used by two systems in different languages.

Text, Ntext

The TEXT data type is often used for storing what might be called "memo" type data. Actually, this data type can be less efficient than other character types, so unless you need it, don't use it. If you have a lot of data to enter into a field, you may want to investigate pointing a file location on the hard drive with a text file or word-processing document. It all depends on how much you need to store and how it should be formatted. In another article, I'll show you how to do that.

Beginning in SQL Server 2008, you can use the new VARCHAR(MAX) data type, which allows 2^31-1 bytes of storage.

Date and Time Data Types

One of the most powerful data types stores date and time values. The reason they are so powerful is that combine with various functions and procedures in Transact-SQL to provide all kinds of calculations and formulas. For instance, you can store only the date and time of someone’s birth, and use a simple formula to determine how old someone is, up to the second.

Datetime, Smalldatetime

The DATETIME value stores both date and time in a single value. Interestingly, it isn’t the actual date that SQL Server stores – it’s really just a serial number that the system uses to interpret dates, based on what the client sends or reads – a very important distinction. You can really get tripped up if you don't keep in mind what the client is. If you're using a Web front end, experiment with various time zones to see if the client enters the data or the server does it on their behalf.

Even though you may only care about the hour or minute a Datetime column holds, the system stores that entire serial number. You can format the data a bunch of different ways when you need it to get the year, day, week, month, hour, minute or second. I’ll explain those in more detail later.

Beginning in SQL Server 2008, you have two more options – DATE and TIME. As you might imagine, they store the date separate from the time. With the TIME type, you get seven levels of precision – something that comes in handy on

Timestamp

Actually, this data type doesn’t store time – it is used to enter a unique value in one column, when you want a number that indicates when the data was updated. It's a great tracking tool, but don't be fooled by the name. You can't select this column type out and get a pretty date or time format. It's just a unique sequencing number. I include it here because most people are looking for it in the datetime data types!

Other Data Types

Some data doesn’t fit as either numbers or text. To help you store “non-standard” data, SQL Server includes lots of other data types. I’m only covering a few of them here.

Binary, Varbinary

Binary data can be used to store most any computer data. You have to chunk it up into 8000 byte, well, bites. You can use this type to store anything from word-processing documents all the way to graphics – although there's another type to handle that specific case.

With binary, you specify how large the column is, and with varbinary, you specify how big it can be.

Image

The IMAGE data type like the binary type, but it's specific to graphics. It’s been replaced in SQL Server 2008 with VARBINARY.

sql_variant

This is a data type that stores values of various data types, but I don't suggest you use it a lot. It makes for messy conversions and exports. You especially shouldn't use it for numeric or date types. It’s normally only used when you don’t know what kind of data might be stored in the cell.

Uniqueidentifier

This data type creates a globally unique number used as an identifier. I don't trust it; I just feel there are better ways to generate a unique number. I'll reference some sites a bit later that will explain that reasoning better than I have here.

bit

The bit data type is useful if you need to store a checkbox value or a yes or no answer. As you may already know, a bit stores either a 1 or 0 value, so you can also use this data type to store any Boolean result. This data type uses very little space, and can be sorted very quickly.

You can't set this data type to null, and it can't be indexed. Because the bit data type is so small, it obviously isn't a good candidate for a primary key!

The User Defined Data Type (UDDT)

The UDDT isn't really a new kind of data, it's just a compilation of the other types that you cobble together with the sp_addtype stored procedure. It lets you name a type, say to create a social security or phone number with a specific length or precision.

InformIT Articles and Sample Chapters

There’s a section in the article SQL Building Blocks and Server Settings that discusses the conversions of data from one type to another, and you should pay special attention to “implicit” conversions.

Books and eBooks

This work also covers data types: Sams Teach Yourself SQL in One Hour a Day, by Ryan Stephens, Ron Plew, Arie D. Jones.

Online Resources

Although I didn’t cover the material for Common Language Runtime (CLR) here, you can actually use it to make your own complex data types. More on that here.