September 15, 2011 at 6:48 am
According to http://technet.microsoft.com/en-us/library/ms187526.aspx,
"SQL Server does not align the index with the table if you specify a
different partition scheme or a separate filegroup on which to put the
index at creation time."
However, the same article then goes on to say
"An index does not have to participate in the same named partition
function to be aligned with its base table. However, the partition
function of the index and the base table must be essentially the
same."
My question: is the below index aligned? It's using the same
partition function, but a different schema and filegroup. I _think_
it is (a partition SWITCH does work), but I'd like to be sure. Is there a way to check?
Thanks!
Repro script.
CREATE PARTITION FUNCTION mypfunc (int)
AS RANGE LEFT FOR VALUES (10, 20, 30, 40)
CREATE PARTITION SCHEME mypscheme
AS PARTITION mypfunc ALL TO
(switch_current)
CREATE PARTITION SCHEME mypscheme_index
AS PARTITION mypfunc ALL TO
(switch_current_indexes)
CREATE TABLE switch_data_current (id int, myval VARCHAR(50))
ON mypscheme(id)
CREATE NONCLUSTERED INDEX idx_sdc_myval
ON switch_data_current(myval)
ON mypscheme_index(id)
INSERT INTO switch_data_current VALUES (1, 'one')
INSERT INTO switch_data_current VALUES (2, 'two')
INSERT INTO switch_data_current VALUES (10, 'ten')
INSERT INTO switch_data_current VALUES (15, 'fifteen')
INSERT INTO switch_data_current VALUES (30, 'thirty')
INSERT INTO switch_data_current VALUES (41, 'fortyone')
September 15, 2011 at 7:06 am
"Aligned" in this case essentially means "same columns and function and file(s)". It just makes the query engine do less work when it does partition switching. It doesn't make switching possible, it makes it quick and efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2011 at 7:39 am
Okay, so is the example I gave aligned? The index is on a different filegroup, but still using the same Function. So technically it meets the criteria in
http://technet.microsoft.com/en-us/library/ms187526.aspx
"An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions."
Additionally, this implies that because I can switch it, it's aligned. But given the contradictory advice, I can't be sure.
"To enable partition switching, all indexes on the table must be aligned."
September 19, 2011 at 6:06 am
Yes, it's aligned.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply