Tuesday, January 10, 2006

SQL Quirks - count and sum

I was reminded of a little quirk of SQL recently, and I thought I would share it with my readers. I will be going over the SQL in a manner suitable for beginners, but the quirk that I refer to could catch an intermediate student of SQL. I will use some simple SQL to illustrate my point.

-- 1. create temporary table
id int not null ,
name varchar(50) not null
) ;

-- 2. declare variables
DECLARE @cnt int, @sum int ;

-- 3. compute statistics
SELECT @cnt = count(*) FROM #test1 ;
SELECT @sum = sum(id) FROM #test1 ;

-- 4. print results to query analyzer
PRINT '@cnt = ' + ISNULL( CONVERT(varchar, @cnt), '[NULL]') ;
PRINT '@sum = ' + ISNULL( CONVERT(varchar, @sum), '[NULL]') ;

-- 5. cleanup
DROP TABLE #test1 ;
If you will paste this sequence into query analyzer, and run it in a non-production database, you will see that it produces:

@cnt = 0
@sum = [NULL]

In step 1, we create an empty table. In step 2, we declare two variables, and their initial value is NULL. Step 3 should assign a value to both variables, BUT since there are no records, @cnt receives a value of ZERO, while @sum is set to NULL. This is counter-intuitive for me, but "it is what it is". Since the statement "SELECT @variable=value FROM thetable;" doesn't assign a value consistently, I like to add a statement like "SET @variable = INIT_VALUE;" or "SELECT @cnt = 0, @sum = 0 ;" first. As an exercise, try min and max statements. Try inserting 1 record between steps 2 and 3, and doing a SELECT ... WHERE id = -1; (a wrong value).

By the way, you will notice that I add a space, then a semicolon at the end of each statement. I do this for two reasons: 1) it helps me to catch mistakes which might otherwise be hidden; 2) a former co-worker had minor vision problems and told me that it was easier to see the semicolon if it was separated from the last word by one space (Hi, Carolyn!).