Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deleting Duplicate Records Expand / Collapse
Author
Message
Posted Wednesday, December 24, 2008 9:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:54 AM
Points: 48, Visits: 204
Cost is usually a good indicator, but it can be misleading -- especially if you only evaluate it on test servers with limited amounts of data. When you move your code into production where the number of rows may be much larger, you can have disasters.

The cost of the query is based on SQL Server's estimate of the number of rows. You can have a small table (or a small subset of a large table) where the cost for a certain operation is lower than another, but as the table grows (or the constraints of the query change), the reverse is true. This is one of the dangers of stored procedures because their execution plans get cached and may not get recompiled as your data changes. Don't get me wrong, I love stored procedures, and I never use inline code in my applications; I always call stored procedures.

RBAR can affect all four of the operations (select, insert, update, delete). One thing to always remember is: "RBAR bad, set-based good!"

Here are a few good articles on RBAR:
http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/
http://www.sqlservercentral.com/articles/T-SQL/61539/
http://www.sqlservercentral.com/articles/Performance+Tuning/62278/
Post #625458
Posted Wednesday, December 24, 2008 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 10, 2012 1:22 PM
Points: 5, Visits: 19
thanks
Post #625461
Posted Wednesday, April 28, 2010 7:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 2:18 AM
Points: 15, Visits: 21
The query will be composed this way:-


WITH TempUsers (FirstName,LastName, duplicateRecordCount)
AS
(
SELECT FirstName,LastName,
ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) 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.


Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
Post #911925
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse