Hi, I have a question, would you please help me? I have designed a database for a web-cms, in the User table_ which includes : UserID, Username, Password, FirstName, LastName, …_ which is the best choice that I have to create the index on it, username or FirstName and LastName? Or both of them? By default the UserID is the clustered index of user table, so the next index must be non-clustered. But I am not sure about UserID to be the clustered index. As this is a web site and many users can register or remove their accounts everyday, is this a good choice to create the clustered index on UserID? i am using sql server 2008
+1
A:
You should define clustered indexes on fields that are often requested sequentially, or contain a large number of distinct values or are used in queries to joion tables. So that usually mean that the primary key is a good candidate.
Non-Clustered indexes are good for field that are used in the where clause of queries.
Deciding which fields you create indexes on is something that is very specific to your application. If you have very critical queries that use the first name and last name fields then I would say yes, otherwise it may not be worth the effort.
In terms of persons removing their accounts I am sure that you do not intend to delete the row from the table. Usually you just mark these as inactive because what happens to all the other related tables that may be affected by this user?
Vincent Ramdhanie
2010-10-05 18:23:20