Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

We've all seen it ... Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 7:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1471910
Posted Tuesday, July 9, 2013 8:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 1,081, Visits: 3,171
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
Post #1471920
Posted Tuesday, July 9, 2013 8:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 35,593, Visits: 32,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1471921
Posted Tuesday, July 9, 2013 8:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 1,081, Visits: 3,171
by the way I had a laugh over the $ controlling the order of things
Post #1471922
Posted Tuesday, July 9, 2013 8:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!

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!
Post #1471923
Posted Tuesday, July 9, 2013 8:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!

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!
Post #1471925
Posted Tuesday, July 9, 2013 8:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1471927
Posted Tuesday, July 9, 2013 8:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 1,081, Visits: 3,171
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
Post #1471928
Posted Tuesday, July 9, 2013 9:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 1,081, Visits: 3,171
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
Post #1471932
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse