August 16, 2011 at 10:04 pm
Comments posted to this topic are about the item Truncate and Delete
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 16, 2011 at 10:53 pm
Good question for testing the basics.
M&M
August 17, 2011 at 1:00 am
This was removed by the editor as SPAM
August 17, 2011 at 1:21 am
Thanks for the question 🙂
It will be interesting to see how many still think that truncate is a non-logged operation.
August 17, 2011 at 3:27 am
I missed it because I also selected an option that said "TRUNCATE cannot be logged". Baffled to know this is otherwise 🙂
Made 3 selections and thought QoTd would validate my selection since only 2 was required. That validation did not happen ... and thus, I saw the dreaded red X saying "Sorry, you were wrong!"
Kwex.
August 17, 2011 at 3:43 am
Truncate
Delete
TRUNCATE is a DDL command
DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row
DELETE statement is executed using a row lock, each row in the table is locked for deletion
Cannot use Where Condition
We can specify filters in where clause
It Removes all the data
It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Delete activates a trigger because the operation are logged individually.
Faster in performance wise, because it doesn’t keep any logs
Slower than truncate because, it keeps logs
Rollback is not possible
Rollback is possible
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table
keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
DELETE retain the identity
Restrictions on using Truncate Statement
1. Are referenced by a FOREIGN KEY constraint.
2. Participate in an indexed view.
3. Are published by using transactional replication or merge replication.
Delete works at row level, thus row level constrains
August 17, 2011 at 3:55 am
This was removed by the editor as SPAM
August 17, 2011 at 3:58 am
Nils Gustav Stråbø (8/17/2011)
Thanks for the question 🙂
Yes, it's a good question.
It will be interesting to see how many still think that truncate is a non-logged operation.
Interesting and somewhat horrifying - up to now, 43% said delete was not logged, which is amazing considering how much discussion of that there has been on SQLServerCentral.com; and more tha 50% got it wrong that way or some other way. 6% chosing "delete resets ID, truncate does not" is crazy, I hope these guys never use identity columns because if they believe that nonsense they are going to make some really horrible mistakes, like thinking that if their table behaves like a stack IDs will always be contiguous, which can cause lots of lovely bugs.
Tom
August 17, 2011 at 5:32 am
easy question!! and good question!
August 17, 2011 at 5:38 am
rfr.ferrari (8/17/2011)
easy question!!
I would liked to have thought so - but sadly (and alarmingly) not so going by the right / wrong answers given - roughly 50/50 split
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 17, 2011 at 5:52 am
Subjective Adapts (8/17/2011)
Rollback is not possibleRollback is possible
That one at least is not true. I wondfer how many of your other statements are also wrong?
August 17, 2011 at 6:00 am
Tom.Thomson (8/17/2011)
Interesting and somewhat horrifying - up to now, 43% said delete was not logged, which is amazing considering how much discussion of that there has been on SQLServerCentral.com; and more tha 50% got it wrong that way or some other way. 6% chosing "delete resets ID, truncate does not" is crazy, I hope these guys never use identity columns because if they believe that nonsense they are going to make some really horrible mistakes, like thinking that if their table behaves like a stack IDs will always be contiguous, which can cause lots of lovely bugs.
I agree with you Tom!!!!
the CONCEPT is in fault here!!!
August 17, 2011 at 6:13 am
Good question, thanks.
http://brittcluff.blogspot.com/
August 17, 2011 at 6:41 am
Appreciate the question. I missed it because I couldn't decide on my second choice, and went with "truncate is not logged" assuming the author was making that common error (um, sorry Stuart). Didn't know the bit about resetting the identity value to the seed, so happy to get that wrong and learn something!
---------------------------------------------------------
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."
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply