Index performance question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Index performance question

Author

Topic

brianberns
Starting Member

10 Posts

Posted - 2008-09-22 : 16:45:15

I have an association table that looks like this:


    CREATE TABLE ClubMember (
        PK uniqueidentifier NOT NULL,
        ClubFK uniqueidentifier NOT NULL,
        PersonFK uniqueidentifier NOT NULL,
        CONSTRAINT [PK_ClubMember] PRIMARY KEY NONCLUSTERED (PK)
    )

The table contains about ten million rows. I want to query it like this:


    select PersonFK from ClubMember where ClubFK = '7130DF7E-4045-437C-A383-A936C0671746'

It seems to me that the best way to maximize performance of this query is to create a clustered index that contains both ClubFK and PersonFK:


    create unique clustered index IX_ClubMember on ClubMember (
        ClubFK,
        PersonFK
    )

However, when I create the index and then run the query through the tuning advisor, it recommends that I instead create a non-clustered index on ClubFK that also "includes" PersonFK:


    create nonclustered index IX_ClubMember on ClubMember (
        ClubFK
    ) include (PersonFK)

Can anyone explain why the second index is faster than the first? It seems to me that nothing could be faster than a physically clustered index with columns that cover the entire query. Thanks.

-- Brian

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-22 : 18:45:42

Why you need Clustered PRIMARY KEY for ClubFk? ClubFk is already uniqueidentifier.Also as PK is uniqueidentifier,you don't need to create Primary key for it.It looks confusing with your data type. CLUSTERED INDEX[PK] with NON-CLUSTERED index on [ClubFK] should make it good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources