SQL Server Indexes

[dj4uk]

I'm going to try indexing tables in SQL Server for the first time. I need some advice as to the best method.

Firstly I have three tables containing location data (state, city, district):

tbl_lookup_state
ID int(4) Identity NOT NULL
state_desc varchar(50) NOT NULL

tbl_lookup_city
ID int(4) Identity NOT NULL
stateID int(4) NOT NULL
city_desc varchar(50) NOT NULL

tbl_lookup_district
ID int(4) Identity NOT NULL
cityID int(4) NOT NULL
district_desc varchar(50) NOT NULL

Queries on these tables is always by ID. They are then subsequently sorted by the text description field.

Is it best to use clustered indexes on the ID field?

I also have another table that holds far more information and can be queried against multiple fields - does this need an unclustered index?

Advice on this for a newbie would be appreciated.

DJ

[Mongo]

http://www.sql-server-performance.com/q&a71.asp

[techgnome]

Originally posted by dj4uk
Is it best to use clustered indexes on the ID field?

NOOOOO!
Clustering on an ID field is a bad idea. Having an index on it should suffice. The only time a clustered index makes sense is when the key being index could be duplicated.
For instance, let's say I have a table, tblStates that has StateID, StateCode, and StateName. StateID is an IDENTITY field, StateCode is the two letter abbreviation and StateName has the full name of the state. Now, I have a second table, tblZipCodes, with ZIPID, ZIPCode, StateID.
I would put indexes on StateID, and possibly StateCode. Indexes would alos be added on ZIPID, and a Clustered Index on tblZipCodes on the StateID field. This way, when I look up the Zip Codes for Illinios (say StateID = 39), they are all together, and not scattered all over.

Originally posted by dj4uk
I also have another table that holds far more information and can be queried against multiple fields - does this need an unclustered index?

Not sure what you mean by this, could you elaborate a bit?

TG

[brucevde]

Something else to think about, when determining indexes on a table, is the number of records per data page.

For instance take your tbl_lookup_City. The record length is 58 bytes, which means there will be about 138 records per data page. If your table only has 104 cities, SQL Server would probably do a table scan rather than use an index.

Clustering on an ID field is a bad idea.

But then again sometimes you may be using a clustered index with out even knowing it.

[From BOL]
Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.