DBCC Fails When Multiple Timestamp Columns Are In View

• Stamey

  SSCertifiable

  Points: 5600

  More actions

  July 24, 2012 at 6:02 am

  #264250

  Background:

  Developers took note of rule regarding Full Text Indexes, in that in order to have incremental updating of a full text index occur, you must have a TimeStamp column in your table. (http://technet.microsoft.com/en-us/library/ms142575.aspx)

  What the docs don't address, and caused the developers to make assumptions on, is what if you are creating an FTI on a view, which combines two, or more tables, that have TimeStamp columns in them. My developers thought, "if a TimeStamp column is required to get incremental updates to a FTI, then we need to pull that column into the view so it is eligible for incremental updates, and since we pull data from three tables, and any one of them could change independently of another, we need to include the TimeStamp columns from each table in our view."

  Right or wrong, I understand where they are coming from, because the documentation does not address this situation, and SQL Server does allow multiple TimeStamp columns in a view.

  Now, as this is Enterprise version of SQL Server 2005, we also have an index on that view.

  Now to the problem. When I run integrity checks on the DB, I get an error when DBCC gets to that view, with the error:

  Msg 2738, Level 16, State 2, Line 1

  A table can only have one timestamp column. Because table '#priv_iv_temp_table9674_0' already has one, the column 'PharmacyIdInsertUpdateTimestamp' cannot be added.

  Outcome: Succeeded

  This is because DBCC looks at a view as a table, and applies the rules for tables to the views.

  In my testing, I have found that DBCC only has a problem with multiple TimeStamp columns in a view if the index is on the view. If the index is not on the view, DBCC sails right through it, even though it, in this case, has three TimeStamp columns in the view.

  My goal is to stop the DBCC errors as it fails my data integrity check job. I am open to design changes, provided I don't adversely affect performance.

  I have posted this question to design forums but have never received a satisfactory response regarding changes to my design. It seems like FTI documentation/knowledge as as elusive as Bigfoot.

  Thanks,

  Chris

  Learning something new on every visit to SSC. Hoping to pass it on to someone else.