## BIT Data Type Comments (0)
By , February 23, 2011 20:18

I stumbled upon (or rather over) a little something this morning while I was writing a procedure one of whose parameters was of type bit.

Books Online states that the bit datatype is “An integer data type that can take a value of 1, 0, or NULL”.

Anyway, I wrote the procedure and while testing it, I was expecting to get an error when passing anything other than 1, 0 or NULL the the bit parameter. I was surprised to find that the bit will always have the value 1 when a value other than 0 or NULL is passed to it, even if the value is not an integer, but rather of type numeric (i.e. decimal or float). Even negative values are interpreted as 1.

 ```SET NOCOUNT ON DECLARE @nullbit BIT DECLARE @falsebit BIT DECLARE @truebit BIT DECLARE @posbit BIT DECLARE @negbit BIT DECLARE @decimalbit BIT SET @falsebit = 0 SET @truebit = 1 SET @posbit = CAST(POWER(2.0,63 -1) AS BIGINT) SET @negbit = CAST(POWER(-2.0,63) AS BIGINT) SET @decimalbit = -0.1 SELECT @nullbit AS [NULL], @truebit AS [TRUE], @falsebit AS [FALSE], @posbit AS positive, @negbit AS negative, @decimalbit AS [DECIMAL] SET NOCOUNT OFF```

Discovering that even bigint values were permitted, I decided to push it a little. Try the following and you’ll see that all values, between -2^122 and 2^122 are interpreted as 1 (with, of course, 0 as an exception).

 ```SET NOCOUNT ON DECLARE @results TABLE (expression FLOAT(53), bitvalue BIT) DECLARE @i FLOAT(53) = -122.0 WHILE 1=1 BEGIN BEGIN TRY INSERT INTO @results SELECT @i, CASE WHEN @i < 0 THEN CAST(POWER(-2.0,@i * -1) AS BIT) WHEN @i = 0 THEN CAST(@i AS BIT) ELSE CAST(POWER(2.0,@i) AS BIT) END SET @i += 1 END TRY BEGIN CATCH SELECT * FROM @results RETURN END CATCH END SET NOCOUNT OFF```

Conclusion
I was expecting my procedure to report some sort of error when a "false" value was passed to it. I think the thing to learn here is: always test to see if things work the way you expect them to.

## Running Totals Comments (0)
By , February 7, 2011 19:02

This morning I read a blog post where a solution for calculating running totals was presented. I wanted to post a code correction as a comment to the post itself, but after three attempts (three site errors) to submit my comment, I gave up and decided to do a quick post of my own.

I don’t want to drag anybody through the mud on this, so I wont post a link to the blog, but this is how the code was presented (I have edited it so that it can’t be used to search for the original post).

 ```DECLARE @RunningTotal TABLE (Amount INT) INSERT INTO @RunningTotal VALUES (1); INSERT INTO @RunningTotal VALUES (2); INSERT INTO @RunningTotal VALUES (3); INSERT INTO @RunningTotal VALUES (4); INSERT INTO @RunningTotal VALUES (5); SELECT a.Amount ,( SELECT Sum(b.Amount) FROM @RunningTotal b WHERE b.Amount <= a.Amount ) rs FROM @RunningTotal a```

I found the idea fairly nifty, but what I wanted to challenge was the fact that this script is entirely dependant on the order of the values stored in Amount and that if a particular amount were to be entered more than once, the results would be incorrect.

Try the above with the following values.

 ```INSERT INTO @RunningTotal VALUES (1); INSERT INTO @RunningTotal VALUES (2); INSERT INTO @RunningTotal VALUES (3); INSERT INTO @RunningTotal VALUES (4); INSERT INTO @RunningTotal VALUES (5); INSERT INTO @RunningTotal VALUES (2);```

Even if pre-sorted, the results would be partially incorrect.

It did however make me think about how I would get the required results. My first idea was to use ROW_NUMBER() and I ended up having to use a CTE too. But it works no matter what order the rows are in!

 ```;WITH RunningTotal_CTE( RowNo, Amount) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RowNo, Amount FROM @RunningTotal ) SELECT a.Amount, ( SELECT SUM (b.Amount) FROM RunningTotal_CTE b WHERE b.RowNo <= a.RowNo ) RunningTotal FROM RunningTotal_CTE a```