September 22, 2010 at 1:39 pm
trubolatta wrote:
My alternative is simply not to insert duplicate records in the production database in the first place [sample code follows] ...
Yes, this is a nice method. Very clean, very elegant.
But then again, my solution doesn't offer that delicious complexity some seem to relish or use the whiz-bang new features of SQL Server. To each his own.
As a developer, I always prefer the KISS approach, and your example certainly offers that. Thank you for posting an alternative.
September 22, 2010 at 1:44 pm
thisisfutile (9/22/2010)
@trubolotta Not trying to start an argument here, but hindsight is 20/20 for everyone. Sometimes we find ourselves in a situation like the OP is describing (or something similar) and we need a solution....This appears to be one of those articles that creates a problem based on poor design and purports to correct it using some bloated functionality of SQL Server. If the table were properly designed with uniqueness constraints, the problem would not exist. Allowing duplicate data into the table in the first place is the problem, not fixing it after the fact. The more likely scenario and the one I have seen most often comes from importing data from poorly designed databases or poorly trained users.
I agree that such problems are usually caused by poor design.
But poor design *is* prevalent in the real world.
So, would you agree that if you encounter *exactly* such instances (and you seem to indicate that you do), that unless you have a time machine to travel back and pre-correct the poor design, your choices are:
1. Surrender, stating that the database was poorly designed.
2. Try to correct the mistake.
If you choose 2., what is wrong with using the technique in this article? I do not recall the author suggesting that you should first design poorly and then use his technique to correct it.
September 22, 2010 at 1:46 pm
but what would u do if u have duplicates row not in one table but after joining to view, i was unable to delete duplicate records then , i got an error message
Msg 4405, Level 16, State 1, Line 1
View or function 'a' is not updatable because the modification affects multiple base tables.
delete from a
from
(select v_rpt_Study_details.StudyId
,view1.col1
,view1.col2
,view2.col1
,view2.col2
,ROW_NUMBER() over (partition by view1.col1
,view1.col2
,view2.col1
,view2.col2 order by view1.col1
,view1.col2
,view2.col1
,view2.col2
,) RowNumber
from view1 inner join view2 on
view1.col1=view2.col1) a
where a.RowNumber > 1
September 22, 2010 at 2:16 pm
skamath wrote:
So, would you agree that if you encounter *exactly* such instances (and you seem to indicate that you do), that unless you have a time machine to travel back and pre-correct the poor design, your choices are:
1. Surrender, stating that the database was poorly designed.
2. Try to correct the mistake.
If you choose 2., what is wrong with using the technique in this article? I do not recall the author suggesting that you should first design poorly and then use his technique to correct it.
You are quite correct. Having encountered such instances, I explain to my client that while I would love to have his business writing a fix for his database, the real problem is the design and my fix will NOT eliminate any problems with the data beyond the moment it is applied. I may risk a contract doing that, but more often than not the client will ask what exactly is the problem and what is the best correction.
There is nothing wrong with the technique in the article, though I say that with some reservation concerning locks, keys, indexes and already published reports that may have relied on the faulty data. I have also found that most cases involving duplicate rows are not nearly as clean and simple as the sample illustration and need quite a bit of stroking, especially if the offending duplicates are used as foreign keys or indexes. I'm just saying there is much more to it and in the case of the simple example, there are still pitfalls and still more efficient ways to achieve the same end.
September 22, 2010 at 3:01 pm
Excellent use of Row_Number.
The more you are prepared, the less you need it.
September 22, 2010 at 4:34 pm
Nice article - well written.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 4:43 pm
So it seems that we are all in agreement.
1. It is best that the system is designed to avoid these issues in the first place. This is similar to every dog owner removing their dog droppings.
2. Sometimes you find youself in the poo. In this case you need to a) Clean the data & b) Change the system to prevent it happening again.
There is often a third part to this issue. One that the "Hey I would just write a perfect database" folks may be ignoring. What happens if your perfect DB now starts to reject these dirty rows? Often systems with poor error handling in the DB also have poor error handling in the App tier.
If one insert in a more complex process fails, will it be contained in a transaction to ensure everything fails cleanly? (Most likely not, few developers use transactions, many prefer WITH (NOLOCK) hints).
If the app does correctly use transactions, will it check the return code from SQL (often not)
If it does check return codes will it present some kind of error to the end-user in a way the User can correct the issue?
And sometimes the User is already gone. eg: Batch systems, Real-Time capture ie: RFID, CEP, Process control, Toll Booth & Speeding Cameras etc.
In short it may be cheap to fix the DB, but you occasionally open the door to a huge rectification project. One that will take a long time to get resourced & funded. Which is why, you will often hear IT mgrs request you to clean the huge mess their DB is in now, & maybe write some scans they can run periodicly, till they get budget to do it right.
Or to go with the dog analogy. If you've just fallen in the sewer. You need to priortise. Perhaps wipe it from your eyes. Shake off the big chunks. And then figure out how to sort out the rest. Having someone nearby tell you, that they wouldn't have fallen in the sewer, is rather redundant.
September 22, 2010 at 6:50 pm
The principle is the same, the implementation depends on the nature of your business.
If quick fix is applicable and acceptable without incurring much cost, you can quick-fix it everyday.
By 80-20 rule, if you have to chase down and remedy all child records and parent records and that is too costly, you will have to determine where to cut off, to leave them along or fix at any cost or in between.
First, propose your fix and have business and IT managers sign off. If they don't understand, have them sign off.
In general, after quick-fix, it will cost your organization less in the long run if you can fix the root cause. That is not always the case. For example, if you don't have the source code, don't have a PASCAL programer, don't have the original design, don't have business rules.
The only time you don't have to fix anything and hand it back to your manager is you already found another job.
Jason
http://dbace.us
😛
September 22, 2010 at 7:21 pm
Nice. Very useful. Thanks.
September 22, 2010 at 7:53 pm
Earlier today before this article was posted I was making an example and showing it to a workmate that it was almost identical to the provided example. I used CTEs though instead of a subquery though. This article being posted today was really a coincidence.
Very good and useful. Thanks for article. 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 24, 2010 at 8:09 am
This would be a very nice technique during our ETL process of cleansing incoming data before merging into our tables.
September 26, 2010 at 10:04 am
First of all, I must thank everybody for their valuable comments and insights on this article. During the past several years, I had come across several situations where duplications came up in some or the other tables. Cases were due to either inadequate database design or programming. Everytime I wrote fresh code to solve the problem. The code in this article is a specific part of the overall solution. I have tried to concentrate specifically on a simple and fast method of finding and eliminating duplicate rows. For the sake of concentrating on the specific problem and clarifying it, I created a situation in the article to get the point across. The reason why I did not put in primary keys and foreign keys in there is to concentrate on the specific problem only. Had I put in foreign keys, then a case of consedering the characteristics of FK like NULL, CASCADE, DEFAULT, etc on UPDATE/DELETE would have come up which would have prolonged the article. Probably it could be part of another article.
The code could be enhanced to include OUTPUT clause for storing deleted rows in an audit table. Similarly, my aim was to solve the problem in one step. So, I did not use temporary tables.
Again, I refrain from using TRUNCATE TABLE in production unless the entire data set in the table has to be removed. If it is a live table, I would rather use DELETE so as not to affect a high volume of readers adversely.
Again since I wanted to scan the table only once, I did not use the IN clause with MIN function in the SUBQUERY.
Once again, I really appreciate your comments and would look forward to more.
September 26, 2010 at 10:13 pm
I didn't get too far because the code block that loads the data generate a syntax error I can't seem to get past.
insert into Emp_Details (Emp_Name, Company, Join_Date, Resigned_Date)
values ('John', 'Software', '20060101', '20061231')
,('John', 'Software', '20060101', '20061231')
,('John', 'SuperSoft', '20070101', '20071231')
,('John', 'UltraSoft', '20070201', '20080131')
...
generates:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
This on SQL 2005 SP3. Either this only works on 2008, or there is some environment condition assumed by the author that isn't the default.
Ok, loaded the data by an alternate method, then the RowNumber instance at the end of the order by line also scores a syntax error. Cut and paste isn't that difficult. Is there something else I'm missing?
I'm trying to apply this to a situation where I need the first duplicate record, but I need to return the name and address, while partitioning only on the address. (the duplicated part). If I considered the name too, of course the records are different, but I don't want to mail twice (or more) to the same address.
September 27, 2010 at 7:02 am
hugh.hemington (9/26/2010)
I didn't get too far because the code block that loads the data generate a syntax error I can't seem to get past.insert into Emp_Details (Emp_Name, Company, Join_Date, Resigned_Date)
values ('John', 'Software', '20060101', '20061231')
,('John', 'Software', '20060101', '20061231')
,('John', 'SuperSoft', '20070101', '20071231')
,('John', 'UltraSoft', '20070201', '20080131')
...
generates:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
This on SQL 2005 SP3. Either this only works on 2008, or there is some environment condition assumed by the author that isn't the default.
Ok, loaded the data by an alternate method, then the RowNumber instance at the end of the order by line also scores a syntax error. Cut and paste isn't that difficult. Is there something else I'm missing?
I'm trying to apply this to a situation where I need the first duplicate record, but I need to return the name and address, while partitioning only on the address. (the duplicated part). If I considered the name too, of course the records are different, but I don't want to mail twice (or more) to the same address.
That's 2008 syntax, don't think you're missing anything.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
September 27, 2010 at 7:19 am
hugh.hemington (9/26/2010)
...I'm trying to apply this to a situation where I need the first duplicate record, but I need to return the name and address, while partitioning only on the address. (the duplicated part). If I considered the name too, of course the records are different, but I don't want to mail twice (or more) to the same address.
Hugh, start a new thread in the 2k5 forum section. Include a table create / populate script to provide some sample data, and a sample of what you would like as your output from it. This is a common problem and will take minutes to solve.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply