I've been asked to develop some queries on an existing database. The queries I've been asked to develop frequently reference a date column in their conditions however the date column isn't indexed. I would like to create an index on the date column to speed up my queries, but the database supports an existing application of which I have no sight of the code - Could my creating this extra index impact the existing app in any other way besides marginally slowing the updates/inserts.

Thanks in advance
Ben

creating an additional indexes may or may not impact one particular query but may impact others queries requesting data from that table.
once you created that index you should re-analyse the other queries using the indexes with that column and see if any of the other indexes are now redundant. Run the index tuning wizard check its recommendation. The index tuning wizard normally makes a good choice based on queries run against the table.

Also read the "Designing an Index" from Books Online

When Microsoft® SQL Server™ 2000 executes a query, the query optimizer evaluates the costs of the available methods for retrieving the data and uses the most efficient method. SQL Server can perform a table scan, or it can use an index if one exists. When performing a table scan, SQL Server starts at the beginning of the table, steps row-by-row through all the rows in the table, and extracts the rows that meet the criteria of the query. When SQL Server uses an index, it finds the storage location of the rows needed by the query and extracts only the needed rows.

When you are considering whether to create an index on a column, consider if and how an indexed column is to be used in queries. Indexes are useful when a query:



Jaime E. Maccou

Thanks for your input Jaime

The database supports a 3rd party application, I have a good understanding of the datacaptured, and I've taken a look over all the tables and have discovered the whole database hasn't been set up very well. Some PK/FK Relationships haven't been set up when they clearly should have been. Would I be getting into dangerous ground if I added these without seeing the applications code?

Before you make changes use profiler and create a trace file, then add your indexes, create another trace and compare the results. Creating the indexes is not going to break your application but can create locks if not constructed correctly.

Jaime E. Maccou

On another note. The application may be constructing the relationship code at the application level. This is why you should profile the app to find out what is doing and what tables its touching.

Jaime E. Maccou

Again thanks for your input.

One other question regarding indexes:

If an index is built on 2 columns, when applying a condition to one of the columns do I have to reference other columns aswell to make use of the index (even though I only want to restrict the values based on the first column)