Data types in SQL Server - SQL Server Tutorial

When you're defining your columns in SQL Server Management Studio, you will find that the data type for each column presents you with a rather large set of choices of things that you could possibly use here and it can be a little intimidating if you are new to SQL Server or you're not quite sure exactly which one you should be picking. So let's explore what some of those options are, at least what the most common ones that you want to know about. The data types are split really in two categories. Is it character or text data? Is it a number? Is it a date? What actually is this? And then we can get a little bit more specific about each of those pieces. Let's start off with character or text data. Well, even when you are looking at this, you might be a little annoyed by the fact that you have six character data types: text, ntext, char, nchar, varchar, and nvarchar. And you might think, what, I am supposed to make six decisions for every single bit of text that I have? Well, actually it's a little simpler than that because straight off the bat we can lose two of these. Even though text and ntext will show up in that drop-down data type box, you should not use them. These are old school and they're officially deprecated in this version of SQL Server, meaning they'll actually be removed from a future version. They are only there for backwards compatibility. So we're left with four and we can choose between these four simply by asking two questions. One, is the data we are going to store Unicode or non-Unicode? And two, is it a fixed length or a variable length? If we need Unicode data, that's if you support or ever expect that you might possibly support international language character sets, you need to pick Unicode. And those are the two character data types with the letter n at the front of them, nchar and nvarchar. If you know that your text is only ever going to be plain old Western European character set, pick non- Unicode which is char and varchar without the n at the front. The impact is that if you pick Unicode, because Unicode can hold a much bigger character set, many, many different international languages, that takes up 2 bytes per character in SQL Server. If it's not Unicode, it's a char or varchar, it's 1 byte per character. Now officially, SQL Server 2008 R2 has something called Unicode compressions. So if you are really worried about the space, you can look into that. Now, I'm just a fan of using Unicode by default all the time. So I'll pretty much always pick the one with the n in front. But the next question that I have to ask is, is this fixed length or is it variable length? And that's simply allows you to choose. If it's fixed length, it's nchar or char. If it's variable length, it's the one with the var inside it. And of course, that's going to be up to you. What is it that you're storing? If it's product ID, for example, you might know that it's always going to be 10 characters long. If it's the contents of a blog entry, well, that's going to be a bit more variable. So you just answer these two questions, Unicode or non-Unicode, fixed length or variable length, and that will take you to the character data type that you need to pick. Once you've chosen that, you need to ask a couple of other questions. See if it's a fixed length character data like nchar or char, well, you need to then predict the amount of space that's going to take. If it's nchar(10), meaning it's a length of 10, that will take up to 20 bytes and even if you only fill 3 characters of it, it will pad it with spaces. If it's just char of 10, it will be 10 bytes. SQL Server likes to know the size of it so it can make its own optimization about how much space this is going to take up in the system. But even if you're picking variable length character data, you need to know the impact this is going to have. If you specify an nvarchar with a length of 10 characters, well, the length of it is flexible. It depends. The maximum of this will be 20 bytes but if you're only storing one or two characters, it should only take up the space for one or two characters. Well, one of the questions might be how long can this get? Well, if you're working with Unicode, the maximum length you can have of either an nchar and nvarchar is 4000 and that's 4000 characters. Again, because it will take, by default, 2 bytes per character. That's up to 8000 bytes here. Now because 8000 is allowed, if you are just working with the non-Unicode version, your maximum is 8000. 8000 for a varchar or a char is the maximum specified length. Well, you might ask what happens if it's bigger than that? Or if it ever could be more than 8000 bytes out of 4000 Unicode characters or 8000 regular ASCII characters? Then you have one other choice. You just use the word max or varcharmax. That allows you up to 2 GB per row per column. So you can store some pretty large stuff in here. You just can't specify a length any larger than 4000 for Unicode and 8000 for non-Unicode. Once you get beyond that, it's max. Next up, we have numeric data types. Let's start with the exact numbers. We have the classic int or integers, storing whole numbers from roughly -2 billion to +2 billion. Nothing after the decimal point; they are always whole numbers. If you don't need that much space, you can have what's called a smallint, which is roughly -32 thousand to +32 thousand. You've got a tinyint, which is 0 to 255. If you know that you have a very small range. But what if you want more than 2 billion? Well, you do have bigint as well, which be +/-2^63 so that should hold pretty much whatever you can come up with. We also have money and smallmoney. These are exact numbers even though they have got numbers after the decimal point. It's a fixed length. Both the money and the smallmoney data types have four digits after the decimal point. Money can hold up to, as you can see here, that's a fairly large amount, whereas small money is +/-214,748 and then four digits after the decimal point. Formerly with your exact numbers, you also have decimal which is a fixed precision and scale. I will explain what that means in a second. Now you'll also see something called numeric. Decimal and numeric are exactly the same. Again, the two terms are here for kind of backwards compatibility. I pick decimal all the time. It doesn't really matter. Just pick one and stick with it. What precision and scale means is as you define two numbers. The p represents the precision, the total amount of digits being stored in this column. And ,s for the scale is the amount of digits after the decimal point. So for example, creating decimal with 9,2 means there is nine digits in total and two after the decimal point. So the maximum value would be seven 9s before the point and two 9s afterwards. So that's decimal and numeric. You do have approximate or floating point numbers. You have got float where you say exactly what the floating point should be and then you have something called real. I would suggest that if you're getting into the floating point numbers and you need to be aware of those, we will look them up on Books Online for more specific information. I will show that in just a second. You do have several date and time data types. We have one that just holds a date. We have one that just holds a time. There is one that's called datetime. That's been around for quite a while. It holds not only the date but the time as well. Although SQL Server has something called datetime2, and that is the one that's recommended. Even if it looks kind of an ugly data type, that's the one you should use if you need to store both dates and times because it to be more accurate, down to about 100 nanoseconds, and it doesn't take as much space as datetime does. If you're working, however, with international time zones and you need to store your time offset from Universal Time Code, there is also the datetimeoffset, which takes that into account. So you could look at that one and if you need on the other side a bit less precision, you can also look at smalldatetime. But I'd say the main ones here should be reasonably obvious, which is you just need a date, use date. You just need a time, use time. You need both, use datetime2. As you start to work with your data types, you will realize that they are not trying to give you several possible options for the same need. They really all do have pretty specific needs to them. There are several other data types. We have binary and varbinary. As you might guess from looking the char and varchar, this really means a fixed length and a variable length binary field for storing things like file attachments. That can grow as big as 2 GB, which you will find is the fairly magic number for large column sizes in SQL Server. There is bit which is basically a field that can be true or false. You have got XML. You can store raw XML data, even though it is text, and you can think surely I could just put it in a text field. You could, but the XML column has the ability for SQL Server to understand the XML and even parse the XML if needed. There is a unique identifier, which would create a 16 digit unique ID, what's often referred to as a GUID, a Globally Unique Identifier, and you can even define your own data types, though we are not going to do this in this course. There is a couple of other rare ones that rounded up. If you start looking through the drop-down box, you will see things like geometry and geography for ellipses and paths and even for latitude and longitude. And there is a few other deprecated ones like timestamp and image not to be used. Here is the real deal. Until this all becomes second nature, what you want to be doing is making sure that you're using SQL Server Books Online and use that as your little quick reference for these. Let me show you the easiest way to start working with that. If I open up my SQL Server Books Online, what I'm going to do is I want to find my data type definitions. Well, I can just type in the word in my index here. So I'm looking in my index, the second tab, and I am saying data types. Now I select data types and I have all sorts of things. It says data types here and blanks and currency and all sorts of stuff but unfortunately I am picking the wrong ones, because SQL Server Books Online is showing me a bit too much information because I got data types in PowerPivot for Excel and data types in CLR integration and data types ODBC. What I'm going to do is filter this down a little bit. Tather than have the unfiltered showing me every possibility, I'm going to say to say give me the SQL Server 2008 Database Engine and let me try that data types again. Well we have a few still here. Data types CLR, data types ODBC, data types OLE DB. Data types SQL Server is what I'm looking for and if you are thinking, well, how do I know for sure? Well, unless, you know that you're going for OLE DB or SMO or SQL Server application, this one is a pretty good bet. And if I highlight that, I can see that it starts talking about the related data types. So I will come down in this page. Let me delete that a little bit. We've got the data type categories, we have got the bigints, and bits, and decimals, and ints. I could jump into any of these pieces such as say smallmoney and it will tell me exactly what that range is for smallmoney. So this looks like a pretty useful page to have for my data types as I am getting used to them. And in fact what I'm going to do is go up here onto my menu bar and just click Add to Help Favorites, hit that button, and what that means is that from now on if I want to just go ahead and remind myself of the data types, I jump to my Help Favorites tab in Books Online. I hit that and it will allow me to drop through all the different data types. The other ones, the binaries, the Unicode, character, date and time, all of it.