SQL Server Performance

Reading old value

Discussion in 'General DBA Questions' started by skchandra, Dec 16, 2004.

  1. skchandra New Member

    Hi,

    Is there any way to read old value in a update transaction before it is committing or roll backing.
    For example the code segment is like,


    BEGIN TRANSACTION

    -- Say old value is PName = 'SQL'
    UPDATE rdbms SET PName = 'SQL Server' where ID = 1
    SELECT PName FROM rdbms WHERE ID = 1
    -- The above SELECT statement fetching the new value 'SQL Server', but not'SQL'
    -- Is there any way to fetch the old value (SQL) without using any variables or intermediate tables.

    COMMIT TRANSACTION -- ROLLBACK TRANSACTION




    Assume the table is a heap and no triggers.


    -- Cheers

    /* SKChandra */
  2. mmarovic Active Member

    declare @PName varchar(255)

    BEGIN TRANSACTION

    UPDATE rdbms SET @PName = PName, PName = 'SQL Server' where ID = 1

    print @PName
    select PName from rdbms where [id] = 1

    COMMIT TRANSACTION -- ROLLBACK TRANSACTION
  3. skchandra New Member


    I am looking for a solution without using temporary variable or pseudo tables.
    (see the underlined T-SQL code.)
    It is an interview question I had.


    BEGIN TRANSACTION

    -- Say old value is PName = 'SQL'
    UPDATE rdbms SET PName = 'SQL Server' where ID = 1
    SELECT PName FROM rdbms WHERE ID = 1
    -- The above SELECT statement fetching the new value 'SQL Server', but not'SQL'
    -- Is there any way to fetch the old value (SQL) without using any variables or intermediate tables.

    COMMIT TRANSACTION -- ROLLBACK TRANSACTION


    /* SKChandra */
  4. mmarovic Active Member

    To fetch into what?
  5. skchandra New Member

    Displaying the old value by using SELECT / PRINT commands is ok for me.


    /* SKChandra */
  6. mmarovic Active Member

    The only way I can think of is to use trigger to store from inserted into auxiliary table, then to select from that table. The question is why not selecting from original table before the update? The question doesn't make sense to me.
  7. skchandra New Member

    Yes. I agree with you. We can store the original value into a variable or after update statement, we can read the old data from 'deleted' pseudo table if there is a trigger defined.

    I have some clarifications...

    Even the default isolation level is read-committed, the said update transaction is reading un-committed data.

    Tried to read the same row (which is being updated) from another transaction by using NOLOCK hint, returned the same un-committed data.

    My question is... when I select the row, it has to return committed data 'SQL', why it is returning un-committed 'SQL Server'?

    When I checked the transaction's IO statistics, there is no physical read. SQL Server fetching data using logical read only.

    Is there any way to force SQL Server to use physical read, so that it can read the committed data?


    /* SKChandra */
  8. Chappy New Member

    Its reading uncommitted data because you specified NOLOCK!<br />Can you explain more about what youre trying to do, no offence but you seem to be making a meal of it <img src='/community/emoticons/emotion-5.gif' alt=';-)' />
  9. Twan New Member

    Hi ya,

    in SQL2000 after you've performed an update you can't read the old value anymore. if a select uses NOLOCK then it will pickup the new value, if a select use default locking then it will wait for the update to commit/rollback. SQL2005 introduces snapshot isolation level which does do what you're trying to achieve here

    Cheers
    Twan
  10. derrickleggett New Member

    You can if the database is in full recovery mode and you use a tool such as Lumigent Log Explorer. Just a thought.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  11. skchandra New Member

    Hi friends,

    The requirement is not a business requirement. Just I asked to know that is there any way to achieve it.

    Thanks Twan. I will try that in SQL2005.

    Thanks you guys.

    /* SKChandra */
  12. RoyL New Member

    I know this is an old thread, but:

    UPDATE rdbms SET @Old_Pname = PName, PName = 'SQL Server' where ID = 1

    or even:

    UPDATE rdbms SET @Old_Pname = PName, PName = @New_Pname where ID = 1

    Works in at least 2000.

    Grumpy

Share This Page