[SQLDownUnder] General confusion about indexes

[sql]

Hi guys,

 

My brain is a mess… I think the best way to answer my questions will be to paint a scenario and talk it through (I like scenarios J).

 

Take an application with very large amount of users, and for each user you store their contacts.

 

Contacts (UserID INT, Email VARCHAR, MobileNumber VARCHAR, FirstName VARCHAR, LastName VARCHAR)

 

UserID is the ID of the user the contact belongs to.          

Lets also say that a user would not have more than 10,000 contacts but more likely 200.

(UserID, Email) is unique so it could be used as the primary key.

 

I have been to a couple of presentations recently that have said the clustering key should a single increasing column, for example a surrogate key IDENTITY column say ‘ContactID’.

 

My first question is, if the main query that is going to be used against this table is ‘GET a users contacts’ (I.e. WHERE UserID=N), Wouldn’t clustering on some surrogate key have bad performance and result in a scenario like the second diagram in the .jpg I have

Attached? So if the User has contacts spread through the file there will be high IO cost even with an index on UserID?

 

1) From a Course I have just taken, it would seem to me that clustering on (UserID, Email) would make more sense? Why would this be a bad strategy?

 

I have heard talk about bad insert performance for such a strategy. I have vague ideas of why, but could someone please just spell it out for me J. And is this performance hit possibly worth taking if Select Queries are more frequent than insert / update?

 

2. Also, silly SQLServer question: When you create a Primary Key in SQLServer by default it creates a Clustered index on that Primary Key.

 

What is the best way to create a table with a clustering index different from the primary key?

 

Currently I create the table from a script with a pk_constaint then go the database tree, right click delete the clustered index and then run the query that creates a new clustered index? Please point out if I am missing something basic, this seems real tedious as I’m often running a large script and I don’t want to do this over an over.

 

Thanks again for help in advanced,

 

Jonathon Kresner

 

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

Attachment: clustered vs unclustered indexes.JPG
Description: JPEG image