Monday, June 20, 2005 10:34 PM
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!
Tuesday, June 21, 2005 6:26 PMIf 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.
Monday, May 18, 2009 12:34 PM
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 ?
Wednesday, January 12, 2011 6:35 PMAt 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................
- Edited by Abercrombie07 Wednesday, January 12, 2011 6:36 PM Clarification