What is the most efficient way to delete records?

Question

Post reply

What is the most efficient way to delete records?

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Are seriously trying to explain to Jeff Moden how deletes work in SQL Server?

Technically Primary key and Unique key is the same thing.

Jeff I am surprised you didn't comment on this idea. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

adhiman SSC Veteran Points: 292 More actions · Answer 2

I am not trying to explain Jeff but i am trying to present my idea. I respect Jeff's and yours contributions to this community. I would consider myself lucky if i could learn something from you guys.

Thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

adhiman (6/8/2012)
Hi Jeff:
I appreciate your response to my blog.
My first article is talking about performing the deletes in an efficient manner.

The link you provided takes us to a discussion on the differences between Unique and Primary Keys which has little if anything to do with deletes. I'm also not the kind of person that's going to search a blog for some supposed related article.

Suppose you have a table that have millions of rows in it and you want to perform delete on it using some condition. If you use this syntax :
begin tran;
delete from table where condition
commit;
What it will do is , it will lock the table until delete happens.

Actually, "IT Depends". If few enough deletes are done, it won't "lock the table". It'll lock a row, page, or extent, or the whole table based on the number of rows you try to delete. There are many experiments you can do to prove this. The easiest thing to do is make a million row test table, add a PK, start a transaction to delete a row, switch to another window and see that you can select, insert, update, and even do another delete in another area of the table.

Secondly, in my other blog which talks about PK and UK. The example of adding an SSN was an idea(not a perfect suggestion).The main aim of that blog is to help understand the basic misconceptions about the PK and UK that people have and i have read on the internet. You are absolutely correct that in the example i have given the best way to keep track of uniqueness of by adding an ID column. Probably i will come up with a better example. The idea is to make readers aware that they should consider thinking about the uniqueness of the rows not just by adding an Id. It does help query optimizer to come up with an optimal plan for the query execution.
I really appreciate your thoughts.
Please let me know if you have questions.

Considering that I'll frequently use two sets of unique keys in a table, you can just imagine what my recommendations will be. My only question would be that if you feel the way you say you do above, why does your blog bash the use of an IDENTITY column? Of course, that's a rhetorical question at this point because I disagree with much of what you stated in that article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Ah... now I see. The link is for all the articles. There is enough white space between the articles that I didn't realize another article followed when I got to the bottom of the first article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

adhiman (6/8/2012)
I am not trying to explain Jeff but i am trying to present my idea. I respect Jeff's and yours contributions to this community. I would consider myself lucky if i could learn something from you guys.
Thanks

My recommendation would be for you to do a bit more research before you post on your blog, learn how to build test data, and do some performance and other tests to prove what you state. For example, doing a delete from a table does not necessarily prevent other DML operations from occuring at the same time. It depends on other factors and you need to know what those factors are instead of just blindly stating that it locks the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Sean Lange (6/8/2012)
Are seriously trying to explain to Jeff Moden how deletes work in SQL Server?
Technically Primary key and Unique key is the same thing.
Jeff I am surprised you didn't comment on this idea. 😉

About the explanation... I'm no one special or different than anyone else. I appreciate someone else's explanation no matter whether I agree or disagree with it. It's all a part of the process of learning and interacting. I appreciate the implied compliment, though.

On the "technically" thing, you actually left off a part of the statement that explained what he meant. Between that and a bit of bad English, I can understand why you thought I should make a comment. What I'm much more concerned with is people thinking that using an IDENTITY column as the PK for something like a Customer table is wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)