Indexing on Views

1) We have SQL Server 2005 database on windows 2003 server.

2) What about columns in functions i.e. SUM(ABS(Col1)) OR SUM(Col2 * Col3). We create a view to select these and index on view on its computed AS column? Can we directly use vw1.AScol in query in place of these function expressions? Will the index be picked and how will cost improvement compare with that of index on normal columns.

3) Can statistics be used in such a situation?

its possible to put indexes on this columns...and it will be picked .... have a look at this link.:

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Indexing Columns

If you have a table with 3 columns,

ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently

Code Snippet

Select ID where Col1='SomeValue' and Col2='SomeOtherValue'

Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.

You could create an INDEX on Col1, Col2, and include [ID].

Something like this:

CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );

This is a 'covered' index. The entire query is satisfied by the index.

|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.

|||

For SQL 2000, if you use this query frequently, index all three columns.

|||

you can not define ID as non clustered since it is a PK.

ID can be clustered index and check the unique checkbox.

you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.

|||

You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.

|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.

If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.

So there is no reason to add ID to your non-clustered index since it will be there already.

I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.

Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.

Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.

|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left>&nbsp;</P>
<P align=left>&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;We could arrive at a decision of using the index on&nbsp;columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database&nbsp;trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left>&nbsp;</P>
<P align=left>Thanks.</P>

Indexes on Joined Columns

I have the following select statement used to create a view:
select
st.col1,
st.col2
from
FirstTable ft
inner join
SecondTable st
on
ft.coluid = st.colfid
and
ft.colkey = st.colkey
My statistics are up to date.
On FirstTable I have a clustered (Unique) primary key comprised of a
composite of coluid and colkey (in that order) having 64,000 rows. Column
coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio of
0.
On SecondTable I have a clustered (non unique) primary key comprised of a
composite of colfid and colkey (in that order) having 168,000 rows. Column
colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio of
0.
The two columns returned (st.col1, st.col2) from the select statement are not
indexed.
I was under the impression from various readings that having indexes on joins
are beneficial, but in testing, when I have the clustered indexes, versus
dropping the indexes and running the select statement, there is no difference
in the output of statistics io.
Also the execution plan states that each table receives a clustered index
scan.
The only benefit I see from having indexes on the joins is in CPU time, with
indexes 187 ms., without 375 ms.
Is that the benefit (reduced CPU, but no reduction of Logical Reads) spoken
of when I read that indexes on joined columns are beneficial? Also, wouldn't
my two columns returned be covered by the clustered index, if the index were
ever used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Um, basically, you want the entire contents of both tables. How about
filtering in some way? Also. if you are using SQL Server 2000, try adding
clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
SQL 2005, create the nonclustered on (st.col1, st.col2) with included
columns on (st.colfid, st.colkey).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:60c001bcd2645@.uwe...
I have the following select statement used to create a view:
select
st.col1,
st.col2
from
FirstTable ft
inner join
SecondTable st
on
ft.coluid = st.colfid
and
ft.colkey = st.colkey
My statistics are up to date.
On FirstTable I have a clustered (Unique) primary key comprised of a
composite of coluid and colkey (in that order) having 64,000 rows. Column
coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio of
0.
On SecondTable I have a clustered (non unique) primary key comprised of a
composite of colfid and colkey (in that order) having 168,000 rows. Column
colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio of
0.
The two columns returned (st.col1, st.col2) from the select statement are
not
indexed.
I was under the impression from various readings that having indexes on
joins
are beneficial, but in testing, when I have the clustered indexes, versus
dropping the indexes and running the select statement, there is no
difference
in the output of statistics io.
Also the execution plan states that each table receives a clustered index
scan.
The only benefit I see from having indexes on the joins is in CPU time, with
indexes 187 ms., without 375 ms.
Is that the benefit (reduced CPU, but no reduction of Logical Reads) spoken
of when I read that indexes on joined columns are beneficial? Also, wouldn't
my two columns returned be covered by the clustered index, if the index were
ever used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||A clustered index scan is a table scan, so that is no surprise.
You cannot use statistics IO to tell which is the better plan. As you have
noticed, the IO is the same, but the CPU is different. What kind of join
does the plan say is being performed? Without indexes, my guess is that
you're getting a hash join, which does a single pass through each table
while it builds and analyzes hash lists. So the IO is only one pass through
each table. But the extra work of building and examining the hash lists, but
the extra memory required to stored the hashed information, can make that
type of join very resource intensive.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:60c001bcd2645@.uwe...
>I have the following select statement used to create a view:
> select
> st.col1,
> st.col2
> from
> FirstTable ft
> inner join
> SecondTable st
> on
> ft.coluid = st.colfid
> and
> ft.colkey = st.colkey
> My statistics are up to date.
> On FirstTable I have a clustered (Unique) primary key comprised of a
> composite of coluid and colkey (in that order) having 64,000 rows. Column
> coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio
> of
> 0.
> On SecondTable I have a clustered (non unique) primary key comprised of a
> composite of colfid and colkey (in that order) having 168,000 rows. Column
> colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio
> of
> 0.
> The two columns returned (st.col1, st.col2) from the select statement are
> not
> indexed.
> I was under the impression from various readings that having indexes on
> joins
> are beneficial, but in testing, when I have the clustered indexes, versus
> dropping the indexes and running the select statement, there is no
> difference
> in the output of statistics io.
> Also the execution plan states that each table receives a clustered index
> scan.
> The only benefit I see from having indexes on the joins is in CPU time,
> with
> indexes 187 ms., without 375 ms.
> Is that the benefit (reduced CPU, but no reduction of Logical Reads)
> spoken
> of when I read that indexes on joined columns are beneficial? Also,
> wouldn't
> my two columns returned be covered by the clustered index, if the index
> were
> ever used?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||I follow your logic in SQL Server 2000, of adding a clustered index on (st.
colfid, st.colkey, st.col1, st.col2). But I am not sure about your statement
regarding SQL 2005. Are you saying create a nonclustered index on (st.col1,
st.col2, st.colfid, st.colkey)? If so, what is the difference between the
indexing from SQL 2000 to SQL 2005 that would make you want to cluster in SQL
2000 and adding an additional nonclustered index in SQL 2005?
Tom Moreau wrote:
>Also. if you are using SQL Server 2000, try adding
>clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
>SQL 2005, create the nonclustered on (st.col1, st.col2) with included
>columns on (st.colfid, st.colkey).
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||I am receiving a merge join, with clustered index scans when the clustered
indexes are in use. Additionally I can change the clustered index scan for
the node representing FirstTable when I apply the filter "where ft.coluid >
0".
When the clustered indexes are removed I have table scans and a hash join.
Kalen Delaney wrote:
>A clustered index scan is a table scan, so that is no surprise.
>You cannot use statistics IO to tell which is the better plan. As you have
>noticed, the IO is the same, but the CPU is different. What kind of join
>does the plan say is being performed? Without indexes, my guess is that
>you're getting a hash join, which does a single pass through each table
>while it builds and analyzes hash lists. So the IO is only one pass through
>each table. But the extra work of building and examining the hash lists, but
>the extra memory required to stored the hashed information, can make that
>type of join very resource intensive.
>>I have the following select statement used to create a view:
>[quoted text clipped - 49 lines]
>> were
>> ever used?
--
Message posted via http://www.sqlmonster.com|||Sorry, I wasn't clear. I can change the clustered index scan for the node
representing FirstTable when I apply the filter "where ft.coluid 0" to a
clustered index seek.
cbrichards wrote:
>I am receiving a merge join, with clustered index scans when the clustered
>indexes are in use. Additionally I can change the clustered index scan for
>the node representing FirstTable when I apply the filter "where ft.coluid >
>0".
>When the clustered indexes are removed I have table scans and a hash join.
>>A clustered index scan is a table scan, so that is no surprise.
>>You cannot use statistics IO to tell which is the better plan. As you have
>[quoted text clipped - 11 lines]
>> were
>> ever used?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||In SQL 2005, you have the option of "included columns". This makes covering
indexes more efficient, since the included columns exist only in the leaf
pages of the index. Basically, make the criteria from your JOIN and WHERE
clauses to be your index keys. Then make the included columns to be any
other columns from your SELECT list that don't already exist in your index
keys.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:60c845387d9c4@.uwe...
I follow your logic in SQL Server 2000, of adding a clustered index on (st.
colfid, st.colkey, st.col1, st.col2). But I am not sure about your
statement
regarding SQL 2005. Are you saying create a nonclustered index on (st.col1,
st.col2, st.colfid, st.colkey)? If so, what is the difference between the
indexing from SQL 2000 to SQL 2005 that would make you want to cluster in
SQL
2000 and adding an additional nonclustered index in SQL 2005?
Tom Moreau wrote:
>Also. if you are using SQL Server 2000, try adding
>clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
>SQL 2005, create the nonclustered on (st.col1, st.col2) with included
>columns on (st.colfid, st.colkey).
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

Indexes on Joined Columns

I have the following select statement used to create a view:
select
st.col1,
st.col2
from
FirstTable ft
inner join
SecondTable st
on
ft.coluid = st.colfid
and
ft.colkey = st.colkey
My statistics are up to date.
On FirstTable I have a clustered (Unique) primary key comprised of a
composite of coluid and colkey (in that order) having 64,000 rows. Column
coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio of
0.
On SecondTable I have a clustered (non unique) primary key comprised of a
composite of colfid and colkey (in that order) having 168,000 rows. Column
colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio of
0.
The two columns returned (st.col1, st.col2) from the select statement are no
t
indexed.
I was under the impression from various readings that having indexes on join
s
are beneficial, but in testing, when I have the clustered indexes, versus
dropping the indexes and running the select statement, there is no differenc
e
in the output of statistics io.
Also the execution plan states that each table receives a clustered index
scan.
The only benefit I see from having indexes on the joins is in CPU time, with
indexes 187 ms., without 375 ms.
Is that the benefit (reduced CPU, but no reduction of Logical Reads) spoken
of when I read that indexes on joined columns are beneficial? Also, wouldn't
my two columns returned be covered by the clustered index, if the index were
ever used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Um, basically, you want the entire contents of both tables. How about
filtering in some way? Also. if you are using SQL Server 2000, try adding
clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
SQL 2005, create the nonclustered on (st.col1, st.col2) with included
columns on (st.colfid, st.colkey).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:60c001bcd2645@.uwe...
I have the following select statement used to create a view:
select
st.col1,
st.col2
from
FirstTable ft
inner join
SecondTable st
on
ft.coluid = st.colfid
and
ft.colkey = st.colkey
My statistics are up to date.
On FirstTable I have a clustered (Unique) primary key comprised of a
composite of coluid and colkey (in that order) having 64,000 rows. Column
coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio of
0.
On SecondTable I have a clustered (non unique) primary key comprised of a
composite of colfid and colkey (in that order) having 168,000 rows. Column
colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio of
0.
The two columns returned (st.col1, st.col2) from the select statement are
not
indexed.
I was under the impression from various readings that having indexes on
joins
are beneficial, but in testing, when I have the clustered indexes, versus
dropping the indexes and running the select statement, there is no
difference
in the output of statistics io.
Also the execution plan states that each table receives a clustered index
scan.
The only benefit I see from having indexes on the joins is in CPU time, with
indexes 187 ms., without 375 ms.
Is that the benefit (reduced CPU, but no reduction of Logical Reads) spoken
of when I read that indexes on joined columns are beneficial? Also, wouldn't
my two columns returned be covered by the clustered index, if the index were
ever used?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||A clustered index scan is a table scan, so that is no surprise.
You cannot use statistics IO to tell which is the better plan. As you have
noticed, the IO is the same, but the CPU is different. What kind of join
does the plan say is being performed? Without indexes, my guess is that
you're getting a hash join, which does a single pass through each table
while it builds and analyzes hash lists. So the IO is only one pass through
each table. But the extra work of building and examining the hash lists, but
the extra memory required to stored the hashed information, can make that
type of join very resource intensive.
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:60c001bcd2645@.uwe...
>I have the following select statement used to create a view:
> select
> st.col1,
> st.col2
> from
> FirstTable ft
> inner join
> SecondTable st
> on
> ft.coluid = st.colfid
> and
> ft.colkey = st.colkey
> My statistics are up to date.
> On FirstTable I have a clustered (Unique) primary key comprised of a
> composite of coluid and colkey (in that order) having 64,000 rows. Column
> coluid has a selectivity ratio of 0.94 and colkey has a selectivity ratio
> of
> 0.
> On SecondTable I have a clustered (non unique) primary key comprised of a
> composite of colfid and colkey (in that order) having 168,000 rows. Column
> colfid has a selectivity ratio of 0.35 and colkey has a selectivity ratio
> of
> 0.
> The two columns returned (st.col1, st.col2) from the select statement are
> not
> indexed.
> I was under the impression from various readings that having indexes on
> joins
> are beneficial, but in testing, when I have the clustered indexes, versus
> dropping the indexes and running the select statement, there is no
> difference
> in the output of statistics io.
> Also the execution plan states that each table receives a clustered index
> scan.
> The only benefit I see from having indexes on the joins is in CPU time,
> with
> indexes 187 ms., without 375 ms.
> Is that the benefit (reduced CPU, but no reduction of Logical Reads)
> spoken
> of when I read that indexes on joined columns are beneficial? Also,
> wouldn't
> my two columns returned be covered by the clustered index, if the index
> were
> ever used?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||I follow your logic in SQL Server 2000, of adding a clustered index on (st.
colfid, st.colkey, st.col1, st.col2). But I am not sure about your statemen
t
regarding SQL 2005. Are you saying create a nonclustered index on (st.col1,
st.col2, st.colfid, st.colkey)? If so, what is the difference between the
indexing from SQL 2000 to SQL 2005 that would make you want to cluster in SQ
L
2000 and adding an additional nonclustered index in SQL 2005?
Tom Moreau wrote:
>Also. if you are using SQL Server 2000, try adding
>clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
>SQL 2005, create the nonclustered on (st.col1, st.col2) with included
>columns on (st.colfid, st.colkey).
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||I am receiving a merge join, with clustered index scans when the clustered
indexes are in use. Additionally I can change the clustered index scan for
the node representing FirstTable when I apply the filter "where ft.coluid >
0".
When the clustered indexes are removed I have table scans and a hash join.
Kalen Delaney wrote:[vbcol=seagreen]
>A clustered index scan is a table scan, so that is no surprise.
>You cannot use statistics IO to tell which is the better plan. As you have
>noticed, the IO is the same, but the CPU is different. What kind of join
>does the plan say is being performed? Without indexes, my guess is that
>you're getting a hash join, which does a single pass through each table
>while it builds and analyzes hash lists. So the IO is only one pass through
>each table. But the extra work of building and examining the hash lists, bu
t
>the extra memory required to stored the hashed information, can make that
>type of join very resource intensive.
>
>[quoted text clipped - 49 lines]
Message posted via http://www.droptable.com|||Sorry, I wasn't clear. I can change the clustered index scan for the node
representing FirstTable when I apply the filter "where ft.coluid 0" to a
clustered index seek.
cbrichards wrote:[vbcol=seagreen]
>I am receiving a merge join, with clustered index scans when the clustered
>indexes are in use. Additionally I can change the clustered index scan for
>the node representing FirstTable when I apply the filter "where ft.coluid >
>0".
>When the clustered indexes are removed I have table scans and a hash join.
>
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||In SQL 2005, you have the option of "included columns". This makes covering
indexes more efficient, since the included columns exist only in the leaf
pages of the index. Basically, make the criteria from your JOIN and WHERE
clauses to be your index keys. Then make the included columns to be any
other columns from your SELECT list that don't already exist in your index
keys.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:60c845387d9c4@.uwe...
I follow your logic in SQL Server 2000, of adding a clustered index on (st.
colfid, st.colkey, st.col1, st.col2). But I am not sure about your
statement
regarding SQL 2005. Are you saying create a nonclustered index on (st.col1,
st.col2, st.colfid, st.colkey)? If so, what is the difference between the
indexing from SQL 2000 to SQL 2005 that would make you want to cluster in
SQL
2000 and adding an additional nonclustered index in SQL 2005?
Tom Moreau wrote:
>Also. if you are using SQL Server 2000, try adding
>clustered index on (st.colfid, st.colkey, st.col1, st.col2). If you have
>SQL 2005, create the nonclustered on (st.col1, st.col2) with included
>columns on (st.colfid, st.colkey).
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

Indexes design

If I have a table with Col1 + Col2 as PK. But most of the queries use Col1, Col2, Col3 and Col4 in the queries, should I create another unique index on this combination? If not, my queries do an index scan. Just wondering is there a general rule for this kind of situation as I have a lot of such cases in my database.depends. most of the time multi column indexes are not a good idea. if i remember correctly for multi column indexes to work at all, the query has to search the columns in the table in the order that you defined the columns in the index.

index scans are ok, it is table scans you have to worry about.

are you experiencing a performance issue?|||Thrasymachus- I agree, we have to include all the columns in the index in the where clause to avoid a index scan. But the table is huge and yes, I am experiencing performance issues. I know I could make them run better with a seek.|||what's huge? tens of millions of records?

what's the ddl like? lots of fields? big fields?

And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition.

What's the query look like?|||3 million records. Table has 9 columns with 2 varchar(512) and a UniqueIdentifier.

Not sure what you mean by "And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition."|||3 million is not that big.

If your index is created like so

CREATE INDEX MyIndex On Table1 (col1,col2,col3)

Then your query should go like

SELECT *
FROM Table1
Where col1 = @.param1
AND col2 = @.param2
AND col3 = @.param3

same order, see.

ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values.|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.|||I develop in sql 2k now, but I just looked at my old design book for sql 7 I have laying around to make sure and this part I did confirm. things might have changed. The other thing I remeber from an ex-colleague of mine who was the sql guru I ever knew.

"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.

The notion that the WHERE clause search arguments have to be in the "same order " as the columns defined in the key of the index is rubbish.

Also, the term "index scan" to me means examining EVERY row of an index because to find a match on a search criteria. In your example, "where clause is on col1 and col3 while your index is on (col1, col2, col3)", given that you don't have any datatype mismatch issues, and given that the index is chosen as the access path, and given that the search argument operator is "=", and given that the index is large enough, then you will KEY POSITION on col1, and scan the rest of the index for col3 matches. So, every row of the index containing col1 value is "scanned", but no more than that.|||KSherlock- I agree with you. I have the same understanding as yours. We got side tracked into this discussion about column order. My original question is a little different. Still looking for some comments/suggestions on it.|||As for this part:

"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."

I will refer you to Database Design on SQL Server 7 by certification insider press page 173.

As for the other thing. This kind of backs me up (taken from http://www.sql-server-performance.com/composite_indexes.asp). I have forgot a little of it:

"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"

So heres mud in your eye Sherlock.|||Check me if I have this straight:

Col1 and Col2 make up the PK

And most queries use all 4 Col1 Col2 Col3 and Col4? Or is that "or"? After all, if you specify col1 and col2, specifying col3 and col4 would be a bit redundant, eh?

If you specify values for Col1 and Col2, you should be seeking on the PK. Are you using functions on the columns? Like

where upper(col1) = upper(some search argument)

The above will cause a table scan every time. Since the PK is usually clustered, you may be seeing this just represented as a clustered index scan. It is really the same thing.|||And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition

...

"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"

So heres mud in your eye Sherlock.

Uhhh, yea. Whatever you say...|||...most of the time multi column indexes are not a good idea...Huh? Please present your theories as such, not as a "proven fact", man. You want the guy to loose his job just because you couldn't resist making a statement?

...ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values...Good point, but ... how should I say it? It's orthodox concept for a table that is NEVER a part of an action query. Introduction of indexes based on "best practices for SELECT queries" ONLY, - is a recepie for your action queries as a potential bottleneck.