Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » SQL Server Ranking Functions
Add to briefcase
31 posts, Page 1 of 4
1234»»»

SQL Server Ranking Functions Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
WayneS
Posted Monday, April 19, 2010 8:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226
Comments posted to this topic are about the item SQL Server Ranking Functions

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #906469
Jeff Moden
Posted Monday, April 19, 2010 11:24 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 32,893, Visits: 26,765
Yowch... the formatting monster hit the code hard on this one. I hope Steve can fix it soon.

Glad to see you in the saddle again, Wayne. It's too late for me to read through it tonight but you can bet I'll read it in the morning over coffee.

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #906531
SQLRNNR
Posted Tuesday, April 20, 2010 12:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329
Congrats on getting this article out Wayne.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #906551
Michael Meierruth
Posted Tuesday, April 20, 2010 1:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, May 05, 2013 10:12 AM
Points: 480, Visits: 1,604
Wayne,
You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.
How do you explain that?

Oh yes, those ORDERBY and PARTITIONBY need fixing.
Post #906596
mister.magoo
Posted Tuesday, April 20, 2010 5:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 1,287, Visits: 3,851
Michael Meierruth (4/20/2010)
Wayne,
You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.
How do you explain that?

Oh yes, those ORDERBY and PARTITIONBY need fixing.


You just have to look at the execution plan to see why they come out in that order without a specific order by on the select.

You will see a SORT to get the row numbers in the correct sequence. In the absence of any ORDER BY clause, there is no reason for the result set to be sorted again.

MM
Post #906725
SW_Lindsay
Posted Tuesday, April 20, 2010 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 1:40 PM
Points: 111, Visits: 280
Nice and helpful article - Thanks. Just Curious...

To select eligible candidates in the first select you say

select * from @Candidates where MeetsEligibility = convert(bit,1);

Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?


Steve
Post #906787
Ken Davis
Posted Tuesday, April 20, 2010 7:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:58 PM
Points: 135, Visits: 354
Excellent article. It's very readable with good examples.
Post #906810
WayneS
Posted Tuesday, April 20, 2010 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 6,367, Visits: 8,226
SW_Lindsay (4/20/2010)
Nice and helpful article - Thanks. Just Curious...

To select eligible candidates in the first select you say

select * from @Candidates where MeetsEligibility = convert(bit,1);

Why do you convert the 1 into a bit? just saying 1 works. I know that the data type for MeetsEligibility is a bit and I'm just curious if there are efficiencies is converting explicitly like this or is it just a readability thing?


Steve

Avoiding an "Implicit conversion". The literal 1 is an integer, resulting in the underlying field being converted to an integer to do the match... if there is an index on this field, it won't be used. Converting the 1 to a bit avoids the implicit conversion, and allows use of an index if one is present.

Personally, I wish there were system variables @@True and @@False of datatype bit, set to 1/0 respectively.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #906816
SW_Lindsay
Posted Tuesday, April 20, 2010 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 1:40 PM
Points: 111, Visits: 280
Thanks for the reply and I would agree those constants would be a great additions!
Post #906821
Andy Lennon
Posted Tuesday, April 20, 2010 7:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386, Visits: 823
Michael Meierruth (4/20/2010)
Wayne,
You state what I always wanted to hear stated, i.e. that ROW_NUMBER()'s ORDER BY is not the same as the SELECT's ORDER BY. Yet, always - haven't encountered an exception yet, when you perform a SELECT the output is always in the order specified by ROW_NUMBER()'s ORDER BY without the presence of an ORDER BY in the SELECT.
How do you explain that?


i had the same question. is there a performance gain with the explicit conversion?

nvm, answered already
Post #906826
« Prev Topic | Next Topic »

Add to briefcase
31 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.