When you need a quick tally (SQL Server 2k8)

In case you're in urgent need for a quick single use tally and don't want to bother creating it, you can use this snippet as a subquery.

	Code:
	$PROMPT(rowcount,How many rows do you need?,48879)$ ( SELECT TOP $rowcount$ ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_$rowcount$

There might be some syntax error around "(SELECT NULL)) AS n"

When trying that snippet, I get

	Code:
	( SELECT TOP 48879 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_48879

and that query doesn't compile.

Last edited by SysOp on Tue May 15, 2012 10:04 am; edited 1 time in total

It is intended to work as a subquery (or a subquery table to be more precise), it does not run on its own. You have to put 'SELECT n FROM' in front of the snippet result to make it work in its most simple form. I mostly use it when I have to populate a table with temporary test data or just need a table with sequential numbers in it. Also comes in handy when cutting strings.

That sounds interesting. Can you provide an example for each described usage type?

Sure.

The first case:
One of our customers has a database that contains a structure about access cards. The cards are assigned to levels of towers. There are 3 towers, each of them having 8 levels. One of our developer was tasked to create a query that tells which levels and towers is a card assigned to. We had the structure that stores assignments but it was completely empty. So I had to produce some test data. While it is very far from real life situation, the following script fills that table. I did not bother adding some conditions so that it resembles the real life data more precisely but it would be easy.

Code:

SELECT
qt_3.n AS [tower]
,qt_8.n AS [level]
,qt_3.n * qt_20.n AS [card]
FROM
( SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_3
CROSS JOIN
( SELECT TOP 8 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_8
CROSS JOIN
( SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_20

The second case:
Another customer has a system that stores doorkeys assigned to a card in a very uncomfortable way. They store it in a varchar fields, each key separated by ':'. Don't ask why, it's a legacy system, it has caused ungodly amount of trouble so far. They have thousands of employees (dolgozók in Hungarian), and several keys (kulcsok) assigned to many of them. There were no checks on the 3 fields (UF4, UF18, UF19) containing the keys but they were required to contain only key positions (kulcs pozíciók) and the separator and we had to indicate if there was an error (hiba in Hungarian). The original table looked like this:

	Code:
	id_dolgozo \| UF4 \| UF18 \| UF19 \| etc. 1 \| 3 \| \| 3:22 \| ;notice the duplicates here 2 \| \| 33:333 \| a32:90 \| ;notice the invalid data here 3 \| 39:22 \| 4 \| \|

We created a query that transformed this mess into a view

	Code:
	id_dolgozo \| kulcs_pozico \| hiba \| etc 1 \| 3 \| 0 1 \| 22 \| 0 2 \| 33 \| 0 2 \| 333 \| 0 2 \| a32 \| 1 etc.

It worked. And the performance was a nightmare. Until the query was replaced with the following below which does not use a loop to locate the boundaries. It's still of moderate speed but it's faster by orders of magnitude.

Code:

SELECT
kl.id_dolgozo
,CAST(SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1) as SMALLINT) AS kulcs_pozicio
,CASE
WHEN PATINDEX('%[^0-9:]%' ,SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1)) > 0 THEN SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1)
ELSE NULL
END AS hiba
FROM
(
   SELECT
      uf.CardHolderID AS id_dolgozo
      ,REPLACE(REPLACE(REPLACE(':' + uf .UF4 + ':' + uf.UF18 + ':' + uf.UF19 + ':' ,'::' ,':;') ,';:' ,'') ,':;' ,':') AS kulcsok
   FROM
      CMS.dbo.UserFields uf
   WHERE LTRIM(RTRIM(uf.UF4)) <> ''
      OR LTRIM(RTRIM(uf.UF18)) <> ''
      OR LTRIM(RTRIM(uf.UF19)) <> ''
      OR LTRIM(RTRIM(uf.UF20)) <> ''
) AS kl
CROSS JOIN
dbo.tally AS t
WHERE n < LEN(kl.kulcsok)
AND SUBSTRING(kl.kulcsok ,N ,1) = ':'

The tally table here is a physical table the database since it is used recurringly but it was created using that snippet.