On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

The N Word

May 9th, 2014 · No Comments · sql, Sql Server

No, not that N word. I’m talking about N string literal prefixes in T-SQL. Like this:

SELECT * FROM Foo WHERE Bar = N'Baz'

If you don’t know what that N is for, it tells Sql Server that your string literal is an nvarchar, rather than a varchar… that is, that the string literal may contain Unicode characters, so it can support non-ASCII characters. Things like this: 例子. But I can hear you now: that sample is all ASCII. Why does it matter? I’m glad you asked.

Let’s pretend for a minute that the Bar column from that example is a varchar column, and not an nvarchar column after all. We have a type mismatch on the comparison. Pop Quiz: what happens?

We’d like Sql Server to convert the ‘Baz’ literal to a varchar, because that is obviously more efficient. Unfortunately, it won’t work that way. Converting from nvarchar to varchar is a narrowing conversion. There are some things that can’t be accurately expressed when converting from nvarchar to varchar, which means there is a potential to lose information in the conversion. Sql Server is not smart enough to know that this particular literal will map to the smaller data type without data loss. If it converts the literal to a varchar, it might give you the wrong result, and Sql Server won’t do that.

Instead, it has no choice but to convert your Bar column to an nvarchar. I’ll say that again: it has no choice but to convert the value from every row in your Bar column to an nvarchar, even if you only get one row in the results. It can’t know if a given row matches your literal until it completes that conversion. Moreover, if you have an index on that column that would have helped, these converted values are not really the same value any more as what is stored in your index, meaning Sql Server can’t even use the index.

This could easily mean a night and day performance difference. A query that used to return instantly could literally take minutes to complete. A query that used to take a few seconds might now run for an hour.

Just in case you think this scenario seems unlikely, keep in mind that ADO.Net uses nvarchar parameter types by default if you use the AddWithValue() function or it otherwise can’t infer the parameter type. If that query parameter compares to a varchar column, you’ll end up in this exact situation, and I see it all the time.

The good news is that you’re okay going the other direction… at least in this scenario. If Bar is an nvarchar column and you define Baz as a varchar literal, converting the Baz literal would be a widening conversion, which Sql Server will be more than happy to perform. Your Bar column values are unchanged, and so you can still use an index with the Bar column.

I hope your conclusion from this example is not that you should always just omit the N prefix. That’s not the message I want to send at all. In fact, the same Stack Overflow question that prompted this example also included an example that would fail to even execute in the case of type mismatch. Instead, I hope I’ve shown here that it can really matter whether you get your SQL string literals right, and that it pays to keep the exact data types of your columns in mind.

Tags:

No Comments so far ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment