patternsqlMinor
Full-text index in partitioned table
Viewed 0 times
fulltextindexpartitionedtable
Problem
I have a table with just over a million records with a full-text index.
This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact same structure in a separate database at the end of every year. This second table has approximately 3+ million records.
I can only guess why this has been done, but now I've been asked to merge this two tables back into a single one, and partition it. I'm running SQL Server 2005.
Will the full-text search work in partitioned tables?
Are there any recommendations regarding this situation, or anything I should pay attention to?
This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact same structure in a separate database at the end of every year. This second table has approximately 3+ million records.
I can only guess why this has been done, but now I've been asked to merge this two tables back into a single one, and partition it. I'm running SQL Server 2005.
Will the full-text search work in partitioned tables?
Are there any recommendations regarding this situation, or anything I should pay attention to?
Solution
I did some more research and found a couple of useful articles with best practices regarding table partitioning and full-text searching on large tables. As I haven't received any answers to this question, I thought I'd post what I've found here for future reference.
I'm still reading those, but for what I've already read, I've found both of them useful and relevant to this particular situation:
SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned:
The observations in this paper are based on tests run in the SQL
Server Customer Lab for a customer who needed to scale up full-text
search to a much greater potential volume. The paper describes the
customer scenario, provides an overview of SQL Server 2005 full-text
concepts that bear on the results, and offers lessons learned and
recommendations for using full-text queries on large catalogs.
Partitioned Tables and Indexes in SQL Server 2005
Summary: Table-based partitioning features in SQL Server 2005 provide
flexibility and performance to simplify the creation and maintenance
of partitioned tables. Trace the progression of capabilities from
logically and manually partitioning tables to the latest partitioning
features, and find out why, when, and how to design, implement, and
maintain partitioned tables using SQL Server 2005. (41 printed pages)
I'm still reading those, but for what I've already read, I've found both of them useful and relevant to this particular situation:
SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned:
The observations in this paper are based on tests run in the SQL
Server Customer Lab for a customer who needed to scale up full-text
search to a much greater potential volume. The paper describes the
customer scenario, provides an overview of SQL Server 2005 full-text
concepts that bear on the results, and offers lessons learned and
recommendations for using full-text queries on large catalogs.
Partitioned Tables and Indexes in SQL Server 2005
Summary: Table-based partitioning features in SQL Server 2005 provide
flexibility and performance to simplify the creation and maintenance
of partitioned tables. Trace the progression of capabilities from
logically and manually partitioning tables to the latest partitioning
features, and find out why, when, and how to design, implement, and
maintain partitioned tables using SQL Server 2005. (41 printed pages)
Context
StackExchange Database Administrators Q#14411, answer score: 6
Revisions (0)
No revisions yet.