March 2011 - Posts

Partitions and FullText - not an option

I'm a strong advocate of partitioning having deployed solutions through SQL 2000, 2005 and 2008, in fact federated servers is about the only aspect of partitioning which I've not deployed in a production environment.

Recently I've been applying partitioning to a logging table, approx 2 million rows a day, I have an automated solution which handles archiving and removing aged data through partition switching (  part of the subject matter of my presentation to VBUG "Gone in 60 nano seconds" ). 

Having resolved the partitioning I decided that fulltext indexing would now be very useful, sigh!, sadly fulltext indexing requires a single column unique index and I've not deployed many solutions where the partitioning column is also the ( single column ) primary key and as you'll know all unique indexes on a partitioned table must include the partitioning column. I just can't figure why a Primary Key, no matter the column makeup, isn't acceptable to fulltext. There are many irritations with native partitioning, the unique index issue being the most irritiating which forces me to use either triggers or indexed views to maintain uniqueness in a single column on a partitioned table.

You can create a unique index if you don't make it part of the partitioning schema ( e.g. on the primary filegroup ) but that means your partition switching will not work any longer. Partitioned views don't suffer this way but of course you can't do partition switching. So I'm in a bit of a catch 22 situation where I need two incompatible features to work together.

In the tests I carried out using an index on a seperate filegroup I was pleasantly pleased to see how much better fulltext indexes perform compared to when I last tried with SQL 2000,

While I'm having a gripe I'd also warn that by default SSMS does not script partitioning options, so if like me you write your create scripts in SSMS and then script the object back out to get all the drop and options SSMS will kindly not refer to partitioning at all and when you rerun the generated script you get a non partitioned table, and it's not as if these options are the most easy to find either in SSMS, it's a bit like using a route planner which by default leave out the road numbers and town names ( If you're only of the sat-nav generation then route planners provided printed instructions and a map - think of it as you providing the vocie over for your sat-nav - oh and maps are like google   bing maps but on paper )

Tech Days in UK

There's a series of Tech days announced

including one for Database  on 26th May in London,

If these days follow the standard set by the BI Seminar earlier this month in Reading then this is something you probably shouldn't miss.


Posted by GrumpyOldDBA with no comments
Filed under: