How really works TRUNCATE TABLE?

• misiekbest

  SSC Veteran

  Points: 212

  More actions

  April 7, 2011 at 3:46 am

  #249104

  Hi.

  Question is simple. What does sqlsrv engine do with table after truncate command?

  Someone told me, that it just rename that table (internally?), create new one and drop renamed...

  Well, I'm not sure that is true. Truncate should 'simply' deallocate data pages, but in SqlSrv it can be rolled back and it resets identity seed so i have some doubts. Google didn't answer my question :unsure: and i hope that you may know something about storage engine behavior :-D.

  Thanks!

  Regards.

  m.

• Lowell

  SSC Guru

  Points: 323573

  More actions

  April 7, 2011 at 4:58 am

  #1308409

  misiekbest (4/7/2011)

  ---

  Hi.

  Question is simple. What does sqlsrv engine do with table after truncate command?

  Someone told me, that it just rename that table (internally?), create new one and drop renamed...

  Well, I'm not sure that is true. Truncate should 'simply' deallocate data pages, but in SqlSrv it can be rolled back and it resets identity seed so i have some doubts. Google didn't answer my question :unsure: and i hope that you may know something about storage engine behavior :-D.

  Thanks!

  Regards.

  m.

  you are correct about the deallocation and identity;

  from BOL:

  The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. 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 log.

  http://msdn.microsoft.com/en-us/library/ms177570.aspx

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 7, 2011 at 12:18 pm

  #1308742

  misiekbest (4/7/2011)

  ---

  Hi.

  Question is simple. What does sqlsrv engine do with table after truncate command?

  Someone told me, that it just rename that table (internally?), create new one and drop renamed...

  Well, I'm not sure that is true. Truncate should 'simply' deallocate data pages, but in SqlSrv it can be rolled back and it resets identity seed so i have some doubts. Google didn't answer my question :unsure: and i hope that you may know something about storage engine behavior :-D.

  Thanks!

  Regards.

  m.

  Why can it be rolled back? Because all pieces of the operation are transactional, including the rewriting of the table's page allocations. Read more details to answer more of your "internals" questions: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1930)-TRUNCATE-TABLE-is-non-logged.aspx

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato