SQL Server Performance Forum – Threads Archive

Reading old value

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 */

---

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

---

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 */

---

To fetch into what?

---

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

---

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.

---

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 */

---

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=’;-)’ />

---

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

---

You can if the database is in full recovery mode and you use a tool such as Lumigent Log Explorer. Just a thought. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

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 */

---

I know this is an old thread, but:
or even:

Works in at least 2000. Grumpy

---

]]>