using Programming;

A Blog about some of the intrinsics related to programming and how one can get the best out of various languages.

SQL Server Datatypes: How to avoid VarChar

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 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 VarChar(15).

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, 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,, or any other repeated value. But, there's a better way.

Let's take a peek at what we know at this point:
  1. The data being stored is binary data;
  2. It's being stored in a string field;
  3. 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 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:
  1. Byte 1: 0xC0
  2. Byte 2: 0xA8
  3. Byte 3: 0x00
  4. Byte 4: 0x01
Microsoft SQL Server also has a VarBinary type which works just like the VarChar type. It supports the same size limits: 1-8000 or MAX. It also requires two bytes of overhead for each row, just like a VarChar type.

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 DateTime, DateTime2, and DateTimeOffset. Microsoft recommends that you no longer use DateTime for new work, as the DateTime2 and DateTimeOffset types align with the SQL standard, and are more portable. The DateTime2 and 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 NVarChar and NChar types?

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 (NVarChar and NChar) are Unicode (UTF-16, specifically) variants of the VarChar and 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 1-4000, or MAX for NVarChar.

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 Char, VarChar, NChar and NVarChar types), or how many bytes (for the Binary and VarBinary types) the field can store.

What are the VarChar, NVarChar and 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:
  1. For data that fits within 8000 bytes, the data is stored in-row;
  2. 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 VarChar(MAX) or NVarChar(MAX), in a situation that doesn't call for it.

In summation:

As always: know your data, know your users, and most of all, know your environment.

Visual C++: Bug with constant arithmetic loops

I was working with Visual C++ for another article I'm preparing, and I noticed an odd bug with the const modifier in Visual C++.

The following code demonstrates the issue:

#include "stdafx.h"
#include <stdio.h>
#include <Windows.h>

#define ITERATIONS 500000
#define GET_START_TIME QueryPerformanceCounter(&StartingTime);
#define GET_END_TIME QueryPerformanceCounter(&EndingTime);
#define CALC_DIFF_TIME ElapsedMicroseconds.QuadPart = EndingTime.QuadPart - StartingTime.QuadPart; ElapsedMicroseconds.QuadPart *= 1000000; ElapsedMicroseconds.QuadPart /= Frequency.QuadPart;

int main()
    short results[ITERATIONS];
    const int n = 5;
    int m = 5;
    LARGE_INTEGER StartingTime, EndingTime, ElapsedMicroseconds;
    LARGE_INTEGER Frequency;

    // This loop seems to take about 1400 us on my computer.
    printf("Beginning loop over %i iterations with n constant.\n", ITERATIONS);


    for (int i = 0; i < ITERATIONS; i++)
        int statement = i % 10;

        if (statement == 0)
            results[i] = n * 0;
        else if (statement == 4)
            results[i] = n * 4;
        else if (statement == 2)
            results[i] = n * 2;
        else if (statement == 5)
            results[i] = n * 5;
        else if (statement == 7)
            results[i] = n * 7;
        else if (statement == 6)
            results[i] = n * 6;
        else if (statement == 1)
            results[i] = n * 1;
        else if (statement == 3)
            results[i] = n * 3;
        else if (statement == 9)
            results[i] = n * 9;
        else if (statement == 8)
            results[i] = n * 8;


    printf("Finished in %lld us.\n", ElapsedMicroseconds.QuadPart);

    // This one takes about 800 us on my computer.
    printf("Beginning loop over %i iterations with m variable.\n", ITERATIONS);


    for (int i = 0; i < ITERATIONS; i++)
        int statement = i % 10;

        if (statement == 0)
            results[i] = m * 0;
        else if (statement == 4)
            results[i] = m * 4;
        else if (statement == 2)
            results[i] = m * 2;
        else if (statement == 5)
            results[i] = m * 5;
        else if (statement == 7)
            results[i] = m * 7;
        else if (statement == 6)
            results[i] = m * 6;
        else if (statement == 1)
            results[i] = m * 1;
        else if (statement == 3)
            results[i] = m * 3;
        else if (statement == 9)
            results[i] = m * 9;
        else if (statement == 8)
            results[i] = m * 8;


    printf("Finished in %lld us.\n", ElapsedMicroseconds.QuadPart);


    return 0;

Essentially, if I use a constant (declared in the method) to multiply against for the if blocks, it takes 175% of the time to run through the loops than if I use a regular variable.

I'm no expert on the subject, but this doesn't seem to be the expected behavior.

If anyone has any ideas on it, I'm all ears. Otherwise, I'm just going to sum it all up in that it's a bug with the compiler or execution runtime.

Additional investigation has revealed the following:

If the short array is replaced with an int array, and the number of ITERATIONS is halved, then both loops take the same amount of time. It seems the issue is somewhere with the assignment of the second arithmetic result to a short array is faster than assigning it to an int.


As it turned out, after inspecting the .asm file, the loops were being optimized because results was never used. This caused the body of the loops to be removed, and the only operation remaining was the i % 10 operation, which was slightly different for each loop.

As Hans Passant said on Stack Overflow:

Looking at the machine code is important to see what is happening. Very little of your code remains after the optimizer is done with it, the result[] assignments are all removed since they don't have any observable side-effects and the n and m identifiers never get used. All that remains is the code for i % 10. Which is optimized to a multiplication, much faster on Intel cores. It uses two different strategies for some reason, one is signed and the other is unsigned. You are seeing that the unsigned version is slightly faster. - Hans Passant, 13 Nov 2015

I guess it goes to show: you can never depend on the compiler doing exactly what you think it does.

On GitHub as promised.

Download: Constant Arithmetic Bug (13-11-2015).zip (232.1KB)

About this Blog

What is Using Programming?

Using Programming is a blog about some of the hidden features of various languages, not-so-obvious optimization strategies, and other ways you can take advantage of various languages and their particular gems. This blog is not exclusive to any one language or framework, I'm going to cover things based on what I run into in my day-to-day work with various languages.

There will probably be a higher quantity of .NET (Visual Basic, C#, ASP.NET) and JavaScript posts simply because that's what my full-time job is in, and what my pet projects are in, but never-fear! I will be making posts on all languages I run into.

Why was the name "Using Programming" chosen? 

The name Using Programming is a two part name. First, it's a play on the C# style of including additional types from additional namespaces in your code. Second, it stands for the ideal of this blog: to help developers get the most out of their programming experience. 

So how do I take the most out of Using Programming?

The best way to use this blog as a resource is to simply try and experiment with what concepts I am drawing out. Everything I run into and blog about I will attach source-code for, so that you may try the exact same experiments that I have done, to help you see exactly how these things work. Some of the optimization strategies I will be going into may be of significant importance to you, and as such you may find the source-code much more usable.

What can I expect to see on Using Programming?

I'm going to try to follow a few guidelines here on Using Programming:
  • All posts will have a summary at the top to indicate a little bit about the topic;
  • All posts related to a language feature will include a digression on what problem the feature is designed to solve, and why it needs solving;

How is the source code licensed?

I will be placing all source-code on GitHub under the MIT license. You may do anything you wish with it and redistribute it at your heart's content. The only request I make is that you include credit where credit is due.

How often is Using Programming updated?

I'll be attempting to make posts at least once-a-week to keep users informed on all the things I've run into. Do note, however, that I may not be able to guarantee a post each week, so don't fret if you don't see a post for a week or two, I promise, I'm still around.

Where can I find examples?

Source code for all articles can be found over on GitHub. You are free to use them to your hearts content, and may do anything you wish with them. I don't guarantee that they will be following best practices, though I do guarantee they cover the text of the article they represent fully.