How to increment a counter that contains NULLs and MSSQL
- Counters - IDs are usually incremented by defining an identity column.
- Identity columns are automatically, usually by 1.
- What happens when you already have a counter but there are some null values in the counter column?
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Microsoft SQL Server 2000 is lot more complicated because it does not have the windowing function of row_number()
Data used
USE sql911;
go
SELECT counter,emp_short,trans_date,debit,credit
FROM trans02;
go
counter emp_short trans_date debit credit
----------- --------- ----------------------- --------------------- ---------------------
10 000190 2007-06-01 00:00:00.000 NULL 125.00
20 000110 2007-06-01 00:00:00.000 NULL 100.00
30 000190 2007-06-02 00:00:00.000 25.00 NULL
40 000030 2007-07-15 00:00:00.000 357.00 NULL
50 000030 2007-07-16 00:00:00.000 NULL 22.00
60 000030 2007-07-18 00:00:00.000 NULL 41.00
70 000030 2007-07-20 00:00:00.000 NULL 29.50
80 000110 2007-07-20 00:00:00.000 42.00 NULL
90 000110 2007-07-22 00:00:00.000 66.00 NULL
100 000110 2007-07-23 00:00:00.000 77.00 NULL
110 000010 2007-07-23 00:00:00.000 11.00 NULL
120 000010 2007-07-24 00:00:00.000 22.56 NULL
130 000010 2007-07-25 00:00:00.000 33.69 NULL
NULL 000070 2007-09-25 00:00:00.000 NULL 11.11
NULL 000070 2007-09-26 00:00:00.000 NULL 11.22
NULL 000070 2007-09-27 00:00:00.000 55.00 NULL
NULL 000070 2007-10-01 00:00:00.000 52.50 NULL
NULL 000160 2007-10-20 00:00:00.000 9.00 NULL
NULL 000160 2007-10-20 01:00:00.000 11.50 NULL
NULL 000160 2007-10-21 00:00:00.000 NULL 45.00
(20 row(s) affected)
Incrementing counters that already contain NULLs
- MSSQL 2005 and MSSQL 2008 provide the windowing function: ROW_NUMBER() OVER (...)
SELECT counter,
CASE WHEN counter IS null THEN
(row_number() OVER (ORDER BY emp_short,trans_date) +
(SELECT MAX(trmax.counter) FROM trans02 trmax ) + 10
)
ELSE counter
END AS 'Calculated counter',
emp_short,trans_date,debit,credit
FROM trans02
ORDER BY 'Calculated counter';
go
counter Calculated counter emp_short trans_date debit credit
----------- -------------------- --------- ----------------------- --------------------- ---------------------
10 10 000190 2007-06-01 00:00:00.000 NULL 125.00
20 20 000110 2007-06-01 00:00:00.000 NULL 100.00
30 30 000190 2007-06-02 00:00:00.000 25.00 NULL
40 40 000030 2007-07-15 00:00:00.000 357.00 NULL
50 50 000030 2007-07-16 00:00:00.000 NULL 22.00
60 60 000030 2007-07-18 00:00:00.000 NULL 41.00
70 70 000030 2007-07-20 00:00:00.000 NULL 29.50
80 80 000110 2007-07-20 00:00:00.000 42.00 NULL
90 90 000110 2007-07-22 00:00:00.000 66.00 NULL
100 100 000110 2007-07-23 00:00:00.000 77.00 NULL
110 110 000010 2007-07-23 00:00:00.000 11.00 NULL
120 120 000010 2007-07-24 00:00:00.000 22.56 NULL
130 130 000010 2007-07-25 00:00:00.000 33.69 NULL
NULL 148 000070 2007-09-25 00:00:00.000 NULL 11.11
NULL 149 000070 2007-09-26 00:00:00.000 NULL 11.22
NULL 150 000070 2007-09-27 00:00:00.000 55.00 NULL
NULL 151 000070 2007-10-01 00:00:00.000 52.50 NULL
NULL 156 000160 2007-10-20 00:00:00.000 9.00 NULL
NULL 157 000160 2007-10-20 01:00:00.000 11.50 NULL
NULL 158 000160 2007-10-21 00:00:00.000 NULL 45.00
Warning: Null value is eliminated by an aggregate or other SET operation.
(20 row(s) affected)
- Line 3: row_number() over (order by emp_short,trans_date) is what makes each row unique.
- Line 4: (select max(trmax.counter) from trans02 trmax ) is to make sure that we actually increment to a larger number than counter, so we do not end up with duplicates in the Calculated counter.
Detailed calculations of Incrementing counters that already contain NULLs
SELECT counter,
CASE WHEN counter IS null THEN
(row_number() OVER (ORDER BY emp_short,trans_date) +
(SELECT MAX(trmax.counter) FROM trans02 trmax ) + 10
)
ELSE counter
END AS 'Calculated counter',
row_number() OVER (ORDER BY emp_short,trans_date) AS 'Row number',
(SELECT MAX(trmax.counter) FROM trans02 trmax ) AS 'Maxed',
emp_short,trans_date,debit,credit
FROM trans02
ORDER BY 'Calculated counter';
go
counter Calculated counter Row number Maxed emp_short trans_date debit credit
----------- -------------------- -------------------- ----------- --------- ----------------------- --------------------- ---------------------
10 10 19 130 000190 2007-06-01 00:00:00.000 NULL 125.00
20 20 12 130 000110 2007-06-01 00:00:00.000 NULL 100.00
30 30 20 130 000190 2007-06-02 00:00:00.000 25.00 NULL
40 40 4 130 000030 2007-07-15 00:00:00.000 357.00 NULL
50 50 5 130 000030 2007-07-16 00:00:00.000 NULL 22.00
60 60 6 130 000030 2007-07-18 00:00:00.000 NULL 41.00
70 70 7 130 000030 2007-07-20 00:00:00.000 NULL 29.50
80 80 13 130 000110 2007-07-20 00:00:00.000 42.00 NULL
90 90 14 130 000110 2007-07-22 00:00:00.000 66.00 NULL
100 100 15 130 000110 2007-07-23 00:00:00.000 77.00 NULL
110 110 1 130 000010 2007-07-23 00:00:00.000 11.00 NULL
120 120 2 130 000010 2007-07-24 00:00:00.000 22.56 NULL
130 130 3 130 000010 2007-07-25 00:00:00.000 33.69 NULL
NULL 148 8 130 000070 2007-09-25 00:00:00.000 NULL 11.11
NULL 149 9 130 000070 2007-09-26 00:00:00.000 NULL 11.22
NULL 150 10 130 000070 2007-09-27 00:00:00.000 55.00 NULL
NULL 151 11 130 000070 2007-10-01 00:00:00.000 52.50 NULL
NULL 156 16 130 000160 2007-10-20 00:00:00.000 9.00 NULL
NULL 157 17 130 000160 2007-10-20 01:00:00.000 11.50 NULL
NULL 158 18 130 000160 2007-10-21 00:00:00.000 NULL 45.00
Warning: Null value is eliminated by an aggregate or other SET operation.
(20 row(s) affected)
