locked
Timestamp columns when creating a full text index on a view? RRS feed

  • Question

  • I have several DBs that have full text indexes in them. The way the developers designed them was underlying tables with a view over that, and the full text index on the view. The underlying tables have timestamp columns in them, and the view created on those tables contains the timestamp columns from every underlying table. This causes an error with I run DBCC integrity check on DB's which have this kind of FTI/view configuration.

    I have to figure out if the developers should have put those timestamp columns into that view. I think I know why they did it, because I have read the same docs, which state that for incremental updates to the full text index to be possible, you must have a timestamp column in your underlying table.

    If they have the index on a view, and have properly provided timestamp columns in each of the underlying tables, which one, or all, should be included in the view that the FTI is built on?

    Can anyone definitively answer this question?

    Thanks,
    Chris

    Monday, June 4, 2012 6:57 PM

All replies

  • Hi Chris,

    As you pointed, timestamp is required to perform the incremental population in full-text index. This is applied to the view, timestamp should be included from base table to use incremental population. Please see: FullText in a Indexed View - Incremental Population.

    But if there does not any timestamp exist, it will result in a full population instead of incremental, no error involved in this scenario. I have tested it on my environment with DBCC CHECKDB command.

    >>If they have the index on a view, and have properly provided timestamp columns in each of the underlying tables, which one, or all, should be included in the view that the FTI is built on?
    In my opinion, timestamp columns in the same table, which contains columns included to full-text index can be referenced in the view.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    Tuesday, June 5, 2012 6:19 AM
  • Is there anyone that knows for sure? I appreciate your reply, but I need a more sure answer, rather than an opinion.

    In my case, I have a view based on three tables, all of whom have a timestamp column in them. All three timestamp columns are included in the view, and there is an index on the view. Because of the timestamps in the view and the index on said view, DBCC CheckDB freaks out about it every time I run it, therefore I need to understand what I can or need to do to change my design so DBCC can function properly.

    This is wjhy I am asking about the timestamp columns in this situation.

    Thanks,
    Chris

    Monday, June 11, 2012 1:54 PM
  • Chris,  What are the timestamp columns defined as or called in your Indexed View?  Technically, there is only supposed to be one timestamp per table and I would expect this to extend into views.

    Could you try recasting the view so that two of the timestamps are BINARY(8) or VARBINARY(8) and see if that addresses your DBCC issue?  Of course, choose to keep the one that seems most meaningful since (ideally) the application will update that row in all circumstances.

    For what it is worth, if you use CHANGE_TRACKING AUTO for your indexes, you do not need timestamps at all.  The engine will keep track of changes and process them as soon as possible.  This is subject to the priority of your indexing.

    I recommend CHANGE_TRACKING AUTO over INCREMENTAL.  It much more closely matches what people expect to happen.  Check out the pros/cons to see if that would fit your model.   Both CHANGE_TRACKING and INCREMENTAL assume a limited set of changes being made, but INCREMENTAL has a lot of overhead in determining what has changed.

    FWIW,

    RLF


    • Edited by SQLWork Monday, June 18, 2012 3:45 PM
    Monday, June 18, 2012 3:44 PM
  • They are defined as timestamps in the view. I guess the SQL Server teams forgot to make it illegal to have multiple timestamp columns in a view. It actually doesn't cause any problems until you put an index on the view, then with what DBCC does to check the index, I suppose, DBCC validates the columns while looking at the view as a table, and has a problem with it.

    If I recast the additional timestamp columns as VarBinary(8) DBCC no longer has a problem with the timestamps.

    The point here is that I need to know what to recommend to the development staff when I ask them to change their design because this causes issues for me, doing DB maintenance. My original question above needs to be answered because that's the first thing the development staff will ask when I tell them about the needed design change.

    There may be several ways to make this better, but in addition to finding a better way to do it, I need to be able to back up why the way it is now is wrong, as development may see this is a big change on their end, depending on what it affects, and the way this shop works the DBA's do not always know all of the idiosyncrasies of the various processes in place.

    I can sort of understand where they are coming from, as there is no documentation I have been able to uncover stating how to handle timestamps in a view when there is a timestamp in each underlying table that participates in said view. Their goal is to make sure that FTI can keep the indexes updated without doing a full rebuild, as we already have performance problems with that. We load a lot of data, and with the amount of data that is loaded, the FTI gets completely rebuilt sometimes. We are trying to give it every opportunity just do an incremental update rather than a rebuild, as the rebuilds take a long time and tie up systems resources.

    Thanks,
    Chris

    Monday, June 18, 2012 4:51 PM