Delete one record when duplicates exist

June 20, 2009 in SQL Server | No comments

Have you ever had a table with completely duplicated records? Of course this can only happen when your table does not have an automatically-increasing identifier nor a primary key or unique constraint. This does not happen often but occasionally it does.

I came across this problem when working with package configurations in my Integration Services packages. All the packages are using the same configuration table in an administration database and somehow the BIDS had duplicated a record in that table. I did not want to delete both records because one of them should stay in there. So I thought of this workaround.

If you already know the timestamp data type you probably know what I’m going to suggest. If you don’t: read on!

Unlike what you’d think, timestamp is not a data type that you want to use to store timestamps. That’s what datetime (or datetime2) is for. Timestamp is a type that SQL Server uses internally to version-stamp rows in a table. You can think of it as a counter. Each database has one and for each modification (Insert or Update) on a record in a table that contains a timestamp column, it is increased by one.

To avoid confusion with this data type, it has been decided to call it RowVersion, thereby deprecating timestamp.

You can look up the current value of your database’s RowVersion counter using the following command:

select @@DBTS;

Now, to get back to the topic: when adding a column of type RowVersion to your table it will get populated automatically with the next available counter values. To add a RowVersion column to your table you can use a command similar to this one:

alter table MyTable add ts RowVersion;

With this column you now have a way to uniquely identify your duplicated records and the possibility to issue a DELETE statement using the RowVersion column. You do it like this:

delete from MyTable
where ts = 0x000000000000082B;

Table contents before the DELETE:

Table contents before the DELETE