Where to create NonClustered Primary Key?

[Andel]

Which one will give you best performance: Creating Nonclustered primary key in the Primary filegroup or in its own filegroup?

Thanks in advance.

Andel
andel@barroga.net

http://www.iluko.com

 

[ESquared]

I thought the primary key was always clustered?

As for performance, it *sounds* like having it in its own filegroup would be faster, but why not try it both ways and see? Real-world environments with real-world production data (millions of rows) can give unexpected results.

 

[SQLSister]

Esquared the primary key is created as clustered by default, but you can create it as non clustered and create some other index as the only clustered one.

Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can aid this process.

So different filegroups can aid performance but they should be on different physical drives with different drive controllers.

This is good when you have lots of users accessing simultaneously, but may not be necessary unless you are having performance problems.





Questions about posting. See faq183-874

 

[Andel]

Thanks so much!

Andel
andel@barroga.net

http://www.iluko.com