Professional Web Applications Themes

Truncate table logging - Microsoft SQL / MS SQL Server

This is what I ran... Create table Test ( Col1 int ) go insert test values (1) insert test values (2) insert test values (3) go Begin transaction Truncate table test Rollback transaction select * from test --returns all values So what Im trying to understand is the logged nature of truncate table TSQL command. So is it safer to use Truncate table test or Delete test ? I know that truncate table is not logged and if its not logged, then how come it rollbacks a transaction... Kinda confused Thanks...

  1. #1

    Default Truncate table logging

    This is what I ran...

    Create table Test ( Col1 int )
    go

    insert test values (1)
    insert test values (2)
    insert test values (3)
    go

    Begin transaction

    Truncate table test

    Rollback transaction

    select * from test --returns all values

    So what Im trying to understand is the logged nature of truncate table TSQL
    command. So is it safer to use Truncate table test or Delete test ? I know
    that truncate table is not logged and if its not logged, then how come it
    rollbacks a transaction... Kinda confused

    Thanks


    FR Guest

  2. #2

    Default Re: Truncate table logging

    As I understand, TRUNCATE TABLE does not log the individual deletes,
    however, does log the operation, hence can be rolledback.

    "FR" <com> wrote in message
    news:phx.gbl... 
    TSQL 
    know 


    Prasad Guest

  3. #3

    Default Re: Truncate table logging

    Still unclear.. I hope i was clear in my email..
    Im trying to understand when I may use it and not use it cos a rollback
    transaction seems to roll it back . If its non logged, where does it store
    it ?


    "Prasad Koukuntla" <com> wrote in message
    news:phx.gbl... 
    > TSQL 
    > know [/ref]
    it 
    >
    >[/ref]


    FR Guest

  4. #4

    Default Re: Truncate table logging

    Floyd,
     [/ref][/ref]

    While DELETE logs the row-by-row operations, TRUNCATE TABLE doesnt do
    that.Instead it just logs the page and extent allocations.While this is
    termed as "partial logging", it still is enough to roll back a transaction.


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "FR" <com> wrote in message
    news:phx.gbl... 
    > > TSQL 
    > > know [/ref]
    > it 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  5. #5

    Default Re: Truncate table logging

    I do understand that ..So when would i need to use delete as opposed to
    Truncate then ?

    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... [/ref]
    >
    > While DELETE logs the row-by-row operations, TRUNCATE TABLE doesnt do
    > that.Instead it just logs the page and extent allocations.While this is
    > termed as "partial logging", it still is enough to roll back a[/ref]
    transaction. [/ref]
    store [/ref][/ref]
    table [/ref][/ref]
    I [/ref][/ref]
    come 
    > >
    > >[/ref]
    >
    >[/ref]


    FR Guest

  6. #6

    Default Re: Truncate table logging

    thats it.. well from now on ..its hello truncate table


    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... [/ref][/ref]
    is 
    > > transaction. [/ref]
    > rollback 
    > > store [/ref][/ref]
    deletes, 
    > > table [/ref][/ref]
    test [/ref][/ref]
    how 
    > >
    > >[/ref]
    >
    >[/ref]


    FR Guest

  7. #7

    Default Re: Truncate table logging

    Dinesh,

    So why does not transactional tion work for Truncate table then ? If
    a rollback can work , so whats logged and whats not logged based upon what
    FR had to say wrt transactional tion?



    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:phx.gbl... [/ref][/ref]
    is 
    > > transaction. [/ref]
    > rollback 
    > > store [/ref][/ref]
    deletes, 
    > > table [/ref][/ref]
    test [/ref][/ref]
    how 
    > >
    > >[/ref]
    >
    >[/ref]


    Hassan Guest

Similar Threads

  1. TRUNCATE TABLE command in 9.3?
    By generic@arigatoo.net in forum Informix
    Replies: 2
    Last Post: November 24th, 08:31 PM
  2. Fw: Turn off logging for a single table [1871]
    By Mark Denham in forum Informix
    Replies: 0
    Last Post: September 15th, 12:42 PM
  3. syslog truncate
    By Sinisa in forum SCO
    Replies: 3
    Last Post: August 1st, 01:24 PM
  4. truncate log
    By DRE in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 07:20 PM
  5. Truncate Access
    By Ramandeep Singh in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 1st, 07:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139