L' Eomot Inversé (1/7/2013)
This would have been a good question and easy to get right if the schemaname had been spelt "Sales" instead of "SALES". However, I happened to remember that the database database collation for AdventureWorks2008R2 is Case Sensitive. Or at least that's what it is when installed out of the box on my laptop. My server default collation is NOT case sensitive, so it wasn't picking up case sensitivity from my server default, it's in AdventureWorks as supplied by MS. So I answered "neither" instead of "rank", because both queries will fail with an invalid oject name error.
Apparently the spelling was a mistake, since that was not the "correct" answer. Not at all an error I would expect to see in one of your questions, Ron! I can't see how the queries can have been tested.
Tom, AdventureWorks is available in different flavors. Apparently, you downloaded and installed the case sensitive version, but there is a case insensitive version as well.
I think the question could have been shortened to simply present two queries, one with RANK() and one with DENSE_RANK(), and then ask which of the two could have produced the given output. That would have made it more clear that the reader should focus on the difference between those two functions.
That being said - I do like the question! Thanks, Ron!
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis