Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd be following the procedure outlined here (unless of course someone has a better way to do it), and I'm wondering if I could just have some help being pointed in the right direction on how to start.

Basically I need help first on HOW to create functions, and general tips on making it adjustable for varying number of columns etc. This may be a very complicated task, as I have no previous experience making SQL functions, so please let me know if this is a difficult task for an SQL noobie working with MS SQL 2005.

share|improve this question

migrated from Jun 4 '12 at 22:10

This question came from our site for system and network administrators.

Since you linked to a MS SQLServer support page, I assume you are speaking about this and not some other DB server? – SvenW Jun 4 '12 at 21:08
Yes I am, sorry, I will edit my post to reflect that. – Hoser Jun 4 '12 at 21:10

That's a big first step for a self-identified newbie. If I read your question correctly, you're looking to create reusable code that will delete duplicates from an arbitrary table. I'm not going to show you how to do that because it's a) dangerous and b) non-trivial. But here's how I'd do it for one table:

with cte as (
   select row_number() over (partition by col_a, col_b order by col_a) as [rn]
   from tbl
delete cte
where [rn] <> 1

This code assumes that you have a table called tbl and you're using *col_a* and *col_b* together to determine uniqueness. What's going on here is that you're essentially enumerating all of the rows within a given set that would be considered duplicate and deleting all but the first.

share|improve this answer
Thanks for the answer, but when running this I'm getting this error message Incorrect syntax near 'cte'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon. – Hoser Jun 5 '12 at 13:26
@Hoser - So terminate the previous statement (before the with) with a semi colon then... – Martin Smith Jun 5 '12 at 13:36
@Hoser: Like Martin Smith said, if the engine tells you to do something, you should do that. People often write this as ";with cte as..." which, while correct, is missing the point; that semicolon belongs to the previous statement. – Ben Thul Jun 5 '12 at 14:27

a similar question has been asked before

another article in which the author mentions different methods on how to achieve your task

a simpler article

but to build a SQL function to achieve the task required is a bit complicated because you will have to send the table and the duplicate column names as parameters and if you have more then one column as your PK that means you have to have a flexible sql function.

Passing a table as a parameter to a sql function or store procedure is not straight forward

if you decide to send the table name as a parameter then you will have to make alot of string concatenations and you will have the same challenge about column names and number of columns.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.