#sql301

Last time, we discussed three methods for inserting data into a destination table that has a recursive relationship.  In this article we're going to cover how to insert data into that table without vi0lating the foreign key constraint by using a WHILE EXISTS loop.

If you want to follow along with the examples, you're going to need my Staging table and my Destination table.

CREATE TABLE Staging (     AccountID UNIQUEIDENTIFIER NOT NULL     , Name NVARCHAR(160) NOT NULL     , ParentAccountId UNIQUEIDENTIFIER NULL     , ParentName NVARCHAR(160) NULL     , BatchID INT     , ConversionStatus INT ) --truncate table AccountBaseDestination CREATE TABLE Destination (     AccountID UNIQUEIDENTIFIER NOT NULL     , Name NVARCHAR(160) NOT NULL     , ParentAccountId UNIQUEIDENTIFIER NULL     , ParentName NVARCHAR(160) NULL     , CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED ([AccountId] ASC) ) ALTER TABLE Destination  ADD  CONSTRAINT fk_Destination_Destination__ParentAccountID_AccountID      FOREIGN KEY(ParentAccountId) REFERENCES Destination ([AccountId])

Now let's put in some simple hierarchical data into our staging table. Basically I insert a row, grab the GUID created, and use that for the parentAccountID for the next row. That way each child will have the right GUID assigned for it's parent. Otherwise, the demo breaks!

--demo: several layers deep --first: empty the staging table TRUNCATE TABLE Staging --we need temp storage for an inserted AccountID to use as a parent account id DECLARE @ParentAccountID UNIQUEIDENTIFIER --next we need a table variable to trap inserted uniqueidentifers DECLARE @InsertedAccount TABLE ( AccountID UNIQUEIDENTIFIER ) --Let's insert a root node, and output the inserted Account ID INSERT INTO Staging (AccountID, Name) OUTPUT inserted.AccountID INTO @InsertedAccount SELECT NEWID() as accountID , 'Root Node' as Name --I know we'll just have one for this demo, so TOP 1 SELECT TOP 1 @ParentAccountID = AccountID FROM @InsertedAccount --clear out the @InsertedAccount before the next insert --otherwise you'll have Multiple ParentAccountIDs to deal with. DELETE FROM @InsertedAccount INSERT INTO Staging (AccountID, Name, ParentAccountID, parentName) OUTPUT inserted.AccountID INTO @InsertedAccount SELECT NEWID() as accountID , 'first child' as name , @ParentAccountID as ParentAccountID , 'Root Node' as parentName --pull out the accountid again SELECT TOP 1 @ParentAccountID = AccountID FROM @InsertedAccount DELETE FROM @InsertedAccount INSERT INTO Staging (AccountID, Name, ParentAccountID, parentName) OUTPUT inserted.AccountID INTO @InsertedAccount SELECT NEWID() as accountID , 'grandchild' as name , @ParentAccountID as ParentAccountID , 'first child' as parentName --pull out the accountid again SELECT TOP 1 @ParentAccountID = AccountID FROM @InsertedAccount DELETE FROM @InsertedAccount INSERT INTO Staging (AccountID, Name, ParentAccountID, parentName) OUTPUT inserted.AccountID INTO @InsertedAccount SELECT NEWID() as accountID , 'great grandchild' as name , @ParentAccountID as ParentAccountID , 'grandchild' as parentName --don't have to clear the table variable this time, we're done. SELECT * FROM Staging

In order to use a WHILE EXISTS loop, you have to be able to define the records you want to work on. For us we want to insert any records from Staging that either have their ParentAccountID value already in the Destination table in the AccountID column, or do not have a ParentAccountID value (NULL).

Here's my query that shows me the records we could insert.

SELECT * FROM Staging staging LEFT JOIN Destination destInsertedCheck on staging.AccountID = destInsertedCheck.AccountID LEFT JOIN Destination destParentInsertedCheck on staging.parentAccountID = destParentInsertedCheck.AccountID WHERE destInsertedCheck.accountID IS NULL --not already inserted and (destParentInsertedCheck.AccountID IS NOT NULL OR staging.parentAccountID IS NULL)--parent is defined or NULL

Notice only our Root Node record is shown. The next thing we need for a WHILE EXISTS loop is the query to do the work, in our case we need to insert these records into the Destination table.

INSERT INTO Destination (AccountID, Name, ParentAccountID, ParentName) SELECT staging.* FROM Staging staging LEFT JOIN Destination destInsertedCheck on staging.AccountID = destInsertedCheck.AccountID LEFT JOIN Destination destParentInsertedCheck on staging.parentAccountID = destParentInsertedCheck.AccountID WHERE destInsertedCheck.accountID IS NULL --not already inserted and (destParentInsertedCheck.AccountID IS NOT NULL OR staging.parentAccountID IS NULL) --parent is defined or NULL

Now, we just need to put it all together. I would suggest changing the SELECT * to SELECT somecolumn in the WHILE EXISTS check. There's no need to pull back the whole row, one non-nullable column would be fine. Even SELECT 1 would be fine.

Take a look at the full code now.

WHILE EXISTS ( SELECT staging.AccountID FROM Staging staging LEFT JOIN Destination destInsertedCheck on staging.AccountID = destInsertedCheck.AccountID LEFT JOIN Destination destParentInsertedCheck on staging.parentAccountID = destParentInsertedCheck.AccountID WHERE destInsertedCheck.accountID IS NULL --not already inserted and (destParentInsertedCheck.AccountID IS NOT NULL OR staging.parentAccountID IS NULL)--parent is defined or NULL ) BEGIN INSERT INTO Destination (AccountID, Name, ParentAccountID, ParentName) SELECT staging.* FROM Staging staging LEFT JOIN Destination destInsertedCheck on staging.AccountID = destInsertedCheck.AccountID LEFT JOIN Destination destParentInsertedCheck on staging.parentAccountID = destParentInsertedCheck.AccountID WHERE destInsertedCheck.accountID IS NULL --not already inserted and (destParentInsertedCheck.AccountID IS NOT NULL OR staging.parentAccountID IS NULL) --parent is defined or NULL END

So now, if you take a look at your Source and Destination tables, you'll see all the records from your source are in the destination, and you didn't get any foreign key contraint violations like we saw in the last article. Pretty easy, right? Next time, I'll show you how you can use a CTE to define what "level" a record is on, and process them from root level to leaf level. Until then, if you have any questions, let me know! I'm here to help.

Earlier this week the question came  up on how can we insert data into a table that has a hierarchical data structure.  If you're not familiar with a hierarchical data structures, here's a quick explanation.

Consider the staging table to the right.  This table is used to load new accounts into our system.  Each account has a unique identifier and a name.  Since one company can own another, we have to be able to show that in our system.  In this system we have parent companies, and children companies.  The children companies' accounts are related to a parent account.

So in our destination table, this relationship would be enforced by adding a primary key on the AccountID, and then adding a foreign key on ParentAccountID to relate it to AccountID.

Fairly straightforward, right?

Well, when you're pushing thousands of rows into the table at a time, you can't control the order of the inserts.  As soon as you try to insert a record that has a ParentAccountID that hasn't yet been defined with a row using that AccountID, you're going to get an error message.

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "fk_AccountBaseDestination_AccountBaseDestination__ParentAccountID_AccountID". The conflict occurred in database "RV_DataConversion_Stage", table "dbo.AccountBaseDestination", column 'AccountID'. The statement has been terminated.

So now we're to the problem at hand.  How can we make sure we don't try to insert a row with a parentID that's not defined?

Use A Cursor

It was proposed we use a cursor to run through the rows, grab a parent and all it's children, and insert the parent first, then insert all it's children.  Then return to the beginning of the set and repeat.  While that would avoid the foreign key violation, it would cost a lot of I/O (disk resources) to do it that way.

BTW: while this option was being presented, the presenter said we won't use cursors to do this.  But at the same time he was advocating a row-by-row operation.

If you've known me for any length of time, you know my stance on row-by-row operations in SQL.  I'm not a fan.

  Insert all the Records, Then Update the ParentAccountID

Another proposal was to insert all the records, and not pass in the ParentAccountID.  Then we could run an update statement against the Destination table where that parent had been defined.  If there were any rows that needed a ParentAccountID where that AccountID was not defined, we simply wouldn't update that row.

Basically the update would be an update with an INNER JOIN, so we'd avoid the violation that way.

Not bad, but if the destination table was being used, users might see a ton of new accounts at the root level, not at whatever level they're supposed to be at.  You also would require locks on the destination table during the update that might escalate from row locks, to page locks, to table locks.  You'd have to test a load where you were adding 25 - 50% new records.  So if your destination table had 100 records, test a load of 25 new records.  Then test a load of 50 records.  During the load watch what locks are being taken?

If you see it escalate to a table lock, you may not want to do that to your users, if they are on the destination table regularly.

Use a CTE

A rather interesting idea that came up was to write a CTE that would select the staging data and add on a LevelNumber column.  This column would indicate what level in the hierarchy the account was on.  For example, level 1 would be the root node, level 2 would be children of accounts on level 1, level 3 would be children of level 2, and so on.

Once we know what levels each account is on, we could load all the level 1 accounts, then all the level 2 accounts, etc.  Not a bad idea.  Especially if you have your insert has to go through an SSIS package, or web service to do the actual insert into the database, you could be sure you're only adding records in a integrity-safe way!

This is the method we ended up going with, due to the fact the insert was going through a webservice.  But... there is another way.

WHILE EXISTS Loop

The last method we discussed was first, we insert all the root nodes (level 1 from the previous image), and then we use a WHILE EXISTS loop to insert children where their parent was defined.  The end result is very similar to the STE solution, in fact the costs are almost identical.  You have to "loop" through the code once for every level.  The difference here is in order to use the WHILE EXISTS method, you have to be able to run the actual INSERT statements.  And that's why we didn't chose this method this time.

In my next training article I'm going to go through this method, so you can see the design pattern.

Conclusion

In the end we went with the method that used the least impact on the server, hits all of the requirements, and gets the job done with the least effort.  There's always a lot of back and forth in these design meetings.  It's all a part of the process of designing good code.  You can't get too invested in "my way is best, I won't do it any other way."  That's just counter-productive.

You have to open your ideas up to scrutiny and participate in the give and take.  In the end, you have to work with these people long after the process has been implemented.  You're going to have to go through another design meeting.  You don't want to make the next one even harder because you had to have your way...right?

This is the last article for my upcoming design patterns session. Today we're going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I'm wrong for not understanding cursors, let me say this: there is a time and a place for everything, even cursors (think running totals). The problem is I still see them doing row-by-row operations in SQL. And SQL is not optimized for row-by-row. It is optimized for set-based operations.

If you can rethink your T-SQL logic and consider the whole set of data you'll be working on, rather than what you have to do to each row, you'll find you're rewarded by the SQL Engine. Your results come back in record time!

Let's look at an example I created to illustrate a row-by-row operation. This cursor takes in a column containing a user's full name, and breaks it into first and last name using a space as the break point.

If you want to follow along, and you have my OriginalTroubledTable, use the following code to set up the NameSplitTable.

IF OBJECT_ID('adventureworks.dbo.NameSplitTable') IS NOT NULL DROP TABLE NameSplitTable GO CREATE TABLE [dbo].[NameSplitTable]( [NameID] INT IDENTITY(1,1) , [FullName] VARCHAR(100) NULL , [FirstName] [varchar](50) NULL , [LastName] [varchar](50) NULL ) ON [PRIMARY] GO --I want to have more than 10k rows so repeat inserts until we get more! WHILE (SELECT COUNT(*) from [NameSplitTable]) < 10000 BEGIN INSERT INTO [NameSplitTable] ([FullName]) SELECT firstname + ' ' + LastName FROM OriginalTroubledTable END

If we were doing a single row, the following code wouldn't be that bad.

DECLARE @fullname varchar(100) DECLARE @NameID INT DECLARE @splitPos INT SELECT TOP 1 @NameID = NameID , @fullName = FullName FROM [NameSplitTable] SET @splitPos = CHARINDEX(' ', @fullname) IF(@splitPos > 0) BEGIN UPDATE [NameSplitTable] SET FirstName = SUBSTRING(@fullName, 1, @splitPos) , LastName = SUBSTRING(@fullName, @splitPos, LEN(@fullName)) WHERE NameID = @NameID END

Look at the execution plan, the cost of the UPDATE isn't horrible. In my test environment the execution took less than one second.

The real cost of this query comes in when you run this code once for every row of the table. In my test case, I have 11,760 rows. That means that this table scan is going to scan through the table 11,760 times. That will add up quickly! Let's see the Cursor version of the code.

DECLARE @fullname varchar(100) DECLARE @NameID INT DECLARE @splitPos INT DECLARE FullNames CURSOR FOR SELECT NameID, FullName FROM [NameSplitTable]; OPEN FullNames; FETCH NEXT FROM FullNames INTO @NameID, @fullname; WHILE @@FETCH_STATUS = 0 BEGIN SET @splitPos = CHARINDEX(' ', @fullname) IF(@splitPos > 0) BEGIN UPDATE [NameSplitTable] SET FirstName = SUBSTRING(@fullName, 1, @splitPos) , LastName = SUBSTRING(@fullName, @splitPos, LEN(@fullName)) WHERE NameID = @NameID END FETCH NEXT FROM FullNames INTO @NameID, @fullname; END CLOSE FullNames DEALLOCATE FullNames

You can run this on your machine if you have a lot of patience, but I'm going to run it for 60 seconds, and see how many rows I can update in 60 seconds. I was able to update 5,734 rows. And that's the big problem with cursors, each operation could be very quick, but the cumulative effect of running the code so many times adds up!

In this case it's the cumulative effect of doing that many table scans and table updates to do the work. How much faster would it be if I could update all the rows at once?

Let's reset our test table and find out.

update [NameSplitTable] set FirstName = NULL, LastName = NULL

First, in order to be able to do this string manipulation across multiple rows we're going to need a table value function to do the same substring we did in the cursor.

CREATE FUNCTION tvf_splitNames ( @fullName VARCHAR(100) ) RETURNS @output TABLE ( [FirstName] [varchar](50) NULL , [LastName] [varchar](50) NULL ) AS BEGIN DECLARE @splitPos INT SET @splitPos = CHARINDEX(' ', @fullname) IF(@splitPos > 0) INSERT INTO @output SELECT SUBSTRING(@fullName, 1, @splitPos) FirstName , SUBSTRING(@fullName, @splitPos, LEN(@fullName)) AS LastName ELSE INSERT INTO @output SELECT @fullname as FirstName, NULL as LastName RETURN END

Now that we have a table value function we can use the CROSS APPLY technique to apply this function to all the rows in our NameSplitTable. We simply map in The full name, and pull out the resulting first and last names.

SELECT nst.FullName , tvf.FirstName , tvf.LastName FROM [NameSplitTable] nst CROSS APPLY tvf_splitNames (nst.FullName) tvf

Yes, this is only a select, but I wanted to show you how 84% cost to the table scan was changed once we move from row-by-row to Set based processing.

We reduced the cost of the table scan, by adding a table valued function and a nested loop. Yes, this is apples and oranges to the original function, but run the code. We were able to select back 11,760 rows, getting their first and last names in 9 seconds.

The cursor took 2:35 to actually update all the rows.

UPDATE nst SET nst.FirstName = tvf.firstName , nst.LastName = tvf.LastName FROM [NameSplitTable] nst CROSS APPLY tvf_splitNames (nst.FullName) tvf

The cross apply updated it in 1 second.

Now that is apples to apples.

One of the harder things to do in SQL is knowing how well your code is going to scale. If you write code thinking your workload will be a handful of records once a day, and that code is actually run several hundred thousand times per minute, you may not be happy with how quickly the work is performed.

The trick is always learning what SQL does well, and what it doesn't. Once you understand that, you'll begin to know what you have to change to speed things up!

That's it for my design patterns session. As always, if you have any questions, please let me know!

Up to this point in our design patterns sessions we've concentrated on removing duplicates from our troubled table.  Now, let's look into preventing those duplicates from being inserted in the first place.  If you haven't heard the phrase upsert before, it's the idea of doing an INSERT and UPDATE in the same statement, that way you handle changing data and inserting it in one go.  It's very helpful for preventing duplicate data from being inserted.

For this article we're going to use T-SQL to prevent these duplicates, rather than establishing a constraint, and then building an error handler when we try to insert duplicate data.  Let's dig in.

CREATE TABLE [dbo].[TroubledTable](     [FirstName] [varchar](50) NULL,     [LastName] [varchar](50) NULL,     [email] [varchar](255) NULL )

Consider our simple troubled table.  This little table has done a good job of collecting contact information so far, we decide, we have no good reason to change the structure, we just want to make it so if a user wants to enter the same row twice, it won't actually INSERT into the table.  If we use an IF THEN construct, we can check to make sure that row doesn't already exist.  Once we know the record doesn't exist, we can run the INSERT statement.  Here's what that would look like.

IF NOT EXISTS ( SELECT FirstName FROM TroubledTable WHERE FirstName = AND LastName = AND email = ) INSERT INTO TroubledTable (FirstName, LastName, email) VALUES , ,

Pretty easy right?  Now this solution doesn't really allow us to update information.  If we wanted to allow updates, we could add a primary key to the table, so we can uniquely identify each row, we could change the construct to search for a primary key, and when found UPDATE, otherwise it runs an INSERT.  In this new scenario we could have two people with the same name, but no two users are allowed to have the same email address.

First we set up a variable to hold the primary key in the case we want to do an update.

DECLARE @PKToUpdate INT

Next, we're going to look for a value for @PKToUpdate.

SELECT @PKToUpdate = ttPrimaryKey FROM TroubledTableWithPK WHERE     email =

If we find no rows, then @PKToUpdate will be NULL.  So we can use that knowledge to build our upsert.

IF (@PKToUpdate IS NOT NULL)     UPDATE TroubledTableWithPK SET         FirstName =         , LastName =         , email =     WHERE         ttPrimaryKey = @PKToUpdate ELSE          INSERT INTO TroubledTable     (FirstName, LastName, email)     VALUES , ,

Easy, right?  Either of these methods work really well for the single row at a time system, such as a web form built for one user at a time to come in and register.  But what if you had a system that allowed bulk inserts?

This is where the MERGE statement shines.  This is a much more elegant solution to handling all three operations in a single statement: INSERT, UPDATE, and DELETE.  If you haven't seen this one before, don't worry about it.  It's all concepts you already know, just put together in a new way!  Check it out.

We first have to think about our data as having two tables: a source table and a target.

In our example we're going to use my OriginalTroubledTable, a backup I made of TroubledTable as my source. It has the same structure as TroubledTable, so it makes things a bit easier. Now that we have a source and a target, we have to decide how we'll match rows between the two. Just like a JOIN statement. When doing UPSERTS, you're generally going to want to want the JOIN (or match) criteria to be the information that makes each row unique. In our case we don't want duplicate email addresses. So we're going to join on email from each table.

So far we have this much of our MERGE statement written by coming up with the source, target and JOIN criteria.

MERGE INTO TroubledTable AS [target] USING OriginalTroubledTable as [source] ON [target].email = [source].email

Next, we have to decide what we're going to do when we have a match, when we don't have a match in the target, and what we want to do if there's no match in the source. All three of these are things you can handle with the MERGE statement.

So, in the case where we have a match on our join criteria, we will want to do an UPDATE. If we don't have a match in the target, we're going to do an INSERT.  If we don't have a match in the source, we're not going to do anything, so at least that's easy!

Here's how we construct that MERGE statement:

MERGE INTO TroubledTable AS [target] USING OriginalTroubledTable as [source] ON [target].email = [source].email WHEN MATCHED THEN UPDATE SET [target].FirstName = [source].FirstName , [target].LastName = [source].LastName WHEN NOT MATCHED BY TARGET THEN INSERT (FirstName, LastName, email) VALUES ([source].FirstName, [source].LastName, [source].email);

So when we run this, any records missing from the target are added. Any records that already exist will have their first and last names updated. And we're all good right?

Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

That's right, Merge doesn't handle it if you pass it if your source table has duplicates in it! You might want to check out my de-duping articles.

Either that, or you could use a sub query for the source.

MERGE INTO TroubledTable AS [target] USING ( SELECT DISTINCT FirstName, LastName, email FROM OriginalTroubledTable) as [source] ON [target].email = [source].email --now we're only looking to make emails distinct WHEN MATCHED THEN UPDATE SET [target].FirstName = [source].FirstName , [target].LastName = [source].LastName --no need to include email, since that's our join criterion WHEN NOT MATCHED BY TARGET THEN INSERT (FirstName, LastName, email) VALUES ([source].FirstName, [source].LastName, [source].email);

Learning to read between the lines when you get a SQL error will go a long way in your career! This is just one chance to do that.

These are my two patterns for dealing with upserts, If you have another way, please let me know! I'm always interested in seeing new ways to do things.

As always if you have any questions about this, or anything else in SQL, let me know! I'm here to help.

Ok, today we wrap up our design patterns for removing duplicates from tables by turning to common table expressions (CTE's). When I start talking CTE's some people begin to cringe in fear. I'll admit, I still have to refer to a reference when doing something I haven't done before with a CTE, which is pretty much de-duping and building hierarchical lists. But they're useful, and they can have a light load on your SQL server in some situations, and de-duping is one of those situations, so let's dive right in!

First we're going to make sure we have our troubled table exists. If you don't have it, grab my script here to create it. Then take a look at the duplicates you have.

SELECT email, COUNT(*) FROM TroubledTable GROUP BY email HAVING COUNT(*) > 1

We're going to look at a email address that's been duplicated in our table.

SELECT * FROM troubledtable WHERE email = '[email protected]'

I'm looking at 7 copies of that email address. Now we're going to create a CTE that gives every copy of an email address a distinct row number. Then we can use that row number to identify the "extra" copies, in our case any record that has a row number greater than 1.

Let's first look at the query that gives us the row numbers for each copy.

SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, email ORDER BY ( SELECT 0)) RN , Firstname, lastname, email FROM troubledtable WHERE email = '[email protected]' --RESULTS: /* RN Firstname lastname email --- --------- --------- ----------------------------- 1 Abra Romero [email protected] 2 Abra Romero [email protected] 3 Abra Romero [email protected] 4 Abra Romero [email protected] 5 Abra Romero [email protected] 6 Abra Romero [email protected] 7 Abra Romero [email protected] 8 Abra Romero [email protected] */

Now, if we define that query as our CTE we can add another where clause that will show us only the "extra" rows. Please notice I start with the semi-colon. Do that, so the interpreter will understand your query. If you miss it, the interpreter might try to include a previous query, and that just won't work.

;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, email ORDER BY ( SELECT 0)) RN , Firstname, lastname, email FROM troubledtable WHERE email = '[email protected]' ) SELECT * FROM cte WHERE RN > 1 --RESULTS: /* RN Firstname lastname email --- --------- --------- ----------------------------- 2 Abra Romero [email protected] 3 Abra Romero [email protected] 4 Abra Romero [email protected] 5 Abra Romero [email protected] 6 Abra Romero [email protected] 7 Abra Romero [email protected] 8 Abra Romero [email protected] */

Those are the records we want to delete. So if we change our SELECT from CTE to A DELETE from the CTE, we're good to go!

;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, email ORDER BY ( SELECT 0)) RN , Firstname, lastname, email FROM troubledtable WHERE email = '[email protected]' ) DELETE FROM cte WHERE RN > 1 --(7 row(s) Affected)

Here's the really cool part of using a CTE, the delete was actually performed against the base table. Check out the execution plan for the proof!

SELECT email, COUNT(*) FROM TroubledTable WHERE email = '[email protected]' GROUP BY email HAVING COUNT(*) > 1 --(0 row(s) affected)

Pretty awesome, right? Now, let's remove the where clause and de-dupe the whole table.

;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, email ORDER BY ( SELECT 0)) RN --, Firstname, lastname, email -- not needed for the delete, just to show the data. FROM troubledtable) DELETE FROM cte WHERE RN > 1 --see they're all gone: SELECT email, COUNT(*) FROM TroubledTable GROUP BY email HAVING COUNT(*) > 1

Using CTE's to remove dupes can be a pretty cool way of doing the job. If you're only removing a small percentage of the rows in your table, the CTE can actually reduce your locking from a table lock to a page or row lock.

As with everything, you need to choose the method that works best for your situation. You'll have to make the judgement call on when each solution will work best for you.

That's it for the design patterns for removing duplicates. Next time we're going to cover a way of preventing duplicates from being inserted in the first place by performing upserts, instead of just inserting new data!

Until then, if you have any questions, please let me know! I'm here to help.

Let's continue the series on getting rid of duplicate data.  Today we're going to use self joins to get rid of them.  A self join is a join from a table back to itself.  It might seem weird to do that, but by the end of this article you'll see how it's useful.  I use self joins to de-dupe when the table has a primary key, yet that primary key didn't prevent duplicates from being inserted.  Let's set up a demo table now.

CREATE TABLE TroubledTableWithPK (       ttPrimaryKey INT IDENTITY(1,1)     , FirstName VARCHAR(50)     , LastName VARCHAR(50)     , email VARCHAR(255) )

Now, let's copy some data from the backup table we created before.

INSERT INTO TroubledTableWithPK (FirstName, LastName, email) SELECT * from OriginalTroubledTable

Take a look at the duplicates in that table.

SELECT email, COUNT(*) FROM TroubledTableWithPK GROUP BY email HAVING COUNT(*) > 1

Yeah, we have duplicates!  Take a closer look at one of these duplicates and you can clearly see how the primary key, which was supposed to uniquely identify the row to the table, didn't prevent duplicate email addresses from being added.

SELECT * FROM TroubledTableWithPK WHERE email ='[email protected]'

Let's propose a way to remove all the duplicate records that aren't the "last" copy of the duplicate data.  The last copy would be the record with the highest ttPrimaryKey Value.

SELECT email, MAX(ttPrimaryKey) FROM TroubledTableWithPK WHERE email ='[email protected]' GROUP BY email

If we were to join TroubledTableWithPK to a sub select of TroubledTableWithPK, we could show all those records we want to remove.

SELECT tt.* FROM TroubledTableWithPK tt INNER JOIN (     SELECT email, MAX(ttPrimaryKey) AS maxTTPrimaryKey     FROM TroubledTableWithPK     GROUP BY email) maxvals     ON tt.email = maxvals.email     AND tt.ttPrimaryKey != maxvals.maxTTPrimaryKey WHERE tt.email ='[email protected]'

Compare these results with the SELECT * statement we ran.  By joining on email, and where the ttPrimaryKey != MAX(ttPrimaryKey) shows us the "early" duplicates in the table.  Now all we have to do is cahnge the SELECT to a DELETE statement.

DELETE FROM tt FROM TroubledTableWithPK tt INNER JOIN (     SELECT email, MAX(ttPrimaryKey) AS maxTTPrimaryKey     FROM TroubledTableWithPK     GROUP BY email) maxvals     ON tt.email = maxvals.email     AND tt.ttPrimaryKey != maxvals.maxTTPrimaryKey WHERE tt.email ='[email protected]'

So now when we run the SELECT *, no more duplicates!

SELECT * FROM TroubledTableWithPK WHERE email ='[email protected]'

So all we have to do to delete all the duplicates, not just the ones for Elit Feugiat, just remove the where clause.

DELETE FROM tt FROM TroubledTableWithPK tt INNER JOIN (     SELECT email, MAX(ttPrimaryKey) as maxTTPrimaryKey     FROM TroubledTableWithPK     GROUP BY email) maxvals     ON tt.email = maxvals.email     AND tt.ttPrimaryKey != maxvals.maxTTPrimaryKey

And All our duplicates not-so-magically disappear!

SELECT email, COUNT(*) FROM TroubledTableWithPK GROUP BY email HAVING COUNT(*) > 1

Now, I would like to say, you might want to check out the execution plan of this method.  You could run into a situation where the table scan, the implicit order of the subquery and nested loops of the inner join to the sub table could give you troubles if you had a large number of records in TroubledTableWithPK.  These operations could cause you some blocking issues if any other process was trying to read records from this table during the delete.

I'd also like to point out, if you're deleting a large number of records, you could also find your transaction log filling up.

Deletes are going to cost you more than a truncate if you inserted the distinct records to a temp table, then truncated the table.  It's even more than selecting the distinct into a new table, then renaming the new table to the old name.

Now you have another tool available when you're faced with duplicate data.  Just one more method to go in this series: CTEs.  Don't worry, they're not as hard as you might think.  Until then, if you have any questions, let me know.  I'm here to help!

Last time, we learned that manually removing duplicate rows from a table could be a long and manual process.  Especially when you have as many duplicates as I created in our troubled table.  Today we're going to look at using temp tables to remove the extra rows.  I gave you the overview to this already, so today we're going to explore several versions of this pattern, and discuss some of the pros and cons of each step.

  Before we get started, my demo is going to use a "real" table instead of a #temp table or a @table variable.  I've talked about the differences between them before, and I will re-visit that topic later.  For the course of this article our temp table is just a table we're going to drop when we're finished using it.

Creating your temporary table

SELECT DISTINCT FirstName , LastName , email INTO TempTroubledTable FROM TroubledTable

or

CREATE TABLE TempTroubledTable  ( FirstName VARCHAR(50)     , LastName VARCHAR(50)     , email VARCHAR(255) ) INSERT INTO TempTroubledTable (FirstName, LastName, email) SELECT DISTINCT FirstName, LastName, email FROM TroubledTable

There are discussions all over the internet on why you should use CREATE TABLE and then INSERT INTO.  It has to do with LOCKS that are held while the data is copied over.  Generally you're removing duplicates on an ad-hoc basis, so you don't think a lot about code re-use while you're doing your work.  But if you're working on a high transaction volume system, and you know resources on your server are scarce, I would recommend creating your table before inserting the data.

We have a distinct list of the records that are duplicated in our source table.

Removing the duplicates from the source table

Again, we have a choice: DELETE or TRUNCATE.  To make that call you need to think about how the source table is used.

Do you have exclusive access to the source table? Will anyone try to read or write to the table while you delete the duplicate rows?

If you can get exclusive access, TRUNCATE will remove all the rows (and therefore the duplicates) faster than a DELETE, due to how the transaction log records the operation.

If you can't get exclusive access, DELETE will allow you to remove the records while maintaining other users' access to the table. But beware: you could experience blocking due to those other users' accessing the table at the same time. But you may need to delete the duplicate rows in batches.

Is your source table very large? Do you have 100,000 rows? A million rows? more? Or is your transaction log size limited in some way?

If you have a very large table, the difference in how TRUNCATE works could save you from having your transaction log fill up your drive. This is a very real situation the more rows you have to remove.

If the source table isn't very large, then DELETE will perform as well as TRUNCATE would.

So, depending on how you answered the above questions you could use either of the following commands to remove the duplicates.

DELETE FROM tt FROM TroubledTable tt INNER JOIN TempTroubledTable ttt     on tt.email = ttt.email

Which could be quite costly, both the join and the transaction log are working against us. (the join is really only needed if you needed to keep some of the rows.)

TRUNCATE TABLE TroubledTable

Either way, once you verify you've removed all the duplicate rows, you're ready to put the unique rows back into your source table.

SELECT email, COUNT(*) FROM troubledtable GROUP BY email HAVING COUNT(*) > 1

Putting the unique rows back into your source table

Since we only selected the DISTINCT rows into our temp table, we can restore the rows with a simple:

INSERT INTO TroubledTable SELECT * from TempTroubledTable

And now we can get rid of our temp table.

DROP TABLE TempTroubledTable

What if INSERT to source fails?

Occasionally the transaction log will be very limited in space, or the transaction log is too slow for my liking.  There is another way to get that data from your temp table back to the source.  You could rename the temp table.  But of course that means you have to drop the current source table, then do the rename.

DROP TABLE TroubledTable GO EXEC sp_rename @objname = 'TempTroubledTable', @newname = 'TroubledTable'

The great thing about this is the command is a data definition language (DDL) command, and runs much faster than the data manipulation language (DML) INSERT/SELECT.  It's something to keep in mind when manipulating data like this.

So, verify you've cleared all the duplicates out of the source table

SELECT email, COUNT(*) FROM troubledtable GROUP BY email HAVING COUNT(*) > 1

And make sure you have data in your source table.

SELECT TOP 100 * FROM TroubledTable

And now you're done!

Next time, we'll cover how to do this by using a join from the source table to  the source table.  It's yet another way you can remove duplicates from your tables.  Until then, if you have any questions, please let me know!

Go ahead and set up your TroubledTable by running the Create Troubled Table T-SQl Script.  Now take a look at your duplicate rows.

SELECT email, COUNT(*) FROM troubledTable GROUP BY email HAVING COUNT(*) > 1

You've got 200 rows that have been duplicated over and over.  Not a pretty sight.  Imagine how long it you would take you to retrieve each distinct row, and manually clear out the duplicate rows.

SELECT * FROM troubledtable WHERE email = '[email protected]'

Returns:

FirstName LastName email Clarke Glass [email protected] Clarke Glass [email protected] Clarke Glass [email protected] Clarke Glass [email protected]

You'd have to then construct two T-SQL statements for each duplicate (all 200 of them).

DELETE FROM troubledtable WHERE email = '[email protected]' INSERT INTO troubledtable (firstname, lastname, email) VALUES ('Clarke','Glass','[email protected]')

That would take a very long time!  Most of us would argue it would take too long, and look for another method of de-duping that data, right?  Now if you had only one or two dupes, you might still consider this method.  Let's look at one more reason you might not want to do that.  Look at the execution plan for that delete.

That table scan could be an issue if your table is quite large (1 million+ rows).  While you could change the table scan into a index seek by adding an index, you'd then create an additional operation during the delete: an index update.  If your table is a high transaction volume table, you may not want to trade the performance loss of an index operation for the gain in performance on your look up.

You need to keep performance concerns in mind when developing your solutions.  That way you can have the best answer as well as the fastest answer!  Next time we're going to take a look at using temp tables to get rid of those pesky dupes.  As always, if you have any questions, please let me know. I'm here to help!