Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>