Beginner's Question on Cluster Indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Beginner's Question on Cluster Indexes

Author

Topic

flanz_g
Starting Member

22 Posts

Posted - 2007-01-24 : 12:40:36

Hi,
Imagine a group of tables headed by Columns: "Day","Customer",and "Town". With One C.Index already built based on "Day" and "Customer". Most queries have only these 2 Columns in their WHERE Clauses.
Now if a new group of Queries are to be run where the WHERE Clause will take "Day" and "Town" but not "Customer".
Question:

Is it better to a) add a New - 2nd C.Index to each table based on "Day" and "Town" or b) to remove it and Create a New (Only one) Cluster Index based on 3 Columns: "Day","Customer","Town" ?

Appreciate any advice,

Gezza

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 12:43:19

you can only have one clustered index per table.
CI will be used if the first column in CI is in the where clause.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

flanz_g
Starting Member

22 Posts

Posted - 2007-01-24 : 18:29:48

Why have 2 columns in CI. Does the 2nd ever get used?

Can there be more Columns in CI than 2?

Please reply. (I hate reading those fat books on SQL!)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-25 : 04:59:20

read those fat books

you can have more than 2.
the point of more columns in CI is if you query only those columns or always join on
them then it will speed things up considerably.

Google up on Clustered index and you'll see.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

flanz_g
Starting Member

22 Posts

Posted - 2007-01-25 : 15:09:13

bless your little cotton socks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources