Difference between ROW_NUMBER, RANK and DENSE_RANK

What is the Difference between ROW_NUMBER, RANK and DENSE_RANK? Which one to use?
This is very common question in the minds of SQL newbie's.
Lets take 1 simple example to understand the difference between 3.

First lets create some sample data :

-- create table CREATE TABLE Salaries ( Names VARCHAR(1), SalarY INT ) GO -- insert data INSERT INTO Salaries SELECT 'A',5000 UNION ALL SELECT 'B',5000 UNION ALL SELECT 'C',3000 UNION ALL SELECT 'D',4000 UNION ALL SELECT 'E',6000 UNION ALL SELECT 'F',10000 GO -- Test the data SELECT Names, Salary FROM Salaries

Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I'll write a query like this :

SELECT names , salary ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER ,rank () OVER (ORDER BY salary DESC) as RANK ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK FROM salaries

>>Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D.
Row_number assign different number to them.
Rank and Dense_rank both assign same rank to A and B.
But interesting thing is what RANK and DENSE_RANK assign to next row?
Rank assign 5 to the next row, while dense_rank assign 4.

The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks. The RANK function does not always return consecutive integers. The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

SQL Paging in SQL Server 2011 using SQL ORDER BY OFFSET and FETCH NEXT

Today I am going to talk about one new feature that is been introduced in the SQL Server Denali. And that is: one interesting enhancement in ORDER BY clause. Now with ORDER BY clause you can also specify the OFFSET and FETCH options.
From Books Online of Denali:
OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.
FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.
This is how the new syntax of ORDER BY clause looks like in SQL Server Denali:
------------------------------------------------------------------------------------------------------
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,...n ]
[ ]
::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}
------------------------------------------------------------------------------------------------------
Take a look at following example:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
GO
------------------------------------------------------------------------------------------------------

Output:

In the example I wrote 5 as OFFSET. You can see in the output 1st five rows are skipped and we got VendorIds starting from 6. Here SQL Server 1st orders the data on the Column specified in ORDER BY clause(i.e. VendorID). The next query uses the clause OFFSET 5 ROWS to skip the first5 rows and return all remaining rows.
Now lets see the example of FETCH:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
FETCH NEXT 3 ROWS ONLY
GO
------------------------------------------------------------------------------------------------------

Output:

As you can SQL Server has fetched only 3 rows and that also after skipping 1st five rows. That is because I specified 5 as OFFSET and 3 as FETCH NEXT. Here FETCH NEXT 3 ROWS ONLY to limit the rows returned to 3 rows from the sorted result set.
Also:
1. offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope. That is, it cannot be correlated with the outer query.
2. ROW and ROWS are synonyms and are provided for ANSI compatibility.
3. In query execution plans, the offset row count value is displayed in the Offset attribute of the TOP query operator.
You can find all this information and more on: ORDER BY Clause (Transact-SQL)

SQLSERVER : To show current datetime

3 ways to get current datetime in sql:

select Getdate()
select Current_timestamp
select {fn now()}

SQLSERVER : Get all Foreign key relationships of all tables in a database

Show all Foreign key relationships of all tables in a database:

SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'


SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )