The reason timestamp is binary is for speed. Timestamps are made to allow version comparisons to be as fast as possible. Unfortunate name though!
Triggers have usually worked well in my projects...
I like that no matter HOW the row is updated (someone using Access/Query Analyzer, an App SP, etc, etc) the trigger fires and my ChangedDTTM and ChangedBy columns get updated...
But in the end, it's really about what you and your team are comfortable with and can support...
I've also looked at TimeStamps. One thing that tipped me off that it's a "version vehicle" is that in someplace's in the BOL MS states that RowVersion is an alias for the TimeStamp datatype...
"triggers are something I try to avoid, so let's ignore that possibility"
Roy, why avoid triggers? It seems to me that a trigger is the best way to deliver exactly what you need!
There's very little difference between a trigger and a stored proc. You seem happy to use one, but not the other.
Triggers are risky unless you /really/ know what you are doing. They seem simply if you have been pulling DBA duty for years, but for the occasional DBA they can lead to spaghetti faster than an Italian chef on meth.
good and helpful blog entry!
Been there, done that. Didn't have a blog at the time... I filed it under "fire the guy who named that".
Hi Roy
Just wanted to write and apologise to you, because i just realised that over the last several months i've continually mispelled your surname as 'oshergrove' instead of 'osherove'. no idea how i came up with the extra letters... but i've cleared them out of my cache now.
cheers
Leon
Roy -- maybe I'm saying the same thing as you when you said:
"I can either get the date as part of a stored procedure parameter and set it every update, or I can just set it on updates and inserts automatically inside the stored procedure using “GETDATE()” functionality in SQL."
We use GETDATE as a default value on our DataModified columns
(which are like standard footers on most tables)
So -- anytime you touch that row, GETDATE() fires...is this
what you describe above?
Defaults when bound to columns always work faster than triggers and one doesn't have to worry about triggers generating errors this way!
So how would one actually use the MS version of TIMESTAMP? I've seen a hundred posts like this, explaining how timestamp isn't a date and all, but the conclusion seems to always be the datetime/getdate() hack.
That's fine, but I'd still like to know how to use the MS version of TS/rowversion for concurrency. I'm having problems casting the wacko MS timestamp value into some C# data type so I can compare them on save/update.