Row_Number, Rank, Dense_Rank, Partition By

Below script shows demo of all of the above


SELECT name, type_desc  
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords  
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName  
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType  
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType  
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName  
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName  
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1  
FROM sys.objects  
ORDER BY name