Excel 2007 Linked Server

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from 2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )
And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'.

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
For Creating Linked Server from Management Studio:
Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No 
Again remember if you have column names in Excel then HDR=Yes. 

- Mangal Pardeshi.

Calculating Running Totals in SQL

 

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query.  So instead of using the technique I used in this post you should use technique mentioned in my new post -  How to Calculating Running Totals

In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -

• Running Total for all records.
• Running Total on each Group of Records.
• Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table CREATE TABLE Sales ( ShipId INT, OrderId INT, ShipDate DateTime, Quantity INT ) GO -- Load Sample Data INSERT INTO Sales SELECT 1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT 2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT 3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT 4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT 5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT 6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT 7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT 8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT 9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT 10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT 11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.ShipId >= B.ShipId GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.

Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.ShipId >= B.ShipId AND A.OrderId = B.OrderId GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.

Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.shipDate >= B.Shipdate AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0) GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity ORDER BY A.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Hopefully these examples will help you understand different methods that can be used to calculate running totals.

- Mangal Pardeshi

RPAD and LPAD Functions in Sql Server

Edit: If you are using SQL Server 2012 or 2014 you may like to see my new post on this topic: LPAD and RPAD Functions in SQL Server

In Sql Server we don’t have RPAD and LPAD functions as they are in Oracle. But it is not that difficult to implement the same thing in Sql Server. With the help of RIGHT and LEFT functions we can achieve that easily. Let’s see how…

First create this table:
CREATE TABLE Temp(Id INT)
GO

Now insert the table Temp with some sample data:
INSERT INTO Temp SELECT
1 UNION SELECT
2 UNION SELECT
12 UNION SELECT
123 UNION SELECT
1234 UNION SELECT
12345
GO

And here is our RPAD and LPAD query

SELECT ID
,RIGHT( '00000' + CONVERT(VARCHAR(5), Id), 5) as LPAD_Example
,LEFT( CONVERT (VARCHAR(5), Id)+'00000' , 5) as RAPD_Example
FROM Temp

--END--

- Mangal Pardeshi

How to delete the Duplicate Rows from SQL Server table

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID	NAMES	CODE
1	Mangal	101
1	Mangal	101
1	Mangal	101
2	Ricky	102
3	Brian	103
4	shiv	104
4	Shiv	104
5	Kunal	105
5	Kunal	105
6	Kate	106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID	NAMES	CODE
1	Mangal	101
2	Ricky	102
3	Brian	103
4	Shiv	104
5	Kunal	105
6	Kate	106

Create And Load Sample Data -

-- Create Sample table CREATE TABLE Temp ( Id INT, Names VARCHAR(100), Code INT ) GO -- Load sample data INSERT INTO Temp SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 2, 'Ricky', 134  UNION ALL SELECT 3, 'Brian', 435 UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 6, 'Kate', 611 GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.


To Find Duplicate rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) SELECT DISTINCT ID, Names, Code FROM Cte WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) DELETE FROM cte WHERE Seq > 1

- Mangal Pardeshi

Removing Time from DateTime column in Sql Server 2005

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

• Format: mm/dd/yy
  SELECT CONVERT( Varchar(12), GetDate(),1)
  Output: 12/27/08

• Format: mm/dd/yyyy
  SELECT CONVERT(Varchar(12),GetDate(),101)
  Output: 12/27/2008

• Format: yy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),2)
  Output: 08.12.27

• Format: yyyy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),102)
  Output: 2008.12.27

• Format: dd/mm/yy
  SELECT CONVERT(Varchar(12),GetDate(), 3)
  Output: 27/12/08

• Format: dd/mm/yyyy
  SELECT CONVERT(Varchar(12),GetDate(), 103)
  Output: 27/12/2008

• Format: dd.mm.yy
  SELECT CONVERT(Varchar(12),GetDate(), 4)
  Output: 27.12.08

• Format: dd.mm.yyyy
  SELECT CONVERT(Varchar(12),GetDate(),104)
  Output: 27.12.2008

• Format: dd-mm-yy
  SELECT CONVERT(Varchar(12),GetDate(), 5)
  Output: 27-12-08

• Format: dd-mm-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),105)
  Output: 27-12-2008

• Format: dd mon yy
  SELECT CONVERT(Varchar(12),GetDate(), 6)
  Output: 27 Dec 08

• Format: dd mon yyyy
  SELECT CONVERT(Varchar(12),GetDate(),106)
  Output: 27 Dec 2008

• Format: mon dd, yy
  SELECT CONVERT(Varchar(12),GetDate(),7)
  Output: Dec 27, 08

• Format: mon dd, yyyy
  SELECT CONVERT(Varchar(12),GetDate(),107)
  Output: Dec 27, 2008

• Format: mm-dd-yy
  SELECT CONVERT(Varchar(12),GetDate(),10)
  Output: 12-27-08

• Format: mm-dd-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),110)
  Output: 12-27-2008

• Format: yy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),11)
  Output: 08/12/27

• Format: yyyy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),111)
  Output: 2008/12/27

• Format: yymmdd
  SELECT CONVERT(Varchar(12),GetDate(),12)
  Output: 081227

• Format: yyyymmdd
  SELECT CONVERT(Varchar(12),GetDate(),112)
  Output: 20081227
  
  Well I already wrote one blog entry on how to convert date in required format.
  Date Formats In SQL SERVER.
  
  For more on CONVERT see
  CAST and CONVERT (Transact-SQL) Books Online
  
  - Mangal Pardeshi.

How To Enable Xp_CmdShell in Sql Server?

Today I will show you how to enable the Xp_CmdShell extended stored procedure in Sql Server 2005 and 2008.
Well normal error message you'll get when Xp_CmdShell is not enabled on your Sql Server and you to try execute some Windows commands using Xp_CmdShell is

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


You can enable the Xp_CmdShell using 2 ways, either by executing T-sql statements or from
"Surface Area Configuration Manager". We'll see both of them.


A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

B. Enable the Xp_CmdShell from "Surface Area Configuration Manager."

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".



- Mangal Pardeshi.

Check Constraint to allow alphabets only in Sql Server

How to create a check constraint to allow alphabets i.e. A-Z characters only or alphabets + numbers with no special characters in Sql Server?
I think many of us have faced this problem sometimes. Here is the scenario, you have column with varchar/char datatype and you don’t want user to enter any numbers or any Special characters like @, #, $, *. And sometimes characters + numbers with no special characters. And we don’t know any efficient way to handle and we end up writing a long NOT LIKE check constraint.
So let’s see how to write a simple CHECK CONSTARINT for allowing only alphabets and numbers.

First I’ll show you how to write a WHERE conditions for such different cases, so you will get an idea.
Let’s create 1 temporary table and populate it with some data

CREATE TABLE #temp
(ID varchar(50))
GO

INSERT INTO #TEMP SELECT
'Mangal' UNION SELECT
'Mangal Pardeshi' UNION SELECT
'Mangal19' UNION SELECT
'19' UNION SELECT
'@Mangal***' UNION SELECT
'@#$%^&*' UNION SELECT
'Mangal19**'
GO

Now our sample table #temp is ready.
SELECT * FROM #TEMP

-- Output --

@#$%^&*
@Mangal***
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
--------------

Case 1 : We just want to get rows with only Alphabets (A-Z characters) with no numbers and Special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z]%'

-- Output --
Mangal
--------------

Now if you notice here ‘Mangal Pardeshi’ din’t get selected as it contains a SPACE in between. To avoid that we need include a single space in our condtion '%[^A-Z]%'. Now we will put '%[^A-Z ]%'.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z ]%'

-- Output --
Mangal
Mangal Pardeshi
--------------

Case 2 : Now we want select all the rows with alphabets + numbers or any of the both with no special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9 ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
--------------
Case 3 : Now we want characters + numbers or characters or numbers or a special character *. So I just add a * in our condition.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9* ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
---------------
Same way you can inculde any special character. You just need to add that character into our original condition.

Now writing a Check Constraint is very simple.

For allowing Alphabets only

ALTER TABLE TableName ADD CONSTRAINT Only_Characters CHECK ColumnName NOT LIKE '%[^A-Z ]%'
**Remember to add extra space.

For alphabets + Numbers

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numebrs CHECK ColumnName NOT LIKE '%[^A-Z0-9 ]%'


Hopefuly this post will help many of you.


- Mangal Pardeshi.

How to create a time dimension in SQL Server

In this post I’ll help you creating a Time Dimesnion as it is very much required if you are working on Sql Server Analysis Services. Until now you need to write a complex Sql procedure running into loops for creating a large Time Dimension. But with Sql Server 2005 and Common Table Expressions (CTE) it is very easy to create your own Time Dimension. A time Dimension is also called as Calendar Table by many.
When I started working on SSAS 2005 I also searched for a good Time Dimension, but now I have created my own Time Dimension with following Script.

Following script creates a table named TimeDiemension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.
Even though following query may look little big but believe me, it's nothing but a simple SELECT statement with CTE.
------- TimeDimension -------

WITH Mangal as
(
SELECT Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = '2020-12-31' --End date
)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension -- Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

---- Script Ends Here ----
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
- Mangal Pardeshi.

Saving the Result of a query to a Text File in Sql Server

After being a part of the Sql Server MSDN forum for a quite a long time, One question I answered many time is “How to save the output of query to a text file in Sql Server?”
So thought of sharing a simple solution with you using BCP utility.

A simple query to save output of query to a text file is goes like this –

EXEC master..XP_CmdShell 'BCP "SELECT * FROM Database.dbo.TableName" queryout "c:\Mangal.txt" -c -T'

If you execute the above query and get the following error –

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Then first you need to enable the xp_cmdshell procedure. To enable the xp_cmdshell execute -

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure Xp_CmdShell,1
GO
RECONFIGURE WITH OVERRIDE
GO

Again back to BCP, Here
-c : Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (new line character) as the row terminator.

-T : Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can save the file to txt, xls, dbf, xml formats also.

There are so many other options available with BCP command.
e.g.
With –U and –P you can provide the username and password when you are not using the Windows Authentication (Trusted Connection).

For more on BCP utility you can read from Microsoft’s Books Online.
BCP Utility Books Online


Idea for this post was just to provide a simple solution on how to save the result of a query to a file?.

Well there is another way of directly sending the result of query to a file is by setting the “Result To Text” in management studio.
For that do –
Open the Management Studio.
In menu bar click to “Query” >> “Result to” and then select “Result To Text.”

Hopefully many of you find this post helpful.

- Mangal

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

Mangal Pardeshi

SQL MVP