Each rule, including this one, has exceptions.

I don't know them all.

Corollary: Rules can conflict. Everything is a tradeoff.

SARGability

• SARG is short for Search ARGument-able.
• Mostly refers to WHERE and JOIN conditions.
• Could SQL Server use an index to resolve the condition if it had one?

Example:

                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        WHERE  ISNULL(a.TextField, N'') = N''
                        

Example:

                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        WHERE  ISNULL(a.TextField, N'') = N''
                        

If dbo.OurTable has an index on TextField, we can't use it!

But shouldn't SQL Server use the index to check for both NULL and N''?

Maybe, but it doesn't.

SQL Server is NOT THAT INTELLIGENT!

You need to check the values separately.

                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        WHERE  a.TextField IS NULL OR a.TextField = N''
                        

                            -- Bad because a.TextField is NVARCHAR.
                            WHERE  a.TextField = ''
                            -- Compare to NVARCHAR instead.
                            WHERE  a.TextField = N''
                            

EXISTS and NOT EXISTS

• Are you using IN or NOT IN with a subquery?
• Are you using a LEFT JOIN just to see if another table has a certain value?
• Do you suffer from slow queries? Too many rows in a result set?
• Ask your presenter if EXISTS and NOT EXISTS are right for you!
• (They are.)

I definitely made up that term. Maybe someone else has a better name.

                        -- Avoid:
                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        WHERE  a.TextField IN (SELECT b.AnotherTextField
                                               FROM   dbo.AnotherTable b
                                               WHERE  b.Id = @SomeParameter)
                        -- NOT IN is even worse.
                        
                        -- Also avoid:
                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        LEFT JOIN dbo.AnotherTable b ON a.TextField = b.AnotherTextField
                        WHERE  b.AnotherTextField IS NOT NULL

                        -- Use EXISTS or NOT EXISTS:
                        SELECT a.Id, a.Whatever
                        FROM   dbo.OurTable a
                        WHERE  EXISTS (SELECT 1 FROM dbo.AnotherTable b
                                       WHERE  a.TextField = b.AnotherTextField
                                       AND    b.Id = @SomeParameter)
                        

Exception

Don't use EXISTS or NOT EXISTS in the SELECT clause.

                        -- Avoid:
                        SELECT a.Id,
                               a.Whatever,
                               CASE WHEN EXISTS (SELECT 1 FROM dbo.AnotherTable b WHERE a.TextField = b.AnotherTextField) THEN 1
                                    ELSE 0 END AS DontDoThis
                        FROM   dbo.OurTable a
                        

Workarounds can be difficult. Consider a LEFT JOIN to a subquery or CTE with GROUP BY.

Table Variables

• Don't use them.
• So, no DECLARE @DontDoThis TABLE
• Use temp tables instead.
• CREATE TABLE #ThisIsBetter

User-Defined Functions

• Objects you create with CREATE FUNCTION statements.
• Use cautiously.
• Avoid scalar user-defined functions in the SELECT clause.
• Such functions cause forced serialization.
• Worse, the scalar function executes individually, row by row, for each row in the result set.
• If you guessed that's really slow…
• Some built-in functions, such as cryptographic functions, cause similar behavior.
• If you must have such a function, consider splitting the query into two queries. First query inserts into a temp table without using the function. Second query runs the function against the temp table.

User-Defined Functions

• Table-valued functions also must execute against each row.
• Same is true of built-in functions like STRING_SPLIT.
• If you have a way to JOIN rather than APPLY — do something besides use the function directly — consider it.

Transaction Isolation Levels

One of the first things relational-database newcomers learn is ACID.

• Atomicity
• Consistency
• Isolation
• Durability

So that works for real-world SQL Server applications, right?

🤣

We can specify isolation levels in .NET (C#) application code as
using (var transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
or in SQL as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Isolation levels in SQL Server:

• READ UNCOMMITTED
• READ COMMITTED (which has two different meanings depending on database configuration)
• SNAPSHOT (has special requirements)
• REPEATABLE READ
• SERIALIZABLE (has nothing to do with "forced serialization" from earlier)

FROM dbo.OurTable a WITH (NOLOCK) on static tables when not guaranteed to be in READ UNCOMMITTED

Common table expressions (CTEs) and subqueries

• Not persisted anywhere.
• May be re-run (many times in extreme cases).
• Can force serialization in some situations.
• EXISTS / NOT EXISTS outside the SELECT clause is an exception (it's a type of join).
• May need CTEs for updates (no aggregates in SET clause), window functions, and recursion when you have no better option.

Do work in the application tier instead of the database whenever reasonable

• Sorting result sets is a great example if the result set would not already be sorted due to index order (can't use with paging).
• Can allow receiving first results sooner. In Dapper, use buffered: false, then .AsParallel().OrderBy(x => x.Whatever).ToList()
• Pass easily-used parameters — don't make the query do the work. Example: pass a bit rather than a string if the string would just be compared to a constant.
• Cache static query results and possibly even use the cache to eliminate joins — but see shouldiblamecaching.com.

Avoid RBAR

• Row by Agonizing Row
• SQL is designed for you to tell it the results you want involving data sets.
• SQL is not designed for you to loop.
• You might need a prepare/execute pattern for multi-row insert, but sometimes alternate means could perform as well. Example: JSON (though performance can also be bad).
• Cursors have their place if you absolutely must loop, but use the least-resource-intensive cursor that makes sense. FAST_FORWARD usually, or STATIC with FORWARD_ONLY if you need unchanging results. (Look up these keywords if you need cursors.)
• WHILE can make sense in some situations, but ensure no batch or application solution is feasible.
• A standard query may still produce an RBAR execution plan. Remember scalar-valued user-defined functions?

Index Use

A much larger topic than we can discuss in any detail in a 75-minute talk

• Know what indexes you have.
• If you're not using the first indexed field, subsequent fields don't help you.
• If you can use an index-only query, do so. Key lookups can be expensive.
• Indexes are not magic. Poor indexes can hurt overall performance.

Execution Plans

Also known as query plans

An execution plan results from SQL Server's compiling a query. You can look at execution plans and… learn things.

Getting Started

• You need SHOWPLAN permissions on the database
• Production vs. non-production
• Estimated vs. actual vs. live
• Will differ with different workloads, statistics, and (sometimes) parameters
• Lots of ways to get plans, including:
  • SQL Server Management Studio (or whatever Microsoft calls it this week)
  • SQL Server Profiler
  • Query store (if enabled) and cache — use Brent Ozar's free "First Responder Kit" at www.brentozar.com/first-aid
  • SET STATISTICS XML ON
• The server returns XML, but really, you're going to look at diagrams in your tool of choice.

Most Important Information

• Basic nodes, especially select, sort (avoid), filter, and join
• Total execution time
• Memory grant — queries cannot start until SQL Server grants them the plan's required memory
• Compilation statistics (time, cache, parallel, allocated memory)
• Cardinality estimates (fancy way of saying "how many rows")
• Warnings (sometimes)

What to Ignore

Query Costs

Also, percentages

Also, specific index suggestions

Temporary Stored Procedures

• Yes, that's a thing.
• CREATE OR ALTER PROCEDURE #MyTempProcedure
• Use to replicate any parameterized query or stored procedure.
• Query plan differs if you just use variables.
• Be sure to roll back transactions!