If you've just started writing SQL Server queries, there's a good chance you probably wrote a query, saw it return the right rows, and moved on. That's normal and it's also exactly why so many beginner-written queries fall over the moment a table grows from 500 rows to 5 million.

This article walks through the habits, patterns, and models that separate a "SELECT * and hope" query from one a senior engineer would actually approve in a code review.

1. Stop Using SELECT *

It's the first habit almost everyone picks up, and the first one you should drop.

-- Avoid
SELECT * FROM Orders WHERE CustomerId = 100;

-- Prefer
SELECT OrderId, OrderDate, TotalAmountFROM OrdersWHERE CustomerId = 100;

Why it matters:

• SQL Server has to fetch every column, including large or unused ones (think NVARCHAR(MAX) or VARBINARY blobs), inflating I/O.

• It breaks covering indexes (more on that below): the optimizer can't satisfy the query from the index alone if you ask for columns not included in it.

• It makes your code fragile: if someone adds a column later, your application code silently receives it, which can break serialization or introduce unexpected data exposure.

  Only select the columns you actually need. Every unnecessary column is wasted disk I/O and network transfer.

2. Understand Indexes Before You Blame the Query

A huge number of "slow query" problems aren't bad SQL; they're missing or misused indexes.

Think of an index like the index at the back of a textbook. Without it, SQL Server has to read every page (a table scan) to find what you asked for. With the right index, it jumps straight to the relevant rows (a seek).

-- Without an index on CustomerId, this triggers a full table scan

SELECT OrderId, OrderDate
FROM Orders
WHERE CustomerId = 100;

-- Add an index to make it a seek

CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

A few beginner-friendly rules of thumb:

• Index columns that show up frequently in WHERE, JOIN, and ORDER BY clauses.
• Don't over-index. Every index speeds up reads but slows down INSERT/UPDATE/DELETE, since SQL Server has to maintain it too.
• Use the Execution Plan (Ctrl+M in SSMS, or SET SHOWPLAN_ALL ON) to see whether SQL Server is doing a Seek or a Scan. This one habit alone will teach you more about performance than any article.

3. Avoid Functions on Indexed Columns

This is a subtle one that trips up a lot of beginners:

-- Bad: index on OrderDate can't be used

SELECT OrderId
FROM Orders
WHERE YEAR(OrderDate) = 2025;

-- Good: index-friendly range

SELECT OrderId
FROM Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';

Wrapping a column in a function (YEAR(), CONVERT(), ISNULL(), etc.) is called a non-sargable predicate; it forces SQL Server to compute the function for every row before it can compare, which disables index seeks entirely. Keep the indexed column "bare" on one side of the comparison whenever possible.

4. Filter Early, Filter Precisely

Push filtering as close to the data source as possible instead of pulling large result sets into the application and filtering in code.

-- Avoid: pulling everything, filtering in app code

SELECT * FROM Orders;

-- Prefer: let SQL Server do the filtering

SELECT OrderId, TotalAmount
FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE());

SQL Server's engine is built specifically to filter data efficiently using indexes and statistics. Your application layer is not. Let the database do what it's good at.

5. Be Careful With JOINs

Joins are where beginner queries often go from "slow" to "grinds the server to a halt."

SELECT o.OrderId, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderDate >= '2025-01-01';

A few habits worth building:

• Always join on indexed columns (usually primary/foreign keys).

• Use INNER JOIN when you only want matching rows; it's often faster than LEFT JOIN because the optimizer has fewer possibilities to consider.

• Avoid joining on computed expressions (e.g., ON LOWER(a.Name) = LOWER(b.Name)), the same non-sargable problem as above.

• Watch out for implicit many-to-many joins that silently multiply your row count (a classic cause of "why do I have duplicate rows?").

  6. Use EXISTS Instead of IN for Subqueries

-- Slower on large subquery results

SELECT CustomerName
FROM Customers
WHERE CustomerId IN (SELECT CustomerId FROM Orders WHERE TotalAmount > 1000);

-- Usually faster: stops at the first match

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o
WHERE o.CustomerId = c.CustomerId AND o.TotalAmount > 1000
);

EXISTS short-circuits as soon as it finds one matching row, while IN typically has to materialize the full subquery result first. For large subqueries, this difference is significant.

7. Paginate Large Result Sets

Never pull 100,000 rows into your app just to display 20 of them.

SELECT OrderId, OrderDate, TotalAmount
FROM Orders
ORDER BY OrderDate DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

OFFSET/FETCH (SQL Server 2012+) lets the database return just the page you need instead of shipping the entire result set over the network.

8. Let SQL Server Help You Diagnose Problems

You don't need to guess. SQL Server gives you the tools:

• Execution Plans: show you exactly how SQL Server intends to run your query (scans vs. seeks, join types, estimated vs. actual row counts).
• SET STATISTICS IO ON: shows logical reads per table, which tells you how much work each part of your query is doing.
• Query Store (SQL Server 2016+): tracks query performance history over time, so you can catch regressions after a deployment.

SET STATISTICS IO ON;
SELECT OrderId, TotalAmount FROM Orders WHERE CustomerId = 42;

Get comfortable reading this output early. It turns performance tuning from guesswork into a science.

9. Use Parameters, Not String Concatenation

This one is as much about security as performance:

-- Vulnerable to SQL injection, and prevents plan caching

string query = "SELECT * FROM Orders WHERE CustomerId = " + customerId;

-- Parameterized: safe, and reuses cached execution plans

SELECT OrderId, TotalAmount FROM Orders WHERE CustomerId = @CustomerId;

Parameterized queries let SQL Server cache and reuse the execution plan across calls instead of recompiling from scratch every time; this is both a performance win and a basic defense against SQL injection.

10. Keep Statistics Up to Date

SQL Server's query optimizer makes decisions based on statistics: estimates of how many rows match a given filter. If those statistics are stale (say, after a huge data load), the optimizer can pick a badly suited plan.

UPDATE STATISTICS Orders;
On most production systems this is automated, but it's worth knowing it exists, especially after bulk inserts or major data changes.

Putting It Together

None of these ideas require advanced SQL Server internals knowledge; they're habits. A query written by someone thinking about performance looks like this:

SELECT o.OrderId, o.OrderDate, o.TotalAmount, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.OrderDate >= @StartDateAND o.OrderDate < @EndDateORDER BY o.OrderDate DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

Notice what's happening: specific columns, sargable date range, indexed join, parameters, and pagination, all in one readable query.

Final Thoughts

Performance tuning in SQL Server isn't a separate skill you learn after you know SQL; it's part of knowing SQL. The good news is that most of the gains come from a handful of habits: select only what you need, index what you filter on, keep predicates sargable, and always check the execution plan when something feels slow.

Master these ten habits, and you'll already be writing queries more carefully than a large share of production code out there.