Hi Jeff, thanks for the thorough explanation.

I usually use execution plans to check for table scans, if I find a table scan relating to a table, the table normally does not have an index, after adding index to table, I use execution plan again, this time no table scan, query performance is much improved. I use execution plan for single stored procedure.

CREATE TABLE Phone (

Phone_ID int NOT NULL IDENTITY(1,1),

Contract_No int NOT NULL,

Make varchar(20) NULL,

Model_No int NULL,

Year smalldatetime NULL,

Warranty varchar(20) NULL,

User_ID varchar(20) NULL,

City varchar(20) NULL,

Status varchar(10) NULL

)

SELECT * FROM Phone

WHERE Contract_No = @Contract_No

AND Status = 'Active'

Question1:

when creating an index on phone table, which columns should be used by clustered index,

and which columns should be used by non-clustered index.

Question2:

Can you run all the application stored procedures against execution plan in one go, or must I test every stored procedure against execution plan separately.