So, it was announced that SQL2012 SP1 CTP4 was released yesterday. You've likely already heard this (more than once, everyone seems to revel in repeating announcements from the team). And it contains a new feature (I was surprised, thought that wasn't supposed to happen, new features in service packs, meanwhile…) called Selective XML Indexes. Interesting concept, especially because dragging around a primary XML index that's 3-6 bigger than the data itself is a burden. But, without it, querying on any non-trivial XML is quite slow. To put it nicely…
Downloaded and installed the SP (remember its a CTP, DON'T install this on a production system). Got my XML test bed out to try. First problem: no docs on the feature. Well, the only information that exists (OK, that I could find quickly) is in Michael Rys' TechEd US/Europe talks. Downloaded, listened to them, you can find them with a web search. I'll try not to repeat his content. There's DDL in his talk. So:
create table foo (id int identity primary key, thexml xml);
create selective xml index fooidx on foo(thexml)
for
(
pathname1 = 'foo/bar/baz',
pathname2 = 'foo/bar2'
);
Msg 9539, Level 16, State 1, Line 1
Selective XML Index feature is not supported for the current database version
Bummer. Really? Well, there is a trick to it (thanks, Michael).
exec sys.sp_db_selective_xml_index null, 'on' — turn on for current database
BTW, if you want to restore/attach this database now to a pre-SP1 system, you have to turn the feature off.
exec sys.sp_db_selective_xml_index null, 'off'
Which likely means deleting all the selective XML index stuff you have in the database too. Haven't tried it yet, though. And first parameter is the database name. NULL means "current database".
And it works now. So now we can all play along at home. 😉 Let a hundred selective XML indexes bloom. Michael suggests there will be BIG performance improvements and little disk space used.
@bobbeauch