BIT Data Type

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

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

Panorama Theme by Themocracy