thanks.. That helps... All of my ID's are Primary Keys in their respective tables. ( i left out attorney_ID (PK) in my example) I assumed I should create indexes for all of the foreign keys within a table as well as all of the fields I search on, but just wanted to double check.
All of my queries/joins will be based on Pkeys and Fkeys w/ some exceptions as first names, last names... As for implementing in SQL server, is it better practice to create each index w/ one column? - j -----Original Message----- From: Rob Baxter [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 2:10 PM To: CF-Talk Subject: RE: SQL Server 2K indexing Jim, Short answer no with an if. Long answer yes with a but..... just kidding ;) You can certainly create and index which contains multiple columns. Whether or not you want to do this is another topic completely. There are alot of questions which you need to answer to properly index a table. What kinds of queries will be run against the table? What fields will be most often searched or joined on? How many updates/inserts are anticipated? All of these things should be taken into account because they can have a large impact on index tuning. For example if you said that most of the searches would be done on names you might want to create a clustered index on (last_name, first_name). Also your table seems to be missing a primary key? Is it (office_id, position_id)? I would add a primary key (which will automatically create an index on those fields, you probably want it unclustered but again it's difficult to say without knowing your data patterns). Another option is to let the SQL Server (assuming you're using sql server, other db vendors likely have similar tools) Index Tuning Wizard do the work. This will allow SQL Server to watch your table in action so to speak and suggest index improvements. This is a good solution if you are unsure about the types of queries that will be run against your table. </rob> -----Original Message----- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 6:44 PM To: CF-Talk Subject: OT: SQL Server 2K indexing Hello all, I'm setting up indexes for a new table on the following fields... first_name last_name office_ID (FK) position_ID (fk) Should I create a new index for each field? Or can i create one index with all fields listed in the column area? TIA, - j ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
