We've all seen it ...

Question

We've all seen it ...

Dwain Camps

SSC Guru

Points: 86948
More actions
July 9, 2013 at 7:25 pm

#395108

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

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 SSC Guru Points: 1004340 More actions · Answer 2

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. 😀 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mickyT SSChampion Points: 10360 More actions · Answer 3

by the way I had a laugh over the $ controlling the order of things 😀

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

mickyT (7/9/2013)
by the way I had a laugh over the $ controlling the order of things 😀

Yeah! Probably should have used the Euro symbol instead. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

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. 😀 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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mickyT SSChampion Points: 10360 More actions · Answer 7

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 SSChampion Points: 10360 More actions · Answer 8

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