Difference between CTE and Temp Table and Table Variable  in SQL Server

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

It is divided into two Local temp tables and Global Temp Table, 

Local Temp table  are only available to the SQL Server session or connection (means single user) that created the tables. 

Global temp tables are available to all SQL Server sessions or connections (means all the user). 

These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed.

cases where you need transaction rollback support.

2. CTE - Common table Expression is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped automatically once it comes out of batch. This is also created in the Tempdb database but not the memory.You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table.
If the resultset is small, the table variable is always the optimum choice.

Remove duplicates in SQL Server when tables have no primary key

Remove duplicates in SQL Server when tables have no primary key

I have table called CricketerDetails, and the table has no primary or unique key.
Below i have mentioned how to remove duplicate items with query.

1. Procedure to remove the Duplicate rows with query is

Query

WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY FirstName,LastName,HighestScore ORDER BY FirstName) As RowNumber,* FROM CricketerDetails
)
DELETE FROM tempTable where RowNumber >1

SELECT * FROM CricketerDetails order by FirstName asc