does ss2008 maintain an internal timestamp of data updates?

Question

Post reply

does ss2008 maintain an internal timestamp of data updates?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
May 11, 2012 at 1:17 pm

#273892

Does SS2008 maintain an internal timestamp of data updates?
For example, if I wanted to select the last 5 rows in a table that were updated is there a T/SQL statement I can execute to get this information?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

May 11, 2012 at 1:19 pm

#1486586

Nope.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You would need to add a trigger to the tables or implement change data capture or change tracking.

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

ScottPletcher SSC Guru Points: 101133 More actions · Answer 3

No. Maintaining that on all tables would be a huge sacrifice of performance for no real gain.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 4

sqlguy-736318 (5/11/2012)
Does SS2008 maintain an internal timestamp of data updates?
For example, if I wanted to select the last 5 rows in a table that were updated is there a T/SQL statement I can execute to get this information?

Actually, with a slight modification to your table, there is a way to do this. If you create a column to contain the RowVersion datatype, SQL Server will fill it automatically as the data in each row is updated using a unique and ever increasing binary value. Then, you can select the top 5 (in this case) ordered in descending order to get what you want for that particular instant in time.

Please see the following link for more information.

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

This method won't, however, tell you WHEN the modifications happened. Despite Scott's objections, for that you'd need a trigger to update a "last modified on" column" and it can be done without truly huge performance problems. It will, however, cost a bit in performance because it generally requires a secondary write to the table unless you use "instead of triggers" and I'm pretty darned sure you don't want to go there!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101133 More actions · Answer 5

I didn't object to doing it on ONE table.

I merely pointed out that for SQL to try to do that on all tables, as a default, as it were, would be huge overhead for no real gain.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 6

Ummm.... are you talking about the trigger or the rowversion column because the rowversion column is virtually no overhead except for the physical column space and that's not any bigger that a datetime column. The overhead of a properly written trigger wouldn't be a "huge" overhead either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101133 More actions · Answer 7

Either one.

Adding a rowversion column to every table would indeed be huge overhead for no real gain, since it likely would never be used. Eight bytes per row would add up across all tables.

Obviously a trigger on every table would be even worse.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 8

ScottPletcher (5/14/2012)
Either one.
Adding a rowversion column to every table would indeed be huge overhead for no real gain, since it likely would never be used. Eight bytes per row would add up across all tables.
Obviously a trigger on every table would be even worse.

I guess that really depends on what the code is supposed to do and I'm really not sure how we got on the subject of doing this to all tables.

That, notwithstanding, let's say that you were given the requirement to be able to tell what the last arbitrary number of rows for all tables (not including reference tables just to keep this sane). What would YOU use to enable it that would be less of an overhead?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

I think Scott is thinking along the lines of doing this for all tables because the original question asked if there was an internal timestamp in SQL server that tracked this sort of thing--if such a thing existed, it *would* be present on all tables and would add overhead in both data writes and data size to all of them. Hence his suggestion that this doesn't exist precisely in order to avoid such overhead when it isn't needed in 99% of situations.

ScottPletcher SSC Guru Points: 101133 More actions · Answer 10

Exactly Paul; well put.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".