Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstikal
Starting Member
19 Posts |
 Posted - 2015-04-09 : 16:35:00
|
| I am building a table change log that will track each attribute update and include the original and new values. [BatchYearMonthKey] [int] NULL, [BatchYearMonthDayKey] [int] NULL, [AccountID] [varchar](200) NULL, [Attribute] [varchar] (200) NULL, [Old_ValueAtrDefault] [varchar] (200) NULL, [New_ValueAtrDefault] [varchar] (200) NULL, [Old_ValueAtrLong] [varchar] (max) NULL, [New_ValueAtrLong] [varchar] (max) NULLThe challenge that the spectrum of varchar lengths across the table. I have one attribute that requires varchar(max) and all other attributes (about 40) are varchar (200). I am trying to accomplish the following: Account ID Status1 EnabledNow changed to AccountID Status1 DisabledMy log table will look like the following: [BatchYearMonthKey] BatchYearMonthDayKey] [AccountID] [Attribute] [Old_ValueAtrDefault] [New_ValueAtrDefault] [Old_ValueAtrLong] [New_ValueAtrLong] 201504 20150409 1 Status Enabled Disabled NULL NULLMy question: I created two fields (Old_ValueAtrLong and New_ValueAtrLong) dedicated for the one attribute that is a varchar (max). I was trying to avoid storing [Status] for example that's a varchar(200) in a field that is varchar(max). Is this the right approach? Or are there other recommendations in how to handle storing the data in the most efficient manner? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-09 : 18:39:30
|
| The storage used for varchar(n) or varchar(max) is based upon the data in the column. So if it's 200, then it'll be 202 bytes since it's n+2. If it's 1GB, it'll be 1GB+2 bytes.So just store it in the varchar(max) column.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-10 : 10:20:41
|
| Personally, as much of a pain as it is, I would use both varchar(200[+]) and varchar(max). From what I've read, and the testing I've seen, varchar(max) values do have more overhead. However, you could reduce that overhead by telling SQL to leave the varchar(max) values on the same page whenever possible. |
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2015-04-10 : 10:36:15
|
| Also - you cannot index a varchar(max) . You can have it in an include of an index, but not index the column itself. |
|
|
|
jstikal
Starting Member
19 Posts |
Posted - 2015-04-10 : 12:43:35
|
| I'm not quite sure what the volume of updates will look like which could determine the management of the data. Thank you everyone, I greatly appreciate the feedback! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-12 : 07:51:07
|
| Do you need to store both the Old and New?if you have a chronological record of changes the "old" is in the next-earlier record. That would halve your storage.We also do not store current value - that's in the main table :) so we only store the "old" values when a row is updated (using the [deleted] pseudo-table in a Trigger) |
|
|
|
jstikal
Starting Member
19 Posts |
Posted - 2015-04-13 : 10:20:59
|
| Thank you Kristen for the feedback. I don't have to store both but having the changes in a single record as compared to a new record with only the changes would provide the same level of detail. The extraction would just have to change. I also looked in to the CDC feature which is similar to the implementation that you described but that includes all attributes and a scan of each column would be needed to identify what changed. I think for now I will include both old and new values in a record and see how the table performs in production. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-13 : 13:24:15
|
You could keep the existing reporting code if you create a VIEW (which gets "a record" and "Next earlier record" and in effect presents Old / New data columns, in the same way the table does now.quote:
a scan of each column would be needed to identify what changed
First audit system I ever wrote was like that (not my choice!) It was absolutely dreadful. Impossible to report off, the procedures for creating the data, and for attempting to report on it!, were huge. And the data itself was absolutely massive ...Much better to store the "before" row IMHO. There might be instances where the only thing which changes is the Status Column, or similar, where it is "cheaper" to store just the changes to that, but for general record auditing I have found storing the "before" record works very well. |
|
|
|
|
|
|
|
|
Optimize memory with varchar(max) datatype