Index Ratification

Hi all,
I'm going through the process of identifying which items in my tables to
place indexes on.
I'm using the following general rules in deciding usage. Can anyone advise
whether these are correct?
- All primary keys to be a clustered index.
- All foreign keys to be unclustered.
- Items which are heavily used in WHERE etc are also unclustered.
DJWA bit simplified. What is the datatype of your PK? Is it int (identity) or
something else? Is it sequential?
As far as th FK goes yeah, I would say yes. There are no really general
rules, it all depends but I would say that those steps are a good start. You
do need to think really hard on your indexing however, perhaps performanse
could be better with some changes.
MC
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:4396883f$1_1@.glkas0286.greenlnk.net...
> Hi all,
> I'm going through the process of identifying which items in my tables to
> place indexes on.
> I'm using the following general rules in deciding usage. Can anyone
> advise
> whether these are correct?
> - All primary keys to be a clustered index.
> - All foreign keys to be unclustered.
> - Items which are heavily used in WHERE etc are also unclustered.
> DJW
>|||Daniel,
I would generalize it like this:
For a VERY write intensive table:
Only clustered index on the PK being a column containing sequential values.
For a more read intensive table:
Nonclustered on the PK (since we will usually only fetch one row at a time
from it)
Nonclustered for searches that find few rows.
Clustered (or covered nonclustered) for searches that find many rows
(typically FK or Date columns).
The "most important" FK of the table is usually a good column to use for the
clustered index. Many searches on it, returns many rows, small data type :-)
/ Tobias|||Is this an OLTP (operational) database or a Data Warehouse used for
reporting purposes?
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:4396883f$1_1@.glkas0286.greenlnk.net...
> Hi all,
> I'm going through the process of identifying which items in my tables to
> place indexes on.
> I'm using the following general rules in deciding usage. Can anyone
> advise
> whether these are correct?
> - All primary keys to be a clustered index.
> - All foreign keys to be unclustered.
> - Items which are heavily used in WHERE etc are also unclustered.
> DJW
>|||It's an OLTP, JT
"JT" <someone@.microsoft.com> wrote in message
news:e8zGYbz#FHA.228@.TK2MSFTNGP12.phx.gbl...
> Is this an OLTP (operational) database or a Data Warehouse used for
> reporting purposes?
> "Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
> news:4396883f$1_1@.glkas0286.greenlnk.net...
>|||Then perhaps optimizing inserts/upadtes/deletes is more important than
optimizing queries. In that case, minimizing the number of indexes and
avoiding clustered indexes.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:43970494$1_1@.glkas0286.greenlnk.net...
> It's an OLTP, JT
> "JT" <someone@.microsoft.com> wrote in message
> news:e8zGYbz#FHA.228@.TK2MSFTNGP12.phx.gbl...
>sql