Full-text indexes in partitioned tables

Question

Hi.
Few questions.

I'm trying to create a unique index on a partitioned table. Why must the partitioning column be a subset of any unique index for that table (or a primary key)?

Adding a little complexity. I want to partition a table which has a full-text indexed column. For a full text index, I need a unique single-column index. However, I cannot create such an index unless it includes the partitioning column (see above).
      -- it seems a workaround would be to create the unique index for full-text in a separate filegroup. Can you explain the reasons behind this?

And last question: Is it possible to full-text index only one partition?

Thanks a lot!

Answer 1

If the unique index does not contain the partitioning column then SQL Server would have to check for duplicate in all partitions for each insert or update of the row (e.g. you partition on columns "a" and you have unique index on "b", then (a,b) rows (1,2) and (3,2) could be in different partitions). This would lead to very inefficient maintenance of uniquness and that is the primary reason why SQL Server 2005 enforces the partitioning key to be contained in the unique key (because then the "duplicates" will always be in the same partition).

You can create unique partitioned index by partitioning it on the key, or create non-partitioned unique index regardless if and how the table is partitioned. This is what you see by creating the index in a "separate" filegroup - such index is not partitioned. If you don't specify filegroup, the index will be (by default) partitioned exactly as the table.

No, it is not possible to full-text index only one partition directly. But you can create indexed view covering only part of the partitioned table and create fulltext index on the indexed view.

Lubor

Answer 2

I have a partition table and have to implement a full text search on the table.

I understand that the Full Text index and the Partition cannot co-exist on the same table.  

I have created all the partitions  on a single filegroup (default primary).  

Can I not create indexed view on all the partition of a table ? 

What happens if I map each partition to a different file group ?

Please advise.

Dhirendra

Answer 3

At a minimum better reads. Especially if the filegroups are on different disks on a high performance SAN. Reduce the contention even further by putting your fulltext index on a separate filegroup too and then reference your clustered unique column in the definition of your fulltext index.

---

DJ Baby Anne's Biggest Fan................