Writing SQL queries makes up a significant part of a database administrator’s (DBA) or developer’s job. The ability to write efficient queries can have a dramatic effect on database performance. This post will look at how using basic SQL Server indexes effectively contributes to improved query response time.

SQL Query Performance

It’s easy to write slow queries using SQL. You only tell the database what you want without any explicit instructions on how to satisfy the query. Even simple and similar queries can use different execution plans and take substantially different amounts of time to complete.

The way the data is stored greatly impacts the ability of a database engine to satisfy a query request. Finding records in a database can be accomplished by scanning, seeking, or a combination of both techniques. Using indexes provides a more efficient method of locating specific data elements stored in a database.

How tables are constructed influences the way the database searches for query results. Tables can be structured as a heap in which there is no defined order to its data rows. In some cases, using a heap can be a valid way to store data.

Tables can also have a defined order for data rows and store them based on key values. This structure is known as a Clustered Index (CI) and results in a clustered table. A table can only have one CI. Tables can also have nonclustered indexes which can facilitate finding data elements quickly.

In the vast majority of applications, a clustered index provides more efficient access to specific data elements and improves query performance over using a heap.

Clustered vs Nonclustered Indexes

Indexes are structures associated with a table or view designed to speed data retrieval. The index contains keys built from the table’s columns and is stored in a tree. Traversing the tree to locate data elements leads to better database response.

Two types of indexes are available in SQL Server.

• Clustered indexes sort and store a table’s data rows based on their key values. Only one clustered index can be created for each table as the rows can only be stored in one order. A clustered table is differentiated from a heap by the existence of a clustered index.
• Nonclustered indexes have a structure independent of data rows. They contain nonclustered index key values that have a pointer to the data row that stored the key value.
• Both types of indexes can be unique, meaning that there is only one combination that leads to a specific data element.

Writing Queries Using Indexes 

Query response time is directly related to the number of pages that need to be read into memory to satisfy a request. Constructing queries that allow the database engine to take advantage of indexes limits the number of pages that need to be examined to return the appropriate results.

Following are some tips for writing queries that make efficient use of indexes.

• Ensure that the columns that will be tested by WHERE, JOIN, and HAVING clauses are in an index.
• Don’t hide column values so they cannot be used by the index. This includes using functions on column values that make it impossible for them to be used effectively.
• Primary key constraints and unique key constraints should be used to create indexes for queries whenever possible.
• Strive to create predicates where SQL Server can isolate a value or range of index key values it needs to process. Most functions and operators inhibit the database engine from using indexes.
• Pay attention to the order of the key values in an index so they can be used more efficiently.
• Make sure your sort order or GROUP BY statements are supported by the index.

More tips are available in an IDERA webcast that takes a deeper look into how using indexes properly can improve query performance. The video provides many hands-on examples of using indexes in SQL Server and is an excellent source of information for DBAs and developers interested in improving database performance.

Aqua Data Studio

Aqua Data Studio is a versatile tool that can be used for a wide range of activities by a database team, including writing queries for SQL Server. In addition to its capacity for tuning queries in SQL Server, it supports performing administrative and development functions on over 40 other database platforms.

With Aqua Data Studio, users can access and develop queries for all supported database platforms from a unified and intuitive interface. The Visual Query Builder and query analysis tools facilitate the creation of high-performing SQL statements. Users can single-step through code to evaluate variables and identify tuning possibilities. Aqua Data Studio is an excellent choice for database teams tasked with supporting the performance of multi-platform environments.

Try Aqua Data Studio for free!