Creating a snapshot/transactional table by appending

Question

Creating a snapshot/transactional table by appending

Viewing 4 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Firstly, why do you want to duplicate data in the table? Why create a new row when a column changes? Why not just change the column value?

Second, ditch the nolock hints. They're not good practice and most users don't appreciate reports that can be wrong as a result of the hint.

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

FromSAStoSQL SSC Enthusiast Points: 107 More actions · Answer 2

Hi,

Thanks for responding. I suppose in my mind I'd like to be able to see the changes historically. So if I want to report on how many applications were in the state of x on a particular date I can. If I overwrite the status every time I can't do that.

Otherwise I think what I have done is pretty much there. Minus the nolock - thanks for the advice. This was copied from someone who helped implement some SQL which feeds a third party software and the nolock was in their SQL.

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

The you would have something like a status history table which records the changes, rather than duplicating the entire row for one column change.

As it currently is, you've got a pretty space-inefficient design, you'd need a timestamp as part of the primary key as the rest of the row gets duplicated and you'd have to write relatively complex T-SQL to get the current version of the row out.

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

FromSAStoSQL SSC Enthusiast Points: 107 More actions · Answer 4

OK thanks - I'll have to have a read up on how to create the history table.