Indexing a table

Using SQL Server 2005 I would like to know what people think is the best way
to index the following table and why. Please note, the database is not
normalized and I realize this and normalizing the database is not an option
at this time.
Table: Call
Fields: SalesRepCallID int identity
CallID int
SpeakerID int
CallDate datetime
Territory varchar(255)
SalesRepName varchar(255)
CallType varchar(50)
Address varchar(50)
City varchar(50)
State varchar(50)
Zip varchar(10)
CallNotes varchar(max)
The table has over 3 million records in it and the only unique field is the
SalesRepCallID field. Once again, I know the database is not normalized but
I don't have the option of normalizing at this time.
The users can search on all of the fields except the SalesRepCallID,
Address, and CallNotes per their demands. The SalesRepCallID is unique
non-clustered index since it is the primary key.
However, I am not sure which field I should make the clustered index. Most
of the time the users will be searching by Speaker so do I make the SpeakerID
the cluster index and the other fields non-clustered or what.
Thanks!
The best way to determine the best index for the query type would be to use
the Database Engine Tuning Advisor or using the DMVs (sys.dm_exec_query_plan)
which gives you the actual query plan in XML format and you can look for the
missing indexes portion.
Now, if I cannot use any of the previous recommendations and I have to
answer this the best I can, I probably would be inclined to try creating a
noncluster index on the SpeakerID and INCLUDE some of the other columns that
are used the most.
Check also the following:
Index with Included Columns
http://msdn2.microsoft.com/en-us/library/ms190806.aspx
I hope it helps
"Anonymous" wrote:

> Using SQL Server 2005 I would like to know what people think is the best way
> to index the following table and why. Please note, the database is not
> normalized and I realize this and normalizing the database is not an option
> at this time.
> Table: Call
> Fields: SalesRepCallID int identity
> CallID int
> SpeakerID int
> CallDate datetime
> Territory varchar(255)
> SalesRepName varchar(255)
> CallType varchar(50)
> Address varchar(50)
> City varchar(50)
> State varchar(50)
> Zip varchar(10)
> CallNotes varchar(max)
> The table has over 3 million records in it and the only unique field is the
> SalesRepCallID field. Once again, I know the database is not normalized but
> I don't have the option of normalizing at this time.
> The users can search on all of the fields except the SalesRepCallID,
> Address, and CallNotes per their demands. The SalesRepCallID is unique
> non-clustered index since it is the primary key.
> However, I am not sure which field I should make the clustered index. Most
> of the time the users will be searching by Speaker so do I make the SpeakerID
> the cluster index and the other fields non-clustered or what.
> Thanks!