SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Fails When Multiple Timestamp Columns Are In View


DBCC Fails When Multiple Timestamp Columns Are In View

Author
Message
Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 1048
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search