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 ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
