Does your table have a key or not? If it does then it doesn't have duplicates. This example may help:
CREATE TABLE tbl (col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL /* NO KEY!! */);
INSERT INTO tbl VALUES (1,1,1);
INSERT INTO tbl VALUES (1,1,1); -- duplicate
INSERT INTO tbl VALUES (2,2,2);
INSERT INTO tbl VALUES (2,2,2); -- duplicate
WITH t(r) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY col1, col2, col3)
DELETE t WHERE r > 1;
How do you decide a row is a "duplicate" for you? When you have "duplicate" rows, how do you decide which one of them you want to keep?
This kind of problem usually isn't hard to fix, and once fixed it is usually easy to prevent it from re-occuring. The problem is that every time I see this kind of problem, the definition of "duplicate" and the way to determine which row you want to keep seems to be unique!
with an IDENTITY primary key, it most certainly is possibly to have "duplicates" -- duplicates of the "real" key, which invariably was not defined with a UNIQUE constraint
see the duplicate?
do a search in your favourite search engine for "removing duplicates" and you will see that there are literally millions of web pages on this topic
Exactly. But unfortunately these questions usually begin like inka's did with someone saying "I need to delete duplicates" and believing they have adequately specified the problem. Knowing what keys exist is a pre-requisite to solving almost any problem in SQL, yet that information is very commonly left out.