I'm looking for some help on how i should index this table.
current table has about 500k records in it.
the fields in the table are:
member_num (varchar(12), not null)
first_name (varchar(20), null)
last_name (varchar(20), null)
ssn (varchar(50), null)
address1 (nvarchar(200), null)
address2 (nvarchar(200), null)
city (nvarchar(200), null)
state (nvarchar(200), null)
zip (nvarchar(100), null)
phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
I generally take a look at how the actual usage pans out. Do you expect the same amount of queries on address2 as last_name? Then there is the question of how often is last_name queried with or without first_name? Once you know how the searches usually shape up, then you can index smarter.