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 :

Dense_Rank