Thursday 3 March 2005 — This is close to 20 years old. Be careful.
I’ve done SQL work for a number of years, but it’s usually been pretty plain vanilla relational data: I haven’t had to venture too far into the truly wild and wooly SQL hacks. For example, I’d never heard of a numbers table. It’s a table with one integer column, and a row for each integer in a range (for example, 1 through 10,000). The table gets used for all kinds of unusual queries.
But is it a good idea? A quick look at some of these queries gives me the impression that they’d be inefficient:
DECLARE @csv VARCHAR(255)
SET @csv = '1,3,5,7,9,14,36,395'
SELECT n
FROM numbers
WHERE CHARINDEX
(
','+CONVERT(VARCHAR(12),n)+',',
','+@csv+','
) > 0
ORDER BY n
This will examine each row in the numbers table, and check if the number is in the string. This is bone-headed. In fact, if that exact algorithm were coded in a procedural language, it would qualify as a Daily WTF.
Sometimes the pure relational model nudges you into odd hacks like this, and sometimes it is hard to know the best way to slice a problem. For example, if the alternative to a numbers table is to write a loop in the client, then shuttling all the data to the client may be a bottleneck, and the numbers query may be better in any case.
As with all performance issues, you can take a guess, but the only way to really know is to measure a real system.
BTW: XSLT is another programming model that leads to these sorts of tricks. Both XSLT and SQL give you a fundamentally declarative style of programming. But developers think procedurally. The conceptual mismatch leads to difficulties expressing yourself, and then off-the-wall ideas like a numbers table to make more things possible.
Comments
It can also be used for things like opening a cursor that is a series of dates:
select add_months('01-Jan-2005', x) as d
from numbers_v
where x < 12;
The alternative to that would be twelve selects unioned together.
Perhaps what you mean is that it doesn't let you change the value once it is set (which throws into doubt the whole label "variable", but whatever).
The SQL language would be infinitely more convenient if it had sets and rows as first-class datatypes, not to mention an expression syntax analagous to Lisp’s let clauses (defining temporarty names in the middle of a compelx expression). With those features it would be possible to do more in the purely-functional subset of the language, avoiding the clunky procedural stuff.
Add a comment: