In T-SQL Tip 9, we discussed how execution plans could have a SCAN operator that went from the beginning to the end of a range of data. SCANs were iffy. Now that we’ve built a non-clustered index (T-SQL Tip 10) our execution plans will have the chance to do a lot more SEEKs. A seek is when the SQL Server optimizer goes straight to the page that has your data in it....

Last time on T-SQL Tip 9, we covered what execution plan scans are and why to avoid them. Well, this week we are going to avoid them by using non-clustered indexes, see T-SQL Tip 5. To build a useful non-clustered index, we are going to look at our queries and identify three parts: Filters, Sorts, and Outputs. Once identified, take every column in the WHERE clause and put them first. Next, take every column in an ORDER BY or JOIN and put them second....

In the last post, there was a quick how to enable plans, and why you might (see T-SQL Tip 8). One of the most common things you can see in an execution plan is a SCAN. What this means is SQL Server went to an object and had a start point and an end point to look through. The most common start and end points are the first and last pages in the table....

When you hit execute, the first thing SQL Server does is make a plan of action for how to do what you said. In Management Studio, you can view estimated plans by pressing Ctrl + L. If you want to see the actual statistics and plan after execution, you can show actual plans with Ctrl + M. Microsoft has decided the best™ way of making a plan is to run a few estimates for what would work, and then pick the cheapest....

Often two tables in a relational database are related. I’ll give you a second to let that shock wear off…. Because the tables are related, it can be helpful to have rules in place to force the data to maintain this relationship. Often these are done with objects called keys. A primary key defines and enforces a unique set of data in a table. A foreign key on a second table forces the second table to only allow records that match data in that primary key....