Clustered and Non-Clustered 1

[jestrada101]

What is the difference between these in SQL Server database? Where would either be good to enable?

Thanks for any info..

JE

 

[mrdenny]

With a clustered index the data in the index is stored by sort order. This can be used to speed up searches as well as order by statements.

A non-clustered index is an index that stores the data in the order it was entered.

You can only have one clustered index per table.

By default when you create a table in enterprise manager and put a primary key on the table the clustered index in created on that key. This is usualy a good place for the clustered index, however there have been times that I have moved the clustered index to other columns within the database.

Is that what you were looking for?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jestrada101]

Thanks! That's exactly what I was looking for. In SQL Server, you can create a Primary Key against several fields in the same table, right? Can indexes can be created against all of these?

Also, here's a STAR for the feedback...

 

[mrdenny]

Correct you can create a Primary Key on a single column or multiple columns. You can also create an index on a single column or multiple columns.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Remember that when you create a clustered index on something other than an identity field, you may seriously slow up inserts and updates when your table gets large.

While you can have a multiple field clustered index, it is often not a good idea from a performance point of view. This is especially true if it is a primary key that will have related tables with the same multiple fields as the forign keys.

Questions about posting. See faq183-874