Deleting Duplicate records in SQL Server

As I have mentioned in one of my previous tips that using ROW_NUMBER() we can get the serial number which is here. Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table. Lets say I have a Users Table which is having duplicate records. I need to delete the duplicate records which are having both the FirstName and LastName same. Table: UsersList   FirstName       LastName       PhoneNumber       Address ------------    ------------     ----------------    ------------- Rashmita        Devi                987554437           NULL Rashmita        Devi                446576578           NULL Adwin            Ratzz              222222222           NULL Charle            Hardwick         343534545           NULL Adwin            Ratzz              576767688           NULL Charle            Hardwick         877778777           NULL     So, with the help of ROW_NUMBER() all the duplicate records present in the above table can be removed easily.

The query will be composed this way:-   WITH TempUsers (FirstName,LastName, duplicateRecordCount) AS ( SELECT FirstName,LastName, ROW_NUMBER()OVER(PARTITION BY FirstName, LastName ORDER BYFirstName) AS duplicateRecordCount FROM dbo.Users ) DELETE FROM TempUsers WHERE duplicateRecordCount > 1 GO   Instead of TempUsers you can give any name. Because this is used only for Temporary purpose. After the execution of the above query, Users table will have the following records.   FirstName          LastName        PhoneNumber         Address ------------       ------------      ----------------      ------------- Rashmita           Devi                987554437              NULL Adwin               Ratzz              222222222             NULL Charle               Hardwick         877778777             NULL Note: Row_Number() function is present in SQL Server 2005 and in later version(s).

SQL SERVER – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer

I learned about SYNONYM feature in SQL Server 2005.

DBA have been referencing database objects in four part names. SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Create Synonyms :
USE AdventureWorks;GOCREATE SYNONYM MyLocationFOR AdventureWorks.Production.Location;GO
Use Synonyms :
USE AdventureWorks;GOSELECT TOP 5 *FROM MyLocation;GO
Drop Synonyms :
USE AdventureWorks;GODROP SYNONYM MyLocation;GO
Synonyms can be created on only following objects.

• Assembly (CLR) Stored Procedure
• Assembly (CLR) Table-valued Function
• Assembly (CLR) Scalar Function
• Assembly Aggregate (CLR) Aggregate Functions
• Replication-filter-procedure
• Extended Stored Procedure
• SQL Scalar Function
• SQL Table-valued Function
• SQL Inline-table-valued Function
• SQL Stored Procedure
• View
• Table (User-defined)

Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

Following is image demonstrates use of SYNONYMS.

An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following:
USE Users;GOCREATE SYNONYM ClientsFOR Offsite01.Production.dbo.Clients;GO
Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.

SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth

During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by suggesting following:

Temp Table and Table Variable — both are created in TempDB and not in memory.

Let us prove this concept by running the following T-SQL script.

/* Check the difference between Temp Table and Memory Tables */
-- Get Current Session ID

SELECT @@SPID AS Current_SessionID

-- Check the space usage in page files

SELECT user_objects_alloc_page_count 

   FROM   sys.dm_db_session_space_usage 

   WHERE session_id = 

   (SELECT @@SPID )GO

-- Create Temp Table and insert three thousand rows

   CREATE TABLE #TempTable 

   (Col1 INT)

   INSERT INTO #TempTable (Col1)

   SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)FROM sys.all_objects a 

   CROSS JOIN sys.all_objects b
  GO

-- Check the space usage in page files

SELECT user_objects_alloc_page_count 

    FROM   sys.dm_db_session_space_usage

   WHERE session_id =(SELECT @@SPID )

    GO

 -- Create Table Variable and insert three thousand rows

  DECLARE @temp TABLE(Col1 INT)

  INSERT INTO @temp (Col1)SELECT TOP 3000 ROW_NUMBER() 

  OVER(ORDER BY a.name)FROM sys.all_objects a 

  CROSS JOIN sys.all_objects b
  GO

 -- Check the space usage in page files

  SELECT user_objects_alloc_page_count 

  FROM sys.dm_db_session_space_usage

  WHERE session_id = (SELECT @@SPID )

  GO

-- Clean up

DROP TABLE #TempTableGO

Let us see the resultset. It is very clear that the size of the table variable and temp table is the
same and created in TempDb.

Have you ever heard of this misconception? Do you know any other method to prove that both
 Temp Table and TableVariable are created in TempDB.