Dumb index question

If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
indexes?
Even if it only creates one compound index, if I run the query: select *
from tab1 where col1 = x
Will it still use the compound index, if nothing better is available,
because the column heads the index.
I am expecting the answer to be: 2 indexes and Duh/yes!
On Wed, 15 Aug 2007 16:42:16 -0700, "Jay" <nospam@.nospam.org> wrote:

>If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
>another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
>indexes?
>Even if it only creates one compound index, if I run the query: select *
>from tab1 where col1 = x
>Will it still use the compound index, if nothing better is available,
>because the column heads the index.
>
>I am expecting the answer to be: 2 indexes and Duh/yes!
A compound index is one index, so if the only index you create is a
two column primary key there is one index. A foreign key is a good
candidate for an index, but no index ix created just because of a FK
definition. Only a PK definition creates an index.
A multi-column index can be used if at least the left-most column is
provided to search on. So in your example of a compound PK on
tab1(col1, col2), the query SELECT * FROM tab1 WHERE col1 = 'x' can
use the index.
Roy Harvey
Beacon Falls, CT
|||In addition to Roy's answer , try to avoid using SELECT * in production
environment.
Always specify columns that you need to return. For example if you run
SELECT col1,col2 FROM tbl WHERE col2='blblbl' then SQL Server will create
most fate execution plan as your index covers columns in SELECT statement.
But if you run SELECT col10 FROM tbl WHERE col2='blblbl' will use bookmark
to clustered index key (contains data) to return the data from col10 which
is not part of index
Just my two cenys
"Jay" <nospam@.nospam.org> wrote in message
news:e9LqqX53HHA.600@.TK2MSFTNGP05.phx.gbl...
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
>
> I am expecting the answer to be: 2 indexes and Duh/yes!
>
|||On Aug 15, 6:42 pm, "Jay" <nos...@.nospam.org> wrote:
> If I create a compound PK on tab1(col1, col2) where col1 is also a FK to
> another table, is SQL Server 2000 (or 2005) creating 1, or 2 underlying
> indexes?
> Even if it only creates one compound index, if I run the query: select *
> from tab1 where col1 = x
> Will it still use the compound index, if nothing better is available,
> because the column heads the index.
> I am expecting the answer to be: 2 indexes and Duh/yes!
It is very easy to see for yourself. Create a FK constraint and see if
it created an index. You may use GUI or select from system views such
as sysindexes and sysindexkeys.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/