select what?

this is really silly, but i didn’t know what

select 1 from dbo.sometable

did.  I understood what select * did, and i understood the consequences of no where clause, but select 1 just didn’t seem to parse in my head.  Now, lets extend the idea to

select 'this_string' from dbo.sometable.

But dbo.sometable doesn’t CONTAIN “this_string” so how can i select it?  To be honest, i’m not sure how to explain it, but i know it works as i expect it to – returns “this_string” as many times as there are rows in the table. No data is returned FROM the table, but the row-count (row presence) is used as a psuedo-cursor to drive the select statement.

Jeff Moden expands on the psuedo-cursor idea at SQL Server Central

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
from dbo.sometable

Now, there IS a row_number, but it is not being returned from dbo.sometable (?) or at least there is not data in the table.  The row_number functions needs an “over” statement, so we make up a select null clause that doesn’t nothing.

the result is a psuedo-cursor which returns a list of numbers – as many as there are rows in sometable

 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM [master].[sys].[stats] -- st1
CROSS JOIN [master].[sys].[stats] st2;

What this will do is to count the lines of a cartesian join between the two tables.  One from table one, all from table 2, second from table one, all from table w, and so on.  In this case, sys.stats has 299 entries, so cross joining on ourselves gives 299*299 or 89401 rows.  You have to give at least one of the joins an alias.  Convention says do it twice.  

 with myCTE as (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as counter
FROM [master].[sys].[stats]
CROSS JOIN [master].[sys].[stats] st2
)
select counter from myCTE where myCTE.counter between 1 and 10

The select_row number stuff is saved into a CTE (common table expression) called myCTE, the column named counter, and then counter is returned – but only the first 10 rows.  If the between was different,  you would get a different start and stop and total count.  For example, the following gets 5,6,7,8,9,10,100

with myCTE as (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as counter
FROM [master].[sys].[stats] 
CROSS JOIN [master].[sys].[stats] st2 
)
select counter from myCTE where (myCTE.counter between 5 and 10 or myCTE.counter = 100)

This would let you create a psuedo-cursor for, say, days in several months

Combining the CTE and cross-joins, we have the famous Itzik Ben-Gan auxillary table method. Create a virtual table with two rows in it containing, eg. 1s.  Cross join that to itself.  You have 4 rows.  Cross-join 4×4 and get 16.  16*16 is  256.  Now it gets fun (big): 16X16 is 65,536.  Next stop is 4+ million.  Do a select row_number over … on the last table and you get lots of sequential numbers efficiently

WITH
C0 AS(SELECT 0 AS const UNION ALL SELECT 0),
C1 AS(SELECT 0 AS const FROM C0 AS A cross join C0 AS B),
C2 AS(SELECT 0 AS const FROM C1 AS A cross join C1 AS B),
C3 AS(SELECT 0 AS const FROM C2 AS A cross join C2 AS B),
C4 AS(SELECT 0 AS const FROM C3 AS A cross join C3 AS B),
C5 AS(SELECT 0 AS const FROM C4 AS A cross join C4 AS B),
C6 AS(SELECT 0 AS const FROM C5 AS A, C5 AS B) — shorthand for cross join
SELECT TOP(100) ROW_NUMBER() OVER(ORDER BY const) AS n from C6

 

.  

 

 

 

 

 

Advertisements