About indexes

[Puiu]

I have a database with 10 tables (give or take). The primary keys for these tables are IDENTITY columns. I can tell you already that there will be lots of joins.

In this case I was thinking of creating a covering clustered index for each table for the primary key and some other column that would be often required in those joins.

I was also thinking that I could add an extra nonclustered index on other less frequently used columns.

I also know that the inserts will be made on at a time (btw what does

 

[Nerseus]

You have a lot of questions, so Ill try them one at a time:

In this case I was thinking of creating a covering clustered index for each table for the primary key and some other column that would be often required in those joins.

If you want to do this "right", I would start by looking at your main queries (or all of them if you dont have too many) and profiling them. For SQL Server, run this in Query Analyzer:

set statistics io on
set statistics profile on

This will show what the optimizer is picking (which index, if any) and whether its seeking or scanning tables.
From that you can decide if you want a covering clustered index or just a regular index.

I was also thinking that I could add an extra nonclustered index on other less frequently used columns.

Same as above - Id do some checking first, unless you have a strong gut reaction that certain columns are always referenced in joins or where clauses.

[qutoe]what does

 

[Puiu]

Thank you again for your answer, Nerseus!