A Fine Slice Of SQL Server
Kevin Wilkie lays out an argument:
If you truly need the UNICODE characters in your data, go ahead and use them! If not though, please make your DBA happy by not using them. Since UNICODE characters take up twice the amount of space as the ASCII versions do, then your DBAs will recommend to use the ASCII versions if you are not going to be using any UNICODE characters.
Read on for the justification. But I’m still NVARCHAR (Almost) Everywhere.
Comments closedSteve Jones wants to know if people are still using CHAR:
I ran across an article, titled When to use CHAR, VARCHAR, or VARCHAR(MAX), which struck me as something I never do. I mean, I do use varchar (and nvarchar), but I can’t remember the last time I actually created a char column. The article is worth a read, and it sets the stage for you to think about your database design process and the choices you make.
It’s Friday, and if you take a few minutes and think about the last few times you’ve added columns to a table have you used CHAR as a data type? Or do you default to varchar of any size as a general rule?
Yeah, using CHAR (or NCHAR, which you’d probably want to use instead) is pretty rare. This is a consequence of storage becoming much less of a constraint over the past couple decades, as well as considerable gains in efficiency for data platform products. (N)CHAR might be a little faster than (N)VARCHAR for certain operations and may be a bit more efficient for single-character columns, but I agree with Steve’s premise.
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, andVARCHAR(MAX).
Click through for Greg’s explanation. My official rule of thumb is as follows:
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.
Comments closedGreg Larsen looks at sequences:
Microsoft introduced the sequence number objects starting with SQL Server 2012. A sequence object generates sequence numbers based on starting and increment values, similar to an identity column, but it has additional features. Over time, you might find that the additional benefits of a sequence number have you wanting to replace an identity column with a sequence number. This article demonstrates two options for replacing an identity column with a sequence number.
When it came out, I thought I was going to use sequences a quite often. In practice, I’ve used it a few times, but IDENTITY is so much easier to type and I’ve rarely needed sequence generators. That said, the times I have needed them, I definitely appreciate their existence.
Tomas Zika takes us through a conundrum:
If it’s the first time you’re seeing a problem like this, it can be confusing. How can
15be less than13?My first train of thought was the same as the original poster’s. Ok, so both values are converted to
varcharaccording to data type precedence – similar to this:
It’s not due to a varchar comparison, but Tomas does have an answer for us.
Comments closedDaniel Hutmacher has fun with implicit conversion:
Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.
I stumbled on an interesting exception to this rule the other day.
Click through for an example of date searching with LIKE.
A long time ago, I answered a question about NULL on Stack Exchange entitled, “Why shouldn’t we allow NULLs?” I have my share of pet peeves and passions, and the fear of NULLs is pretty high up on my list. A colleague recently said to me, after expressing a preference to force an empty string instead of allowing NULL:
“I don’t like dealing with nulls in code.”
I’m sorry, but that’s not a good reason. How the presentation layer deals with empty strings or NULLs shouldn’t be the driver for your table design and data model. And if you’re allowing a “lack of value” in some column, does it matter to you from a logical standpoint whether the “lack of value” is represented by a zero-length string or a NULL? Or worse, a token value like 0 or -1 for integers, or 1900-01-01 for dates?
Definitely read what Aaron has to say. I disagree with the tenor of his point enough that, now that I’m actually blogging again, I’ll have a post up tomorrow laying out the core of my disagreement. Stay tuned!
2 CommentsAaron 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, andDATETIME. 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.
Comments closedChris Webb has a follow-up to a prior post:
My post earlier this year on enabling query folding when using SQL queries as a data source in Power Query provoked a lot of interest. This post adds one more useful detail: how to preserve the original data types of the columns in your query when using this technique with SQL Server-related sources.
Read on for an example using the DATE datatype.
Data types are one of those core things you need to get right. Whether it’s matching types between join columns, or between parameters and predicates, not doing so can really squash application performance in quite similar ways to writing non-SARGable predicates.
That’s because — wait for it — a lot of the stuff we’ve talked about over the last week that can happen with poorly written predicates can happen with poorly matched data types, too.
Click through for an example. If this keeps up, we may never save Sarge.
Comments closed