How can I remove duplicate rows in SQL Server?

This article will use a table variable for reference with sample data.

Query:

DECLARE @Employee TABLE(Id INT IDENTITY(1,1), [EmployeeName] VARCHAR(100))

INSERT INTO @Employee ([EmployeeName])
SELECT 'Vikram' AS [EmployeeName]
UNION ALL
SELECT 'Vijay' AS [EmployeeName]
UNION ALL
SELECT 'Vinod' AS [EmployeeName]
UNION ALL
SELECT 'Vijay' AS [EmployeeName]

Query to get duplicate records:

SELECT		T1.Id
			, T1.EmployeeName
FROM		@Employee T1
OUTER APPLY (
				SELECT	T.Id
						, T.EmployeeName, 
						ROW_NUMBER() OVER (PARTITION BY T.EmployeeName ORDER BY T.EmployeeName ASC) AS Sno 
				FROM	@Employee T
			) AS T2
			 
WHERE		T1.Id = T2.Id AND T2.Sno > 1

Query to get unique records:

SELECT		T1.Id
			, T1.EmployeeName
FROM		@Employee T1
OUTER APPLY (
				SELECT	T.Id
						, T.EmployeeName, 
						ROW_NUMBER() OVER (PARTITION BY T.EmployeeName ORDER BY T.EmployeeName ASC) AS Sno 
				FROM	@Employee T
			) AS T2
			 
WHERE		T1.Id = T2.Id AND T2.Sno = 1

Query to delete the duplicate records from the table:

DELETE		T1 
FROM		@Employee T1
OUTER APPLY (
				SELECT	T.Id
						, T.EmployeeName, 
						ROW_NUMBER() OVER (PARTITION BY T.EmployeeName ORDER BY T.EmployeeName ASC) AS Sno 
				FROM	@Employee T
			) AS T2
			 
WHERE		T1.Id = T2.Id AND T2.Sno > 1

SELECT * FROM @Employee AS T