Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


We've all seen it ...


We've all seen it ...

Author
Message
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
OK folks. You've seen me (and many others) use Tally tables often enough.

You generate a ROW_NUMBER() and you don't care what the ordering is so you do something like this:


SELECT TOP 10 n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns




Why not like this?


SELECT TOP 10 n=ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns




Then there's my personal favorite, which I might start to use if no one offers an objection.


SELECT TOP 10 n=ROW_NUMBER() OVER (ORDER BY (SELECT $))
FROM sys.all_columns




I'm wondering if using NULL is the best performing or if it doesn't matter. I haven't tried the latter but I did run some tests on the first 2 and they looked like a wash.

Saving a few keystrokes helps with my carpal tunnel. :-)


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
The only thing I could think of is the size of the datatype used and I don't think that would have any noticeable difference on the speed.
Having a look at the plans of the the three options
... OVER (ORDER BY (SELECT NULL))
... OVER (ORDER BY (SELECT 0))

have a compute scalar with a data size of 110B, while

... OVER (ORDER BY (SELECT $))

has a data size of 150B.

Using
... OVER (ORDER BY (SELECT CAST(0 AS BIT))

has a data size of 90B.

And in the category of just plain silly
... OVER (ORDER BY (SELECT 'I can''t think of anything better to put in here too
cause this to be a string larger in bytes than I would expect the previous examples
to have, so I''ll just keep on typing until the carpel tunnel sets i'))


the size balloons out to 1,140B including the carriage returns:-)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45012 Visits: 39884
Test Code...
DECLARE @BitBucket BIGINT

SET STATISTICS TIME,IO ON
SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1, sys.all_columns ac2;

SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns ac1, sys.all_columns ac2;

SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT $))
FROM sys.all_columns ac1, sys.all_columns ac2;

SET STATISTICS TIME,IO OFF



They all seem to takes turns winning but all are within just a couple of ms of each other on my Dev server at work.

As a bit of a side bar and as cool as it is, I prefer not to use the "$" sign trick... I already have people that have difficulty in understanding how the T-SQL behind even this simple thing works. I don't want to push them over the edge. :-D Other than that, I don't have a preference but my fingers frequently type "(SELECT NULL)" due to some old muscle memory.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
by the way I had a laugh over the $ controlling the order of things :-D
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
mickyT (7/9/2013)
by the way I had a laugh over the $ controlling the order of things :-D


Yeah! Probably should have used the Euro symbol instead. :-P


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
Jeff Moden (7/9/2013)
Test Code...
DECLARE @BitBucket BIGINT

SET STATISTICS TIME,IO ON
SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1, sys.all_columns ac2;

SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns ac1, sys.all_columns ac2;

SELECT TOP 1000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT $))
FROM sys.all_columns ac1, sys.all_columns ac2;

SET STATISTICS TIME,IO OFF



They all seem to takes turns winning but all are within just a couple of ms of each other on my Dev server at work.

As a bit of a side bar and as cool as it is, I prefer not to use the "$" sign trick... I already have people that have difficulty in understanding how the T-SQL behind even this simple thing works. I don't want to push them over the edge. :-D Other than that, I don't have a preference but my fingers frequently type "(SELECT NULL)" due to some old muscle memory.


I actually do the same with (SELECT NULL) ... oddly habit forming.

Speed seems to hold pretty constant up thru 10,000,000 rows, fluctuating a little in favor of one or the other each time you run it.

Why doesn't SQL allow the ORDER BY to be optional? In which case it should default to "no ordering."


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
mickyT (7/9/2013)
And in the category of just plain silly
... OVER (ORDER BY (SELECT 'I can''t think of anything better to put in here too
cause this to be a string larger in bytes than I would expect the previous examples
to have, so I''ll just keep on typing until the carpel tunnel sets i'))


the size balloons out to 1,140B including the carriage returns:-)


That does nothing for my Carpal Tunnel.

Neither does this:


SELECT TOP 10000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT CAST(0 AS NVARCHAR(MAX))))
FROM sys.all_columns ac1, sys.all_columns ac2, sys.all_columns ac3




But oddly it seems to perform in the same approximate time as the previous 3.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
dwain.c (7/9/2013)Why doesn't SQL allow the ORDER BY to be optional? In which case it should default to "no ordering."


I suppose it comes down to the majority of cases where doing this operation without an order makes no sense.
How often have we seen a reply to a question along the lines of ' ... you can do it, but without an order by there is no guarantee you will get a sensible result'
It would be nice if the 'nanny state' rules could sometimes be relaxed somteimes in favour of 'You can do it, but don't come crying to me' attitude:-)
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
dwain.c (7/9/2013)


SELECT TOP 10000000 @BitBucket=ROW_NUMBER() OVER (ORDER BY (SELECT CAST(0 AS NVARCHAR(MAX))))
FROM sys.all_columns ac1, sys.all_columns ac2, sys.all_columns ac3




But oddly it seems to perform in the same approximate time as the previous 3.

Even filling up a nvarchar(MAX) variable with millions upon millions of characters and using that makes no difference to performance. I suspect that it comes down to the optimizer recognizing that it is a constant and deciding that a sort is not required. Put in something like NEWID() and all of a sudden a sort operation is done and you are watching paint dry:-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search