Category: Data Types

Greg Larsen compares CHAR, VARCHAR, and VARCHAR(MAX):

In every database, there are different kinds of data that need to be stored. Some data is strictly numeric, while other data consists of only letters or a combination of letters, numbers, and even special symbols. Whether it is just stored in memory or on disk, each piece of data requires a data type. Picking the correct data type depends on the characteristics of the data being stored. This article explains the differences between CHAR, VARCHAR, and VARCHAR(MAX).

Click through for Greg’s explanation. My official rule of thumb is as follows:

• If you have a fixed-length code which you display to customers, use NCHAR.
• If you have a fixed-length code which you only use internally and you know that the code will never include characters outside of your SQL Server installation’s code page and you know that the code page will never change…probably still use NCHAR, though if you twist my arm enough I’d say fine, CHAR.
• Otherwise, use NVARCHAR.

Three decades ago, the choice was a lot trickier given performance differences between the two. Today? Unless you’re hunting for microseconds I don’t think you’ll see a practical difference. And if you are hunting for microseconds, you probably want more than rules of thumb.

Aaron Bertrand finds a case where casing matters:

We all have coding conventions that we have learned and adopted over the years and, trust me, we can be stubborn about them once they’re part of our muscle memory. For a long time, I would always uppercase data type names, like INT, VARCHAR, and DATETIME. Then I came across a scenario where this wasn’t possible anymore: a case-sensitive instance. In a recent post, Solomon Rutzky suggested:

As long as you are working with SQL Server 2008 or newer, all data type names, including sysname, are always case-insensitive, regardless of instance-level or database-level collations.

I have a counter-example that has led me to be much more careful about always matching the case found in sys.types.

Click through for that scenario.

Kenneth Fisher discovers a secret of the sysname datatype:

The other day I was creating a table to store some metadata. Since the metadata I was collecting (sys.databases.name for example) uses the datatype sysname I used the same datatype. In case you didn’t know sysname is an nvarchar(128) user defined datatype used in a number of the system views.

Click through for Kenneth’s finding.