0

We want to add full-text index on a table which has composite primary key. The requirement for a full-text index is that table has to have single-column unique index which will be used for full-text.

Thus primary key cannot be used (it is composite). I was going through table columns but neither of them is guaranteed to be unique, except rowversion column.

According to MSDN - rowversion seems to be guaranteed to be unique, but I am wondering whether it is a good idea to put unique index on it and use it for full-text then.

michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63
  • 1
    Is there a reason not using int / bigint as identity? – Eric Apr 14 '16 at 09:34
  • 1
    See [this answer](http://stackoverflow.com/questions/16521372/consequences-of-indexing-the-rowversion-timestamp-column-on-sql-server) for a very cogent reason for why you wouldn't want to do this. Every time you update a row the indexed value will change which will result in massive fragmentation and a performance hit. – strickt01 Apr 14 '16 at 09:49
  • Unfortunately yes, we use two bigint columns as primary key and we cannot change it. We can also create indexed view which will use PK made of single bigint column but to create such view a use of non-deterministic function is needed, so no luck :( – michal.jakubeczy Apr 14 '16 at 09:50
  • 3
    Can you add an IDENTITY column to the table and create a unique index on that column. – Sean Pearce Apr 14 '16 at 09:58
  • @SeanPearce thanks, that advice was helpful. I think Eric said the same, I just did not get it :) – michal.jakubeczy Apr 14 '16 at 11:30

0 Answers0