I’ve been spending the past week or so changing a colleagues scripts into SSRS reports so we can automate things a bit better. During some QA checks I noticed that I was coming out with higher counts on one section of the report.

Much to my frustration the two queries looked identical and I had no explanation as to why the counts were different.  Both queries were pointing at the same database, using the same tables, using the same variable and variable values. How was this possible?

It was all down to a single variable being declared as a BIT when it should have been a TINYINT. The below TSQL demonstrates this issue…

DECLARE @bit BIT;
SET @bit = 2;
SELECT @bit AS whoops;

Shows what happens when you set SQL Servers BIT data type to two!

That’s right, no overflow error, it just happily truncates the value down to one! I’ve blogged about a similar issue before but MySQL was the badly behaved one in this situation.  I’m guessing here that the BIT variable was based on a column that was, at one time, a BIT. But as the system evolved the column changed into a TINYINT but reports weren’t updated.  I’m unsure if there’s a way to make SQL Server error on this but it’s certainly something to keep an eye on!