How to Query Data the Professional Way in SQL Server: A Performance-First Guide for Beginners

When you're just starting to write SQL Server queries, it's easy to throw a query together and hope for the best. However, this approach can lead to performance issues as your tables grow larger. This guide outlines the habits, patterns, and models that will set your queries apart from those of a senior engineer.

First and foremost, avoid using `SELECT *` in your queries. Instead, specify only the columns you need. This reduces I/O, avoids covering indexes, and prevents fragile code.

Understand indexes before blaming the query. Think of indexes like an index at the back of a textbook. They allow SQL Server to jump straight to the relevant rows, rather than reading every page (a full table scan). Use indexes on columns frequently used in WHERE, JOIN, and ORDER BY clauses but be cautious about over-indexing, as it can slow down INSERT, UPDATE, and DELETE operations.

Avoid using functions on indexed columns, as it turns the column into a non-sargable predicate. This forces SQL Server to compute the function for every row and prevents index seeks. Keep the indexed column bare on one side of the comparison.

Push filtering as close to the data source as possible. Instead of pulling large result sets into your application and filtering there, let SQL Server handle the filtering using its built-in efficiency.

When joining tables, always join on indexed columns. Use INNER JOIN when you only want matching rows. Avoid joining on computed expressions, as this can lead to non-sargable predicates.

For subqueries, use `EXISTS` instead of `IN` when dealing with large result sets. `EXISTS` short-circuits as soon as it finds the first matching row, whereas `IN` typically materializes the full subquery result first.

Paginate large result sets to avoid pulling unnecessary data into your application. Use `OFFSET` and `FETCH` in SQL Server 2012 and later to retrieve only the page of data you need.

Leverage SQL Server's diagnostic tools, such as Execution Plans, `SET STATISTICS IO ON`, and Query Store, to understand how your queries are executed and identify performance bottlenecks.

Finally, always use parameters instead of string concatenation to prevent SQL injection and enable plan caching.