Non Clustered Primary Key

Question

Non Clustered Primary Key

Oscar Zamora

SSCrazy

Points: 2067
More actions
January 19, 2004 at 8:56 am

#84015

When I create a table specifying a NON clustered primary key to be stored into the INDEX filegroup, does it mean that just the INDEX generated from that primary key will be stored into that filegroups? Or the whole row will be stored into the file group (like clustered primary keys)?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Taken from BOL filegroups -> index placement

By default, indexes are created on the same filegroup as the base table on which the index is created. However, it is possible to create nonclustered indexes on a filegroup other than the filegroup of the base table. By creating the index on a different filegroup, you can realize performance gains if the filegroups make use of different physical drives with their own controllers. Data and index information can then be read in parallel by multiple disk heads. For example if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used with no contention. However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used, resulting in no performance gain.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Oscar Zamora SSCrazy Points: 2067 More actions · Answer 2

Yes, I agree with what you said; that is why I am using a different fliegroup located in a different container. I was not sure if it will behave as it was a CLUSTERED index (where the whole row is moved to the specified filegroup).

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

I would recommend making it clustered if sequential processing is important. As long as the first key in the clustered index is inserted in order you shouldn't have problems with page breaks.

Also, uniquely keyed composite clustered indexes make great lookup tables.

It's a good idea that all tables have a CU, SQL will often handle it sub-optimally otherwise.

Signature is NULL

Oscar Zamora SSCrazy Points: 2067 More actions · Answer 4

Thanks Calvin Lawson. The data is not stored sequentially. That is why I prefered to create a NON-clustered index with FILLFACTOR=50 so it has enough space to avoid frequent page splitting (in a different filegroup).

This table is a part of a partitioned view, which can contain between 20 to 50 million rows.