\nSo, 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…\n<\/p>\n
\nDownloaded 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:\n<\/p>\n
\ncreate table foo (id int identity primary key, thexml xml);\n<\/p>\n
\ncreate selective xml index fooidx on foo(thexml)
\nfor
\n(
\npathname1 = 'foo\/bar\/baz',
\npathname2 = 'foo\/bar2'
\n);\n<\/p>\n
\nMsg 9539, Level 16, State 1, Line 1
\nSelective XML Index feature is not supported for the current database version\n<\/p>\n
\nBummer. Really? Well, there is a trick to it (thanks, Michael).\n<\/p>\n
\nexec sys.sp_db_selective_xml_index null, 'on' — turn on for current database\n<\/p>\n
\nBTW, if you want to restore\/attach this database now to a pre-SP1 system, you have to turn the feature off.\n<\/p>\n
\nexec sys.sp_db_selective_xml_index null, 'off'\n<\/p>\n
\nWhich 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".\n<\/p>\n
\nAnd it works now. So now we can all play along at home. \ud83d\ude09 Let a hundred selective XML indexes bloom. Michael suggests there will be BIG performance improvements and little disk space used.\n<\/p>\n
\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
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, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,37],"tags":[],"class_list":["post-472","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sql-server-xml"],"yoast_head":"\n