SQL Server Ranking Functions

Question

SQL Server Ranking Functions

Viewing 15 posts - 16 through 30 (of 30 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Thanks Wayne, I had been meaning to investigate the ranking/window functions but hadn't gotten the time yet. Your article with the clear examples made it a cinch to understand the differences.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 2

WayneS (4/20/2010)
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.

You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx

Your implicit conversion comment is incorrect - as I understand it, the column is treated like any exact numeric from a query perspective. An index will be used if appropriate. That will primarily be determined by whether other non-columns are required (as in your example) and by the selectivity of the index.

Matt.

WayneS SSC Guru Points: 95460 More actions · Answer 3

matt stockham (4/20/2010)
You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx

I didn't know this... just tried it out, and it does work.

Your implicit conversion comment is incorrect - as I understand it, the column is treated like any exact numeric from a query perspective. An index will be used if appropriate. That will primarily be determined by whether other non-columns are required (as in your example) and by the selectivity of the index.
Matt.

I could have sworn that I had seen this happen before, but I can't duplicate it. Using 'TRUE', 'FALSE', 1, 0, or a 1/0 cast into an int variable all are producing an index seek for me, so I guess it isn't necessary.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

UMG Developer SSChampion Points: 13482 More actions · Answer 4

matt stockham (4/20/2010)
You can use 'TRUE' and 'FALSE' as quoted strings - http://msdn.microsoft.com/en-us/library/ms177603.aspx

Matt,

Thanks for some reason I never saw that, I think that will help make some code more readable.

SW_Lindsay SSCommitted Points: 1983 More actions · Answer 5

Thanks to all this is why I subscibe to learn something and I learned a lot from this article and subsequent discussion.

steve

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

I have seen Row number used in several occasions and always struggled to understand it. This article is very clear and practical. I expect to start using these functions soon. Thanks.

BradyB SSC Veteran Points: 241 More actions · Answer 7

Good article.

I've become very fond or ROW_NUMBER() lately, it's an efficient way of doing what I need to do for a large report.

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Very interesting article. I haven't used some of the fuctions, but I will try to use them now...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Wonderful article WayneS.. a great read.. taught me new things about Ranking... Thanks 🙂

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 10

Loved it! New features like ranking functions tend to be underutilized, since people prefer to stick with true and tried. Articles like this one, really clarify things and help us start using these features.

CyclingRabbit Say Hey Kid Points: 693 More actions · Answer 11

CyclingRabbit

Say Hey Kid

Points: 693

June 9, 2010 at 4:57 pm

#1178345

Great article. Well written. Thank you, sir.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

I'm a little late on the feedback after the formatting fix and I apologize for that. VERY nicely done, Wayne. This will help a lot of folks just getting into Windowing functions.

--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)

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Nice article, nice simple examples. Well done, Wayne.

Tom

WayneS SSC Guru Points: 95460 More actions · Answer 14

Thanks Tom, I'm glad that you liked it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

autoexcrement SSCertifiable Points: 5956 More actions · Answer 15

THANK YOU for such an excellent clarification of something that is very confusing to newbies like myself. Exceptionally well done. Much appreciated!

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura