Hi,
I'm struggling to get my head round indexes even after having a few explanations, im getting closer but its still well confusing....i want to list a few scenarios within my database, then hopefully if suggestions can tell me the best type of index etc i should be able to put this into practice elsewhere...
FIRST SCENARIO
Firstly, i have a users table, 20,000 records, unique primary key called Users.UserID. I have an artistprofiles table, maybe 15,000 records, linked by a foreign key called ArtistProfiles.UserID, there is also a text field in ArtistProfiles called Artistname. ArtistProfiles.UserID is frequently joined, or referenced in where clauses with the Users table. Frequent text searches are executed on the ArtistProfiles.Artistname field. Therefore, after reading as much as my head can handle on indexes, i was going to do the following:
ArtistProfiles.ArtistID - Make this a unique index (What are the options, Constraint or Index, and also Ignore Duplicate Key) (Dont think this needs to be a primary key does it???)
ArtistProfiles.Artistname - Non-unique Non-clustered index.
SECOND SCENARIO
I have a table called Charts, 1 record created every week, currently 200 records...Unique and Primary key called ChartID (is a primary key required, i have read that if there are not masses of records a PK could slow it down, yet it needs to be unique so am i better creating it as a unique, clustered index??) Then i have a table called ChartEntries which has a foreign key called ChartID - this cannot be unique in this table as there could be 40 records all relating to the same chartID - therefore MSSQL wont even let me create the index, do i therefore need to add a unique key on the table as perhaps a PK....this table could potentially have thousands of records.
The bit im struggling on, no matter how much i read up on it, is how to decide which fields should be either indexed, whether they should be clustered or non-clustered and whether the field should be a primary key....Does anyone have a decent set of guidelines, that you can almost put any scenario/design into, which will aid you in deciding what type of index, if any, to put on a column???
Thanks guys
Al
