Question about index

Hi, all.
Recently one of my collegues asked me to create two indexes:
ix_1 : col1, col2, col3
ix_2 : col1, col2
I wonder this is a good practice to create the second index, ix_2. Because
ix_1 includes all the columns for ix_2.
And another question. Someone asked me to create indexes like following:
ix_1: col1, col2, col4
ix_2: col1, col2, col3
How about this case? Is it a good practice to create each index, or can I
make just one index like following:
ix_1:col1, col2, col3, col4
Any suggesion would be appreciated.See in line
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:uBjyLYxrDHA.3456@.tk2msftngp13.phx.gbl...
> Hi, all.
> Recently one of my collegues asked me to create two indexes:
> ix_1 : col1, col2, col3
> ix_2 : col1, col2
> I wonder this is a good practice to create the second index, ix_2. Because
> ix_1 includes all the columns for ix_2.
Depend on queries being run and the nature of the data.
If the col3 is small then there will be little difference. However, if col3
has an average size of say 1000bytes, then ix_2 will provide some
performance gains in queries that do not need col3. This is because there
will be less physical reads in ix_2
Eg lets say col1 and col2 are ints and col3 is a nvarchar(4000) with an
average of 1024 bytes per record. Also lets say there are 1 million rows in
this table.
So ix_1 will take up about 978Mb (125000) of space, and ix_2 will take up
about 7.5Mb (1000 pages).
Now lets look at the following queries
Select col1, col2 FROM ...
Select col2 FROM ... WHERE col1 = ...
If you do not have ix_2 then you will need to load more pages to get the
results. If you have ix_2 then this will be used as it uses less pages
> And another question. Someone asked me to create indexes like following:
> ix_1: col1, col2, col4
> ix_2: col1, col2, col3
> How about this case? Is it a good practice to create each index, or can I
> make just one index like following:
> ix_1:col1, col2, col3, col4
Similar to above.
> Any suggesion would be appreciated.
>