In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!
The basis for a good query design is to have your database normalized. Normalization will ensure the data integrity and will minimize the data duplication so you know that a data will be only in a single place. With that you will be able to write fast and less complex queries.
Having a normalized table means that all your tables should have both a primary key (PK) and foreign keys (FK) constraints where it applies. You can only have a PK per table and in an SQL Server it will create all PKs as clustered indexes by default unless you specifically say not to. Even though this can be good for most of the cases, it may not be the best in other cases so review the Clustered Indexes to be sure that you have chosen the best one for each table.
The next step is to create the rest of necessary non-clustered indexes to boost the query (typically on the fields used in the WHERE clause of your queries).
NOTE: The work with the indexes does not ends here. Maintenance of the indexes is one of the most important maintenance tasks, so create a regular job to Reorganize and Rebuild indexes in your database. This will ensure a constant low level of index fragmentation and will help your queries run faster.
Until now, I just talked about the physical organization of the tables that are truly the basis for having a good query performance but this may not be enough if you write a poor query syntax.
So what do we know by now?
But there are more things to be taking into consideration when writing your queries, such as:
In conclusion, start with a well and normalized database design. This is the basis for building good queries in any RDBMS. Then choose the best candidates for your indexes (clustered and non-clustered) and schedule regular index defragmentation jobs. Lastly, avoid the use of poor performance SQL clauses. Respect these rules and you will have less performance issues in the future.