If two or more rows tie for a rank, each tied rows receives the same rank. Dense_Rank() will not leave gaps between groups. But we need to use ORDER BY clause so that the numbers are assigned to the specific order.
CREATE TABLE #TEMP (NAMES VARCHAR(100)) INSERT INTO #TEMP SELECT 'Hitesh' UNION ALL SELECT 'Abhimanyu' UNION ALL SELECT 'Priyanka' UNION ALL SELECT 'Dev' UNION ALL SELECT 'Dev' UNION ALL SELECT 'Bhawesh' SELECT NAMES, Dense_Rank() OVER (ORDER BY NAMES) AS SNO FROM #TEMP drop table #TEMP
Result :