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 �bulk insert� mean ?). In this case I suppose there won�t be a problem with the clustered index on the IDENTITY column.

 

I was also wondering what is the query that will show me the existing indexes for a given table?

[Nerseus]

You have a lot of questions, so I'll 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 don't 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 it's 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 - I'd 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 �bulk insert� mean

A bulk insert is done through BCP (command line tool) or DTS in Bulk insert mode. Bulk inserts are very fast as they have options to skip a lot of integrity checks. For example, you can bulk insert rows with no constraint checks (you could end up with broking foreign keys). This wouldn't normally apply to your transactional database except for "batch jobs" or "interfaces" - programs or things that run off hours that do lots of DB reading/writing.

 

I also know that the inserts will be made one at a time. In this case I suppose there won�t be a problem with the clustered index on the IDENTITY column.

It's not a matter of one at a time. When you have a clustered index on a table, the order of rows must match the order on the physical hard disk. So if you happen to create a cluster on a column whose value can be anything (versus an identity column), you take a chance that an insert could mean rearranging ALL the data in the table on the disk. Even if your cluster is on an identity column (plus other data), you take a slightly bigger hit on inserts than a non-clustered index. As long as the first column in your clustered index is something like an identity column, you should be Ok.

 

I was also wondering what is the query that will show me the existing indexes for a given table?

You can get them with sp_help <table> or more specifically sp_helpindex <table>.

 

-ner

[Puiu]

Thank you again for your answer, Nerseus!