March 29, 2011 at 5:31 am
Has anyone used the ROWVERSION to generate an row identifier.
Most of the literature out there indicates this datatype is to be used for Concurrency control.
The IDENTITY datatype is already being used to identify the Entity within the table, the ROWVERSION would potentially be used to identify a version of that entity.
March 29, 2011 at 6:22 am
Based on how it works, no, I wouldn't recommend it. If you already have an artificial unique identifier on the table, why do you need a second one? Does the data have a natural key? Put a constraint on that (probably should anyway).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 6:24 am
Sounds like you're storing two things in one table, if you have entities and versions in the same table, maybe worth considering a split.
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
March 29, 2011 at 6:31 am
The live/current table will only ever contain one version of the entity as identified using the GID column (PK) ... a supporting Audit table can contain multiple multiple historic versions of that entity..
I want to use the SID to identify the entity at a historical point in time...
With regard to a natural key this may be formed of one or more columns (depending on the table) which I will have uniqueness controls against however i do not want to propagate this natural key to other tables within the system, i'm replacing the nautral key with the GID column.
March 29, 2011 at 6:40 am
If you're looking to do versions of data, you might want to take a look at this article[/url] I wrote for Simple-Talk. Better still, take a look at the comments. Alex Kuznetsov had some great ideas for improving what I had done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 6:45 am
Thanks i'll have a look at this ...
March 29, 2011 at 5:32 pm
Any chance you'd like to share more details about what you are trying to accomplish?
There are, of course, many ways to do many different things. But, are you doing some sort of warehousing, versioning, change detection or something else?
If you can articulate what you are trying to accomplish, we can probably help start you down a path or suggest a solution.
If you are doing some sort of warehousing and you are dealing with temploral data, I'd suggest you read the book "Temporal Data & the Relational Model" by C.J. Date (possibly ignoring the implementation portion).
March 30, 2011 at 4:09 am
The goal is to version data so I can track changes and identify which versions of an entity were used in decsion making procceses.
Each live table would have 2 keys one to identify the entity (IDENTITY Column) within the table and another to identify the version of the entity (potentially ROWVERSION).
All non-current versions are housed in a separate AUDIT table leaving only the current version in the live table.
So my question was if anyone had any experience of using the ROWVERSION datatype in the manner described above.
I've never used this datatype so i've no idea if there are any issues with it or any pitfalls if i were to use it in the way i mentioned above.
March 30, 2011 at 4:13 am
Rowversion is not intended for what you are trying. Rowversion is typically used to detect what rows in a table have changed since a previous point in time. The value changes any time the row is updated. It's not used as an identifier.
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
March 30, 2011 at 4:15 am
Why not have the version as an identity column? Seems the obvious approach.
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
March 30, 2011 at 5:05 am
I also fail to see why an identity in the primary table doesn't work. In the audit table, would it not be the identity plus a datetime?
March 30, 2011 at 5:12 am
Steve Jones - SSC Editor (3/30/2011)
I also fail to see why an identity in the primary table doesn't work. In the audit table, would it not be the identity plus a datetime?
In the audit table it would be either the primary key of the primary and an identity to show the versions, or the primary key of the primary and a datetime to show versions. If datetime, must make sure that no two versions of an entity can be created at exactly the same time.
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
March 30, 2011 at 5:26 am
I'm currently using the IDENTITY datatype in the Live table its what I use to identify the entity in the live table i.e. PK value...
No 2 versions of the same entity can be laid down with the same datetime...
I currently use the IDENTITY column and the DateTime to identify a version.... what i was hoping to implement was an approach which would replace this composite version id (IDENTITY+DATETIME) with a single id value ...I'm limited in the structural changes i can implement here since this is not greenfield development. .. ergo my interest in ROWVERSION
Thanks for everyones input you have covered my primary question which was should/could I use the ROWVERSION datatype for this and in a nutshell the answer was no :).
Once again thanks to everyone for your suggestions and comments.
March 30, 2011 at 5:33 am
Mikey01 (3/30/2011)
what i was hoping to implement was an approach which would replace this composite version id (IDENTITY+DATETIME) with a single id value
Why?
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
March 30, 2011 at 5:42 am
Mikey01 (3/30/2011)
I'm currently using the IDENTITY datatype in the Live table its what I use to identify the entity in the live table i.e. PK value...No 2 versions of the same entity can be laid down with the same datetime...
I currently use the IDENTITY column and the DateTime to identify a version.... what i was hoping to implement was an approach which would replace this composite version id (IDENTITY+DATETIME) with a single id value ...I'm limited in the structural changes i can implement here since this is not greenfield development. .. ergo my interest in ROWVERSION
Thanks for everyones input you have covered my primary question which was should/could I use the ROWVERSION datatype for this and in a nutshell the answer was no :).
Once again thanks to everyone for your suggestions and comments.
If you look at the approach on the link I provided, it's two INTEGER values to make up a key with a version. It's smaller and easier to manage than datetime and indexes a bit better, being somewhat narrower. That's the approach I'd recommend.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply