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

Clustered indexes on alphanumeric data are not overly recommended, especially with the great variance that is inherent to the uniqueidentifier type - with non-clustered data, you're better off with a non-clustered index. Regular advice would be to make your PK column an identity column, which is the ideal candidate for a clustered index.

You seem to be missing a unique index/constraint on (ClubFK, PersonFK) - if you choose to make it a unique constraint, the system will build an index for you anyway.

Thanks, that's a good point about the unique index on (ClubFK, PersonFK). I'll add that and see how it performs.

Uniqueidentifier is not an alphanumeric type (even though it looks that way because the literal values are written in quotes). It actually behaves like a 16-byte integer.

-- Brian

  Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

Hi  

In this case  let me suggest slightly different option 

Change the data type of the primary key  to BigInt   and  make it as an  identity column

Then considering  the retrieval frequency  of the  columns  either  you can make  clustered index  on primary key  or any one of the foreign keys ,if you are making clustered index on the primary key  keep another  two non clustered indexes for  the  foreign keys.

Thanks

Saji

Hello.

Are you using asp.net membership by any chance? 

Anyway, it is possible to have a guid as a primary key but this is not recommended due to the random nature of the guid produced (as others have stated). However, SQL Server 2005 has a new default datatype called NewSequentialID. This beauty will create a valid uniqueidentifer but each newly created value will ALWAYS be greater than the previous by exactly 1! Therefore, you have a column that is both using a unique value and is incrementing by 1. Indexes obviously find this much more appealing.

The peformance is not equal to a surrogate int identity column but it is very very close and much much faster than using the standard uniqueidentifer guid.

SQL Server will only let you use this as a default value on a field i.e. you cannot use it in an insert sproc directly, you have to set the default value of the field to newsequentialid() and let sql server do the rest. The only downside as I see it is the fact that the unique identifer becomes predictable as it only increments by one - so it could be a security risk if it was ever exposed to users. To be honest though, and I am only speaking from asp.net perspective, this should be very easy to ensure never happens. 

For more info on this:

http://www.fotia.co.uk/fotia/DY.19.NewSequentialId.aspx

 Altering asp.net membership:

http://www.ericswann.org/blog/archive/2008/03/15/asp.net-sql-server-2005-membership-provider-and-newsequentialid.aspx 

Hope this helps.