Ask Index creation principle

hi,
i want to know the basic principle to create simple index or composite
index, e.g.
Table1
col1, col2, col3, col4, col5
select * from Table1 where col1 = 0
select * from Table1 where col1 = 1 and col2 = 'testing'
select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
should i create simple index on col1, col2, col4 and col5 respectively? or
create
1. a simple index on col1
2. a composite index on col1 and col2
3. a composite index on col1, col4 and col5
thanks!
Hello Mullin,
When designing an index strategy one has to find a balance. This balance
is goverened by typical workloads sent to the server. So, what you need
to do is run profiler on your machine for a typical workload - this
could be for 24 hour period, or the working day for instance.
You then need to look though your profiler trace (tracing to a table
will be ideal) for the most common queries to your database. If this
table appears regularly in your common queries, then make sure the most
popular queries are indexed.
You also need to rely on the user experience of the application using
this data. If the user experience is satisfactory then you don't need to
add indexes. Only add indexes where necessary as they add overhead.
So, from your example below, let's say that query number 2 appears the
most in your profiler trace. You could then place a composite index on
col1 and col2.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mullin Yu wrote:
> hi,
> i want to know the basic principle to create simple index or composite
> index, e.g.
> Table1
> col1, col2, col3, col4, col5
> select * from Table1 where col1 = 0
> select * from Table1 where col1 = 1 and col2 = 'testing'
> select * from Table 1 where col1= 2 and col4 = 5 and col5 = 'abc'
> should i create simple index on col1, col2, col4 and col5 respectively? or
> create
> 1. a simple index on col1
> 2. a composite index on col1 and col2
> 3. a composite index on col1, col4 and col5
> thanks!
>