Post reply

Interview Question

  • September 21, 2011 at 7:30 am

    #248175

    I have 1,00,000 rows in table.

    Question :

    begin tran

    truncate table ....

    Rollback Tran

    what is the output of this query?

    Can any one explain me in Detail?

  • September 21, 2011 at 7:32 am

    #1384484

    What do you think all the possible answers are and why?

    I can think of at least 3.

  • September 21, 2011 at 7:36 am

    #1384488

    Thanks for reply ..but i did not understand.

  • September 21, 2011 at 7:37 am

    #1384491

    hint # 1 the number of rows don't matter...testing it yourself goes a lot farther education-wise than asking and getting an answer....

    hint # 2 when can you use or not use truncate?

    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!

  • September 21, 2011 at 7:39 am

    #1384492

    hint #3, aside from hitting F5 is what can stop this from running correctly or at all.

  • September 21, 2011 at 8:05 am

    #1384508

    Can we rollback if we use truncate command??? I dont think we can rollback since truncate command does not generate any logs.

  • September 21, 2011 at 8:11 am

    #1384512

    (: Hakuna Matata 🙂 (9/21/2011)

    Can we rollback if we use truncate command??? I dont think we can rollback since truncate command does not generate any logs.

    try it yourself.

    ALL transactions can be rolled back, no matter what they do.

    truncate gets logged, same as every other event.

    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!

  • September 21, 2011 at 8:22 am

    #1384515

    So after you finally hit F5 you can try finding to other 2-3 possible outputs

    http://www.sqlservercentral.com/Forums/Topic908285-1198-1.aspx

  • September 21, 2011 at 8:26 am

    #1384523

    i just tried it and it seemed to work

    begin transaction

    truncate table tablename

    rollback transaction

    didn't remove anything from the table

  • September 21, 2011 at 8:28 am

    #1384524

    I was able to rollback a table with around 181000 records.

  • September 21, 2011 at 8:30 am

    #1384526

    (: Hakuna Matata 🙂 (9/21/2011)

    I dont think we can rollback since truncate command does not generate any logs.

    Common myth, completely false. Truncate (like every single other data modification in SQL) is logged.

    http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281930%29-TRUNCATE-TABLE-is-non-logged.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 21, 2011 at 8:37 am

    #1384532

    (: Hakuna Matata 🙂 (9/21/2011)

    Can we rollback if we use truncate command??? I dont think we can rollback since truncate command does not generate any logs.

    If SQL Server was like Oracle you would be right.

    Fortunately, it's not.

    -- Gianluca Sartori

  • September 21, 2011 at 9:12 am

    #1384584

    Gianluca Sartori (9/21/2011)

    (: Hakuna Matata 🙂 (9/21/2011)

    Can we rollback if we use truncate command??? I dont think we can rollback since truncate command does not generate any logs.

    If SQL Server was like Oracle you would be right.

    Fortunately, it's not.

    Very true.. In Oracle truncate does not permit rollback. So I presumed it is the same with SQL Server.

  • September 21, 2011 at 9:31 am

    #1384603

    (: Hakuna Matata 🙂 (9/21/2011)

    Gianluca Sartori (9/21/2011)

    (: Hakuna Matata 🙂 (9/21/2011)

    Can we rollback if we use truncate command??? I dont think we can rollback since truncate command does not generate any logs.

    If SQL Server was like Oracle you would be right.

    Fortunately, it's not.

    Very true.. In Oracle truncate does not permit rollback. So I presumed it is the same with SQL Server.

    It just doesn't log the individual rows being removed. It logs the PAGES it is removing. (so it's a lot less being logged, but still - it's logged).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • September 21, 2011 at 9:34 am

    #1384609

    THANKS FOR ALL REPLY 🙂

Viewing 15 posts - 1 through 15 (of 15 total)

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