Dude where’s my byte?

I’ve encountered a very interesting problem today, the mystery of the missing byte. It all began when one of tests starting failing on my development machine after I got latest from repository. The problem revolves around saving a checksum (byte array) to a varbinary column on SQL Server 2005. The checksum that was generated in code (which is unique for each machine) has a trailing byte of zero in the byte array, and after being saved to SQL Server, the zero byte is missing!!!!!

It was doing my head in, I went through the code and database creation scripts, and finally managed to find the problem. Apparently in the database scripts, there was a line ‘SET ANSI_PADDING OFF‘. This tells SQL Server to truncate off trailing zeros for varbinary and trailing spaces for varchar types. This setting takes into effect during table creation, so whatever you specified stays in effect after. In order to change “setting” for that table, you have to drop the table and re-create it with new setting, like so..



-- and so on


MSDN has recommended that you leave the setting to ON, and for very good reason. I doubt you would want things to auto-magically disappear or be modified without your knowledge, but should be should be made explicit and known. It would have saved me hours of troubleshooting, and I hope it will save hours for you too! Please see the ANSI_PADDING MSDN page for more info.

Share this post :

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: