Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 We've all seen it ... Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 9, 2013 7:25 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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
Post #1471910
 Posted Tuesday, July 9, 2013 8:11 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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 toocause 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-Forever Group: General Forum Members Last Login: Today @ 3:29 PM Points: 42,079, Visits: 39,468
 Test Code...`DECLARE @BitBucket BIGINTSET STATISTICS TIME,IO ONSELECT 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1471921
 Posted Tuesday, July 9, 2013 8:19 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1471923
 Posted Tuesday, July 9, 2013 8:23 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Jeff Moden (7/9/2013)Test Code...`DECLARE @BitBucket BIGINTSET STATISTICS TIME,IO ONSELECT 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1471925
 Posted Tuesday, July 9, 2013 8:35 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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 toocause 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 returnsThat 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
Post #1471927
 Posted Tuesday, July 9, 2013 8:40 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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 Group: General Forum Members Last Login: Monday, November 28, 2016 11:55 AM Points: 1,227, Visits: 3,308
 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

 Permissions