an index seek is better...an index is stored in order. so if a query tries to look for say, PROD_ID=7, it knows to use the index to SEEK the PROD_ID of 7, so it's the fastest method of all.

an INDEX SEEK might be something like it's looking for PRODUCTNAME="BANANAS", and knows that following the index to find(seek) to get the leaf of information that is related to what it is looking for is the next best method.

if there was a different index on PRODUCTNAME, it might use that instead, but it will try to use the clustered index(in this example on my primary key PROD_ID) most of the time.

in this example, dropping the clustered index and recreating it to have two columns, PROD_ID and PRODUCTNAME might increase performance if there's alot of queries that include the product name