Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioned Table and Index Storage Confusion Expand / Collapse
Author
Message
Posted Sunday, January 19, 2014 9:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:08 AM
Points: 202, Visits: 565
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!
Post #1532495
Posted Sunday, January 19, 2014 11:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532506
Posted Monday, January 20, 2014 7:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:08 AM
Points: 202, Visits: 565
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)
Post #1532648
Posted Monday, January 20, 2014 7:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
It'll be on whatever filgroup is defined as the default one.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1532649
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse