Issue with NULL behaviour in CHARINDEX?

April 23, 2009

select LEFT(null,CHARINDEX(‘.’,NULL))

You would expect it to work and return NULL right?
I’ve seen it not working on some servers. These servers are mostly 64 bits (meaning it’s easier to reproduce it on 64 bits) but it gets reproduced on 32 sometimes. You might even encounter the situation where it will work for an indefinte runs and all of a sudden crash.

I have posted it on some sql server forums but nobody can give a valid explanation. And going to Microsoft with it is not always easy🙂

Kind of busy lately

April 1, 2009

Last two weeks were very busy. Our company released a new version of the software and the deadline was really tight. Managers are nervous, programmers are overwhelmed, everybody wants to get the job done in time which is not always possible. The risk to break something when fixing another thing is high; also I do not like last-minute fixes. But all is well when it ends well🙂

Besides that I had to work on a arbitrary precision implementation for our calculation module. I guess we were the only calculation engine in the world where 1/3*3 is not 1🙂
I had to go with different approaches like IntX, BigNum, fraction implementations. All fixed the problem, but were not acceptable performance wise. So decision is still pending.

Tricky string concatenation

March 24, 2009

I had a bug today in one of my stored procedures. The error message indicated that somewhere a string truncation was happening. Easy enough right?


When generating dynamic sql code into some string variable which will get executed, I prefer using nvarchar(max). After all you never know how big can a string get right?. But let’s say some sp gets as input parameter a table name. No point in declaring it as nvarchar(max) since the maximum length for a database object can be 128. So I use that.

The trick is that when using constants for concatenation sql server has a limitation: 4000 for nvarchar and 8000 for varchar:

declare @sql nvarchar(max)
select @sql = replicate(N’a’,3000) + replicate(N’b’, 3000)
select len(@sql)

The result is 4000. Sql Server examines both operands before the assignement, sees them as constants and decides to use the maximum limit for nvarchar(n), even though @sql is declared as max.

Another example where varchar is used with nvarchar to illustrate data type precedence, combined with implicit conversion from varchar to nvarchar

declare @sql varchar(max), @sql1 nvarchar(max)
set @sql1 = ”
select @sql = replicate(‘a’,3000) + replicate(‘b’, 3000)
select len(@sql)
–result is 6000

declare @sql varchar(max), @sql1 nvarchar(max)
set @sql1 = ”
select @sql = replicate(‘a’,3000) + replicate(‘b’, 3000) + @sql1
select len(@sql)
–result is 4000

Sql Server converted the varchar variable @sql (length = 6000) to nvarchar. It truncated it because max length for nvarchar is 4000. it used for this datatype precedence

Sql server will not let you assign a string constant with a length greater than 4000. It will truncate:
declare @sql nvarchar(max)
select @sql = replicate(N’a’,5000)
select len(@sql) –result is 4000

The tricky part
What happens when I concatenate one string smaller than 4000 with other string variables?

The bug I had is not that obvious:

declare @sql nvarchar(max), @str1 nvarchar(128), @str2 nvarchar(max)
SET @str1 = N’x’
set @str2 = N’y’
set @sql = REPLICATE(N’a’,4000) + @str1 + @str2

select len(@sql)
select @sql

The resutl of the len is 4001. Oh, it’s greater than the 4000 limit so you would think everything is ok right? Not so fast cowboy. The @sql result ends up with ‘ay’, and in fact the length should be 4002 right? So, where did the ‘x’ character go?
Well, sql server will start evaluating the expression before assignment (also it doesn’t know that the result is a nvarchar(max)). It evaluates from left to right: first replicate(N’a’, 4000) + @str1. @str is nvarchar(128) so it assumes the result should be a constant of maximum 4000, thus it truncates the ‘x’. Next it adds that to @str2 which is a nvarchar(max) so it assumes the result is a nvarchar(max0, thus it adds the ‘y’

How can you fix it:

  1. use nvarchar(max) for all variables which are concatenated
  2. before assignment, initialize @str like this: set @sql = N” and then @sql = @sql + strings_to_concatenate. This way sql server will convert the result of concatenation from nvarchar(max) from the beginning, because it evaluates from left to right

I prefer using the second approach; no point in declaring variables using larger data types if you don’t really need it

De ce lucrez in SQL Server

March 19, 2009

Din greseala🙂

Intotdeauna mi-au placut bazele de date, iar in facultate am lucrat la greu cu Oracle.

In timpul facultatii m-am angajat la o firma de software ca programator in Visual Fox Pro (nimeni nu angajeaza un student pe oracle). Backend-ul era in sql server. Restul vi-l imaginati.

Acuma lucrez la o alta firma, mai mare si mai frumoasa🙂, dar inca raman cu regretul ca… Well, I needed the money😀


Get every new post delivered to your Inbox.