Performance in T-SQL

I'm taking a look at performance issues in T-SQL this morning. How do indexes and functions affect performance? I'm working with the "AdventureWorks" test database from Microsoft. It can be downloaded here: link

Since there isn't currently an index on the Sales.SalesOrderHeader table, I'm going to create one with the following script:

Now that a nonclustered index exists for the SalesOrderHeader, I'm going to run some test scripts. You can see the queries and the results below:

These queries return all the orders placed in 2001. Query 1 accomplishes this without the use of a function, while query 2 uses the YEAR function. So what's the lesson here? Take a look at the execution plans. Query 1 has a cost of only 7 percent. The database engine can use the index I set up with this query because it is comparing actual indexed values. Query 2 uses a function that requires the engine to scan the entire index in order to see whether the results of the YEAR function applied to each value meets the criteria.

I dropped the index and ran the two queries again. Now each query takes the same amount of time. How you construct queries and how you implement indexes makes a big difference in performance. It's not enough to just retrieve the desired results.