I've seen, time and time again, programmers make many of the same mistakes regarding their SQL datatypes, and one of them is to use
VarChar for almost everything. I've seen it so many times that if I had a nickel for each time I saw it, well, let's just say my McLaren P1 would be yellow.
Why do people use
VarChar so much?
Well, to be honest, it's easy. We, as people, are generally lazy, and it's easy to store anything in a
VarChar(50), or worse, a
VarChar(MAX)! Why is this a bad thing? Well for some data, it's not, but for others, it's just not the best option. As developers and programmers, we almost always have a choice as to how we should store our data, and sometimes, it's easy to make an inefficient one.
Let's take a solid example. I was over on Stack Overflow
one day, and I noticed a developer doing something odd: the developer was storing an IP address (we'll assume IPv4 of
192.168.0.1 which is a pretty common IP for default gateways in small home and office networks) in a
VarChar or a
Char field. I'm not sure on the precision of it, or which it was (as the developer left out the DDL), but for sake of argument let's assume it was the smallest precision required to store any IP Address, and as such a
The developer, much like the rest of us, was trying to find a way to shrink the amount of data used down. So, the developer proposed the suggestion of, instead of store
220.127.116.11, we'll just omit all the characters except the last two (in this example:
.1), and keep the fourth octet in the database. The downfall of this is quite obvious: we now have no way of distinguishing whether our value is
18.104.22.168 or any other repeated value. But, there's a better way.
Let's take a peek at what we know at this point:
- The data being stored is binary data;
- It's being stored in a string field;
- The maximum length on the string field is 15 characters;
Now this doesn't just apply to IP Addresses, it also applies to hashes, encrypted data and other binary objects.
At first glance this might not seem so bad. The IP Address as a string is
192.168.0.1. The maximum data-size is going to be 17 bytes, as the
VarChar type takes one byte per character, and two bytes of overhead. The size for our specific address is 12, by the same math. The developer took the time to address the issue of fitting the data within the seemingly smallest datatype possible. But what did the developer forget?
First, we're trying to store binary data. The smallest way to store this (at least in string format) is either in hexadecimal or Base64 encoding. Let's assume we use hexadecimal (it really doesn't matter either way). We're storing data that is four bytes, which means we need eight characters. Our example leaves us with
0xC0A80001 or, for short:
C0A80001. So, this alone allows us to reduce our maximum storage space to almost half it's original size, and our utilized space (for this example) to 10 bytes from 12. With just one quick optimization we converted our 15-character string to an 8-character hexadecimal string. Now that we know that, we can make another optimization and change it to a
Char(8) type. This reduces two more bytes of overhead, and leaves our example at a cool 8 bytes of storage space.
But, we're forgetting one small thing: SQL Server (at least, Microsoft SQL Server) has a
Binary type. Much like the
Char type, the
Binary type has a fixed size. The difference is that the
Binary type can store raw byte data. It takes a length, just like the
Char does, so in our case, it would be
Binary(4) (to store four bytes for one IPv4 address). The binary type will only store the raw data for the address, so we're left with:
- Byte 1:
- Byte 2:
- Byte 3:
- Byte 4:
Microsoft SQL Server also has a
VarBinary type which works just like the
VarChar type. It supports the same size limits:
MAX. It also requires two bytes of overhead for each row, just like a
The nice thing about using a
Binary type for this field, is that it allows us to save a significant amount of space. By optimizing this field, we've saved 11 bytes of storage per row. How significant is that? If we had 500,000,000 we've saved 5.5GB of data. (And for big-data applications, 500,000,000 rows is insignificant.)
You might say, "well my application is small data, 500,000,000 rows is a pretty significant number, and 5.5GB for that many records is small." While that may be true, this is just one field we've optimized.
The DateTime example
Let's take another example: I've seen a lot of people use the
VarChar type for
DateTime data as well, when it's completely unnecessary. The SQL Server has several types for
DateTime data, the more useful being
DateTimeOffset. Microsoft recommends that you no longer use
DateTime for new work, as the
DateTimeOffset types align with the SQL standard, and are more portable. The
DateTimeOffset fields also have better precision and a larger range.
Why is this so important? You can just as easily store a as a string in a
VarChar field, and then parse it later. The problem with that is that you can't filter quite so easily for certain criteria. It's easy (at least with a
DateTime2 field) to filter for dates within a certain range, on a certain date, etc. It's less intuitive with any string type.
The other problem is less obvious: with a
VarChar type, there is no validation done that guarantees the input string is a
DateTime string. This means it's up to whatever logic you have manipulating the database to make this guarantee.
What about the
I've not discussed these so far because we were talking about binary data, which in most any form is stored in some ASCII or raw form. These types (
NChar) are Unicode (UTF-16, specifically) variants of the
Char types, respectively. These types take two bytes per character, with the variable-length type taking an extra two bytes of overhead. In our example, were the first field type an
NVarChar(15) it would have taken up to 32 bytes of data. (As 30 bytes for the 15 characters plus two bytes of overhead.) The specifiable sizes for these two fields are any integers in the range
What do the numbers in parenthesis represent?
Many fields have an optional size, precision or other parameter to represent different amounts and forms of data that can be stored within them. For all fields we're discussing in this article, the parenthesis represent how many characters (for the
NVarChar types), or how many bytes (for the
VarBinary types) the field can store.
What are the
VarBinary types doing internally?
All three of these types work in a very specific way, internally. You can see that the maximum size any of the three of them can take is up to 8000 bytes, but what does that mean?
Internally, in Microsoft SQL Server, the variable length fields (which have the optional
MAX specification) store data in one of two ways:
- For data that fits within 8000 bytes, the data is stored in-row;
- For data greater than 8000 bytes, the data is stored out-of-row and a pointer to the data is stored in-row;
This should help clarify what the server is doing, and what the specifications mean, and why I always cringe when I see
NVarChar(MAX), in a situation that doesn't call for it.
As always: know your data, know your users, and most of all, know your environment.