How to generate rownumbers using RANK(),DENSE_RANK(),ROW_NUMBER Functions

Hi All,

In different kind of situation we may required to generate rownumber on a table. Here I have explained three methods to get the rownumber on a table.

You can decide yourself to find the best approach based on your requirement.

Method: 1

USING RANK()FUNCTION:

Rank function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual row number as next rank.

Ex:

——–USING RANK()———

SELECT RANK() OVER (

ORDER BY Title) AS [RANK],

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

Method: 2

USING DENSE_RANK()FUNCTION:

DENSE_RANK () function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual rank number as next rank.

Ex:

——–DENSE_RANK()———

SELECT DENSE_RANK() OVER (

ORDER BY Title) AS DENSE_RANK,

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

Method: 3

USING ROW_NUMBER() FUNCTION:

ROW_NUMBER () function generates the rownumber based on the Order by column and it considers identity number for the given order by column.

EX:

——–ROW_NUMBER()———

SELECT ROW_NUMBER() OVER (

ORDER BY Title) AS RowNumber,

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

How to DROP,DELETE,SELECT,TRUNCATE all the tables in the Database

Hi All,

      I recently came across the situvation like how to delete all the tables in the database?

     It’s interesting because a single line of statement can do this entire action. I thought to share this with you all.

      The solution is here:

         EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

 when i start looking further on the same i also idenitfies the following options also,

      To delete all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

     To select all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “select * FROM ?”

     To Truncate all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

Note:  The procedure sp_MSforeachtable is hidden in MSSQL