It’s Not a Zebra: Debugging Your Code Efficiently
If you’re in a pasture and hear hoofbeats…it’s probably not a zebra.
I have heard this saying several times in the past. It basically means to check the most obvious first.
A doctor once told me this when my daughter was having stomach issues. After weeks of every kind of test for scary medical issues without finding anything wrong, we went to a second doctor. He told us “If you’re in a pasture and hear hoof beats…it could possibly be a zebra….but it’s probably just a horse”. In other words, it could be stomach cancer, could be ulcers, etc. Our first doctor was checking for all the “zebras” before eliminating the possibility of the more common “horse”. It turns out my teenage daughter was just suffering from some stressful issues at school and all was well once this was resolved.
As a DBA, I am often presented with extremely complex code to debug, optimize, or rewrite. Occasionally, we are even lucky enough to find an obscure bug in SQL Server that causes strange behavior. I was presented with a scenario recently that had me confused and flustered. I’m actually embarrassed to admit how much time this issue took me to resolve.
A user presented me with a question concerning the usage of variables in a LIKE operation. He was getting very unpredictable results and asked me to review his sample code. All three iterations of his code appeared to be equivalent and should have returned the same results. He was getting very different results with each one.
Sample code:
I realize that using preceding % in a LIKE is inefficient, but the usage of the variable in the sample above all appeared correct.
The actual results are shown below:
Hmmm…
This had me confused and led me down many paths. I searched the web for hotfixes, connect items, issues with the LIKE command in 2008 R2, and anything else I could think of. I couldn’t find instances of anyone else reporting this type of issue. I decided there had to be an issue with the underlying data.
I removed the ‘COUNT(*)’ and replaced it with the P.name column name so I could view the data. Now things were really confusing.
The results from the first two queries looked nothing like I expected. Many rows had no visible ‘FR’ characters:
The last query however did return the expected results:
I even examined the hex and varbinary values of the underlying data to look for strange hidden characters:
At this point, I had to take a break. After a little caffeine in my system and a few minutes away, I started again. That’s when I noticed something I should have noticed an hour before….
declare @queryvar nvarchar;
Eureka! The variable was declared as nvarchar with no size given.
I changed my variable to a size of 4 and reran my queries:
declare @queryvar nvarchar(4);
Now, the results were the same (and were correct) for all 3 queries:
Just to verify the earlier behavior, I checked the length of my original variable.
And got the expected length of 1:
A couple of quick searches revealed that SQL server data types of nchar and nvarchar default to a length of 1 when no length is specified. When used in a CAST, the default length is 30. For more information, please visit
https://technet.microsoft.com/en-us/library/ms186939(v=sql.105).aspx
I sent the information back to my customer and all was well.
There are two lessons I learned from this debugging experience. The first is to not be lazy when declaring variables – always specify the length. The more important lesson that I was reminded of that day is, “If you’re in a pasture and hear hoof beats…it’s probably not a zebra”. Before diving into any deep analysis or looking for the strange and obscure errors, go back to the beginning and recheck your basic query information. You’ll probably find your “horse”!
Thank you for reading my first RDX blog post! Please check back soon for more posts.