January 19, 2014 at 9:59 pm
Hi,
I have a simple yet maybe complex question. I never created or worked with Partitioned Tables yet.
Lets pretend I have a table in Sales.SalesOrderDetails in AdventureWorks (I don't remember the exact table name) with a million records.
Next lets pretend there is a column name order_date and values ranging from 1/1/2012 to 1/19/2014.
In addition the table is partitioned based on the order_date into three filegroups, one per year.
This table is a heap and I decided to create an index idx_order_date on column order_date.
Where is this index stored? Is it stored across the three file groups?
Thanks!
January 19, 2014 at 11:55 pm
Depends. If you create the index on the partition scheme, it will be partitioned like the table and stored across the three filegroups. If you create the index on a filegroup, it's not partitioned and is stored entirely in that filegroup.
Basically, it's stored where ever the ON clause of the create index specifies.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 7:45 am
GilaMonster (1/19/2014)
Depends. If you create the index on the partition scheme, it will be partitioned like the table and stored across the three filegroups. If you create the index on a filegroup, it's not partitioned and is stored entirely in that filegroup.Basically, it's stored where ever the ON clause of the create index specifies.
Thanks Gila.
If I don't specify an on clause, will it by default be stored on the primary filegroup?
IE:
CREATE NONCLUSTERED INDEX idx_orderdate SalesOrderDetail(order_date)
January 20, 2014 at 7:51 am
It'll be on whatever filgroup is defined as the default one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply