SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

http://msdn.microsoft.com/en-us/library/ms189798.aspx
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.


Good explanation of ranking function

Good example of ranking function

SQL 2005 - Outer apply, Cross apply

SQL - using apply : Outer apply, Cross apply and example

There are two forms of APPLY: CROSS APPLY and OUTER APPLY.

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.

OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.


Example: We have EmployeeMaster and sales table we want know which through they year the sales person achieve the highest sales and which month

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)


Create Table Sales
(
SalesId int identity(1,1),
EmpId int,
SalesAmount int,
Month varchar(3)
)
Go


Insert into Sales values(1,32000,'Jan')
Insert into Sales values(1,12000,'Feb')
Insert into Sales values(1,42000,'Mar')
Insert into Sales values(1,32000,'Apr')
Insert into Sales values(1,52000,'May')
Insert into Sales values(1,32000,'Jun')
Insert into Sales values(2,2000,'Jan')
Insert into Sales values(2,7000,'Feb')
Insert into Sales values(2,15600,'Mar')
Insert into Sales values(2,9000,'Apr')
Insert into Sales values(2,7500,'May')
Insert into Sales values(2,560,'Jun')

set statistics io on

Normally we will code as

Select E.EmpName,
(select top 1 SalesAmount from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as SalesAmount,
(select top 1 month from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as month1
from EmployeeMaster E

Table 'Sales'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


In SQL 2005, we can use OUTER Apply

Select E.EmpName, A.SalesAmount,[Month] from EmployeeMaster E
OUTER APPLY
(
Select top 1 SalesAmount,[Month] from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc
) A

Table 'Sales'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Result

EmpName SalesAmount Month
-------------------------------------------------- ----------- -----
James 52000 May
May 15600 Mar
Henry NULL NULL


set statistics io off

Refer to above,

1. The query cost for first statement (66%) is double then second statement (34%)
which using OUTER APPLY.
(Highlight the above 2 query in query analyzer and click on Display Estimation Execution Plan)

2. Page Read for Sales (Using set statistics io on) , the first statement is 6 and second
statement is 3. Page read for first statement is double that second statement



Notes:

To use APPLY, the database compatibility level must be at least 90.

Reference:

Using APPLY

Cross Apply and Outer Apply in SQL Server 2005

SQL 2005 execute as

I need to login as user when i want to test their permission against the database object and most of time I forgot/ never record user password and need request password from them

with SQL 2005, I don't need relogin, I can do this as below

login as sa
execute as user='user'
--select from login table with "user" permission
select * from login

revert
--revert back to sa