I taught a T-SQL Programming class this week and in the process looked over some old books on the subject. One in particular I've enjoyed was the Transact-SQL Cookbook from O'Reilly - I have yet to find as novel an approach to SQL, coming from the ideas of set theory rather than tutorials on querying. I'm biased too, my best friend in highschool was Slovenian and one of the authors, Aleš Špetič, hails from that fine country.
A cool idea from the very first chapter is the pivot table, a numeric range that can come in handy for many different types of operations. The book, which is probably circa Microsoft SQL Server 7, demonstrates the building of a pivot table using some hardcoded insert statements followed with a cartesian join that generates the range.
It occured to me in SQL 2000 and higher one can use a User Defined Function and get all the benefits with a little bit more flexibility. Here is a simple approach to the same concept:
CREATE FUNCTION fnPivot(@BOUND INT)
RETURNS @Pivot TABLE(I INT)
DECLARE @I INT
SET @I = 1
WHILE @I <= @BOUND BEGIN
INSERT INTO @Pivot VALUES(@I)
SET @I = @I + 1
The approach is different but the benefits are similar. A simple one from the first chapter is building a calendar of a given range of days. I've adapted it to use the function above:
CONVERT(CHAR(10), DATEADD(d, i, CURRENT_TIMESTAMP), 121) [date],
DATENAME(dw, DATEADD(d, i, CURRENT_TIMESTAMP)) [day]