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.-- AvoidSELECT * FROM Orders WHERE CustomerId = 100;-- PreferSELECT 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 QueryA 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 scanSELECT OrderId, OrderDateFROM OrdersWHERE CustomerId = 100;-- Add an index to make it a seekCREATE 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 ColumnsThis is a subtle one that trips up a lot of beginners:-- Bad: index on OrderDate can't be usedSELECT OrderIdFROM OrdersWHERE YEAR(OrderDate) = 2025;-- Good: index-friendly rangeSELECT OrderIdFROM OrdersWHERE 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 PreciselyPush 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 codeSELECT * FROM Orders;-- Prefer: let SQL Server do the filteringSELECT OrderId, TotalAmountFROM OrdersWHERE 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 JOINsJoins are where beginner queries often go from "slow" to "grinds the server to a halt."SELECT o.OrderId, c.CustomerNameFROM Orders oINNER JOIN Customers c ON o.CustomerId = c.CustomerIdWHERE 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 resultsSELECT CustomerNameFROM CustomersWHERE CustomerId IN (SELECT CustomerId FROM Orders WHERE TotalAmount > 1000);-- Usually faster: stops at the first matchSELECT CustomerNameFROM Customers cWHERE EXISTS (SELECT 1 FROM Orders oWHERE 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 SetsNever pull 100,000 rows into your app just to display 20 of them.SELECT OrderId, OrderDate, TotalAmountFROM OrdersORDER BY OrderDate DESCOFFSET 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 ProblemsYou 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 ConcatenationThis one is as much about security as performance:-- Vulnerable to SQL injection, and prevents plan cachingstring query = "SELECT * FROM Orders WHERE CustomerId = " + customerId;-- Parameterized: safe, and reuses cached execution plansSELECT 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 DateSQL 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 TogetherNone 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.CustomerNameFROM Orders oINNER JOIN Customers c ON o.CustomerId = c.CustomerIdWHERE o.OrderDate >= @StartDateAND o.OrderDate < @EndDateORDER BY o.OrderDate DESCOFFSET 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 ThoughtsPerformance 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.