The SQL Booklet · SQL Server
SQL Server for every day.
Syntax, database exploration, troubleshooting, and execution plans in one place.
SELECT and FROM: choose what information to read
Choose the columns and source objects that should shape a result set.
Read article ReferenceWHERE: decide which rows meet the condition
Keep only the rows that satisfy an explicit condition.
Read article ReferenceDISTINCT, TOP, and ORDER BY: reduce and sort a result
Remove repeated rows, limit a result, and define a predictable order.
Read article ReferenceINNER JOIN: keep matching rows
Return only rows that find a match on both sides of a relationship.
Read article ReferenceLEFT, RIGHT, and FULL JOIN: keep unmatched rows
Preserve rows that do not find a match while still exposing related data when it exists.
Read article ReferenceCROSS JOIN, CROSS APPLY, and OUTER APPLY: combine or correlate rows
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceCOUNT, SUM, AVG, MIN, and MAX: summarize a set
Reduce a set of rows to useful totals, averages, boundaries, or counts.
Read article ReferenceGROUP BY and HAVING: summarize and filter groups
Summarize rows by group and filter the resulting groups after aggregation.
Read article ReferenceCASE: express decisions inside a query
Express conditional values directly inside a query.
Read article ReferenceCOALESCE, ISNULL, and NULLIF: work with missing values
Provide fallbacks, replace nulls, and deliberately turn equivalent values into NULL.
Read article ReferenceINSERT: add rows explicitly
Add rows while making the target columns and supplied values explicit.
Read article ReferenceUPDATE: change only the intended rows
Change existing data while keeping the affected set visible and deliberate.
Read article ReferenceDELETE and TRUNCATE: remove data with different scopes
Remove rows with the operation whose scope and logging behavior match the task.
Read article ReferenceUNION and UNION ALL: combine compatible results
Stack compatible result sets and choose whether repeated rows should be retained.
Read article ReferenceSubqueries, EXISTS, and NOT EXISTS: use related results
Use the result of one query to filter or qualify another result.
Read article ReferenceWITH and CTEs: name an intermediate result
Give an intermediate result a name so a larger statement remains readable and composable.
Read article ReferenceOVER and PARTITION BY: calculate without losing detail
Calculate values across related rows without collapsing the detail of the result.
Read article ReferenceROW_NUMBER, RANK, and DENSE_RANK: number and rank rows
Assign sequence or rank values according to an explicit ordering rule.
Read article ReferenceOFFSET and FETCH: split an ordered result into pages
Return a stable page from an ordered result set.
Read article ReferenceCAST, CONVERT, TRY_CAST, and TRY_CONVERT: control data types
Convert values deliberately and decide how invalid conversions should behave.
Read article ReferenceText functions: build, locate, and normalize strings
Build, search, trim, replace, and normalize character data.
Read article ReferenceDate functions: calculate periods and safe boundaries
Calculate date boundaries and periods without relying on fragile string manipulation.
Read article ReferenceTemporary tables and table variables: keep intermediate results
Keep intermediate rows during a batch or stored procedure.
Read article ReferenceTransactions and TRY/CATCH: complete or roll back a unit of work
Treat related statements as one unit and handle failures without leaving partial changes.
Read article ReferenceComparison and logical operators: build precise conditions
Combine comparisons and logical operators into conditions that match the intended rule.
Read article ReferenceDECLARE, SET, and SELECT assignment: work with variables
Declare variables and assign values in a predictable way.
Read article ReferenceIF, ELSE, and BEGIN...END: decide which statements run
Run different statements according to a condition.
Read article ReferenceWHILE, BREAK, and CONTINUE: control repeated execution
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceCREATE, ALTER, and DROP: manage database objects
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceCREATE TABLE and constraints: define a reliable table
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceALTER TABLE: evolve an existing table deliberately
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceViews: name and reuse a query
Give a reusable query a database object name and a stable interface.
Read article ReferenceStored procedures: encapsulate a parameterized operation
Encapsulate an operation behind parameters and an executable database object.
Read article ReferenceUser-defined functions: package reusable calculations
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferencePIVOT and UNPIVOT: reshape rows and columns
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceINTERSECT and EXCEPT: compare compatible sets
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceOUTPUT, inserted, and deleted: inspect affected rows
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceSTRING_AGG and STRING_SPLIT: join and split delimited text
Convert rows into delimited text or split delimited text back into rows.
Read article ReferenceJSON: read, transform, and produce documents in SQL Server
Read, shape, validate, and produce JSON from SQL Server.
Read article ReferenceMERGE: understand its scope and important cautions
Use this SQL Server feature to express the operation clearly and keep its scope visible.
Read article ReferenceOPTION and query hints: influence compilation
Influence a compilation decision while keeping the hint and its scope visible.
Read article Applied casesQuery customers with confirmed orders
We need to prepare a list of confirmed orders. The customer name is in Cliente, while the date and status are in Pedido.
Read article Applied casesFind customers who do not have orders yet
We want to identify active clients that do not yet appear in Pedido. The difficulty is to preserve precisely the rows that do not find a match.
Read article Applied casesShow the first available phone number for each customer
The system stores mobile phone and home phone in different columns. We want to present a single contact telephone number, preferring the mobile phone and avoiding treating an empty string or one composed of spaces as a v
Read article Applied casesCalculate total sales by customer and filter the highest totals
We want to know how much each customer has purchased in confirmed or shipped orders. Each item has quantity, unit price and discount; therefore, the total is not saved directly in Pedido.
Read article Applied casesInsert an order and its line items safely
An order and its items represent one operation. A transaction lets us commit all its changes or roll them back as one unit. If the header is saved but an item fails, we do not want to leave incomplete information.
Read article Applied casesUpdate category prices with prior validation
We need to increase the price of active products in the Accesorios category by 8%. Before modifying anything, we want to inspect the scope and capture the old and new values during the update.
Read article Applied casesCombine current and historical orders
Recent orders remain at Pedido and PedidoDetalle; the old ones were summarized in PedidoHistorico. We want a single timeline without hiding where each row comes from.
Read article Applied casesReturn the latest order for each customer
We want to show a single row per customer with their most recent order. Grouping by customer and using MAX(FechaPedido) gets the date, but doesn't safely fetch the ID and status of that same row.
Read article Applied casesCalculate daily sales and a running total
We need a daily sales series and, next to each day, the amount accumulated since the beginning of the period. The first calculation reduces order lines to one row per day; the second uses those rows without reducing them
Read article Applied casesBuild paginated search with optional filters
A product screen allows you to type part of the name, choose a category, and limit a price range. All filters are optional and the result must be divided into stable pages.
Read article Development and explorationSearch for a table or view by name
We know a word in the name—for example, “Order”—but we don't know if the object is a table, a view, or what schema it is in. Opening folder by folder works on a small basis; on a legacy basis it soon ceases to be a relia
Read article Development and explorationSearch for a column across the database
A screen, error, or requirement mentions IdCliente, but we don't yet know which tables hold that data. The name of a column is often a useful clue to start rebuilding the model.
Read article Development and explorationSearch for text inside procedures, views, and functions
We need to know where the name of a specific table, column or call appears. Manually reviewing each stored procedure can hide just the reference that explains a behavior.
Read article Development and explorationInspect the dependencies of an object
Before modifying a table, view or procedure we want to know what objects it mentions and what other objects mention it. The question is not just “does it exist?”, but “what could be affected?”
Read article Development and explorationList relationships and foreign keys
We find a table, but we still don't know which other tables point to it or which ones it points to. Foreign keys record a portion of those relationships and also reveal rules that can affect a delete or update.
Read article Development and explorationInspect primary keys, unique constraints, and indexes
We need to understand how each row is identified and what structures exist to preserve uniqueness or support searches. Seeing just the name of an index is not enough: the order of its columns, the included columns, and t
Read article Development and explorationList columns with type, length, nullability, and default value
We found the table we were looking for, but we still don't know what data it expects. Before writing a INSERT, preparing an integration or changing a column, we need a readable x-ray of its structure.
Read article Development and explorationFind triggers and their associated tables
An INSERT, UPDATE, or DELETE causes additional changes, rejects an operation, or takes longer than expected. Some of that logic may be in a trigger and run without appearing in the original statement.
Read article Development and explorationFind recently modified objects
A behavior changed after a deployment or we need to recognize which objects have received recent modifications. The catalog offers a useful date to narrow down the initial search.
Read article Development and explorationGet approximate row counts for every table
We want to quickly recognize which tables are empty, which have thousands of rows, and which dominate the volume of the database. Running COUNT(*) on each table may be unnecessarily expensive for a first scan.
Read article Development and explorationMeasure the space used by each table
Is the base growing or do we need to decide where to focus a review. The number of rows helps, but it doesn't reveal how much storage is consumed by data, indexes, and reserved pages.
Read article Development and explorationInspect IDENTITY columns and their current value
A table has breaks in its identifiers, has received a load with explicit values, or is approaching the limit of its numeric type. Before modifying the sequence we need to know its definition and the last value generated.
Read article Development and explorationFind duplicate rows by business key
A payload contains clients with repeated emails, or a table allows storing several rows that represent the same functional data. The primary key distinguishes each record, but does not answer whether the business conside
Read article Development and explorationFind orphaned rows
A load contains orders whose customer does not appear in the received catalog. It can also occur on a basis without foreign keys, after an incomplete migration, or when the relationship is validated only in the applicati
Read article Development and explorationCompare two data sets
A product load should match the current table, but we need to know which rows are missing and which contain different values. Comparing only the number of records can hide important changes.
Read article Development and explorationValidate conversions before changing a column
A load table saves dates and quantities as text. Before converting the columns or moving the data to final tables we need to find the values that do not comply with the expected format.
Read article Development and explorationRun an UPDATE with controlled validation and rollback
We need to increase the price of active products in a category. The condition seems simple, but an incorrect filter could modify more rows than expected and leave the error confirmed before it is detected.
Read article Development and explorationRun a DELETE with a logical backup and controlled rollback
A correction requires removing inventory movements identified and older than a date. In addition to checking the filter, we need to retain a recoverable copy if the deletion is confirmed.
Read article Development and explorationCompare the structure of two databases
An application works in development, but fails to deploy because a column has a different length, allows null values, or uses a different type in production. Going through the tables one by one makes it easy to miss a sm
Read article Development and explorationDescribe the result of a query or stored procedure
We need to integrate a query into an application, but we don't know for sure what names, types, and nullability it will return. Running it with real data can be expensive or require parameters that we do not yet have pre
Read article Development and explorationFind tables without a primary key
An update needs to identify each row unambiguously, but we don't know which tables declare a primary key. We also want to locate structures that deserve review before an integration or migration.
Read article Development and explorationSearch selected columns for a value
We know a name, email or reference, but we don't remember which of several columns of text it was saved in. Generating a search over the entire database would be expensive and could read information we don't need.
Read article Troubleshooting and optimizationView active requests and their SQL text
The application is responding slowly and we need to know what SQL Server is running right now. A list of connected sessions is not enough: a connection can exist without having an active request.
Read article Troubleshooting and optimizationIdentify blocking and blocked sessions
A request remains suspended and returns an identifier in blocking_session_id. We need to know who is waiting, what session is ahead, and what text we can inspect at both ends.
Read article Troubleshooting and optimizationBuild the blocking chain
Several sessions appear blocked and some of them also block others. Reading isolated pairs makes it difficult to find the session that leads the problem.
Read article Troubleshooting and optimizationFind long-running open transactions
A session leads to locks or the log grows while a transaction remains open. We need to measure how long it exists and relate it to the session that maintains it.
Read article Troubleshooting and optimizationFind idle sessions with an open transaction
A session appears as sleeping, but continues to block other requests or prevent reuse of log space. You are not executing a request and still maintain a transaction.
Read article Troubleshooting and optimizationView waits for active requests
A request is suspended, but the wait type displayed in its queue does not account for all of the tasks involved. This is especially true in parallel queries, where different tasks may wait for different reasons.
Read article Troubleshooting and optimizationReview the main accumulated waits
The server feels slow at different times, but a snapshot of active requests does not show a stable pattern. We need to know in which waiting families the time has been concentrated since the beginning of the measurement.
Read article Troubleshooting and optimizationFind queries with the highest total CPU use
CPU usage is high or has repeated spikes. Before reviewing random plans, we need to locate the completed statements that have accumulated the most CPU among the plans still present in cache.
Read article Troubleshooting and optimizationFind queries with the highest logical reads
The data load seems excessive or certain operations put pressure on memory and CPU. We want to identify completed statements that have requested more pages from the buffer during the life of their cache plans.
Read article Troubleshooting and optimizationFind queries with the highest average duration
Users report slow responses, but the total consumption hides infrequent operations. We need to locate statements whose average elapsed time is high among the plans still present in cache.
Read article Troubleshooting and optimizationRetrieve a cached execution plan
A sentence appears among those with the highest CPU, readings or duration. To understand how SQL Server decided to run it, we want to retrieve the associated plan that is still cached.
Read article Troubleshooting and optimizationReview missing-index suggestions
A query does a lot of reads or has expensive access and the plan suggests an index. We want to gather the tips recorded for the current base and sort them by their estimated cumulative profit.
Read article Troubleshooting and optimizationReview index usage
A table has several indexes and we need to know which ones have participated in reads and which ones have been maintained by data changes. Comparison helps find candidates who deserve a deeper review.
Read article Troubleshooting and optimizationReview index fragmentation with controlled scope
A large index participates in relevant reads and we want to know its fragmentation before considering maintenance. The review should be limited to a specific object so as not to make the diagnosis an unnecessary burden.
Read article Troubleshooting and optimizationReview statistics age and modifications
A plan estimates row quantities that are very different from those observed or changes unexpectedly. We need to know when its statistics were updated, how much it was sampled, and how many modifications the main column o
Read article Troubleshooting and optimizationMeasure data and log space by database
A database approaches the volume limit or the log file grows frequently. We need to separate allocated size, used space and still available space within the current files.
Read article Troubleshooting and optimizationDiagnose why the transaction log cannot be reused
The used percentage of the log continues to grow or does not decrease after the expected operation. We need to identify what condition SQL Server reports as currently preventing truncating and reusing inactive records.
Read article Troubleshooting and optimizationRetrieve recent deadlocks from system_health
An application receives deadlock victim errors, but the deadlock is already gone when the server is checked. We need to recover the graph maintained by the system_health session to know participating processes and resour
Read article Troubleshooting and optimizationFind regressed queries with Query Store
An operation that previously responded normally now takes longer, but the previous plan is no longer cached. We need to compare a recent window with a base period maintained by Query Store.
Read article Troubleshooting and optimizationFind sessions and requests consuming tempdb
tempdb grows, approaches its limit, or shows pressure during a particular load. We need to know which sessions retain user and internal object assignments and how much corresponds to still active tasks.
Read article Troubleshooting and optimizationReview pending or excessive memory grants
There are RESOURCE_SEMAPHORE waits, suspended queries, or few operations that seem to reserve much of the available memory for execution. We need to see pending applications and large grants that are still active.
Read article Troubleshooting and optimizationCalculate read and write latency by file
Waits point to entry and exit or multiple bases share storage. We need to compare how long reads and writes wait for each file based on the counters accumulated by SQL Server.
Read article Troubleshooting and optimizationFind queries with multiple plans and different performance
The same query alternates between fast and slow responses, or [Query Store window comparison](/en/troubleshooting/find-regressed-queries-with-query-store/) showed degradation. We want to compare the plans that Query Stor
Read article Execution plansWhat an execution plan is for
We write a query to state the result we need. SQL Server must still decide how to locate, combine, and process the data that produces it. The execution plan represents that strategy.
Read article Execution plansEstimated plans, actual plans, and Live Query Statistics
SQL Server can show the strategy for a query before executing it, after observing an execution, or while the work is still in progress. All three modes represent the plan, but they do not contain the same evidence or car
Read article Execution plansHow an execution plan tree is organized
An execution plan may extend from left to right on the screen, but its structure is described as a tree. Recognizing its parts lets us follow the strategy without relying yet on the meaning of every icon.
Read article Execution plansArrows, properties, and execution counts
An execution plan diagram lets us recognize operators and connections, but its visible labels do not contain all the evidence. Arrows provide an initial orientation; properties let us verify which quantity they represent
Read article Execution plansOperator map: what role each node plays
A small plan can contain lookups, filters, joins, calculations, and sorts in the same branch. Reading their names as an isolated list requires memorizing icons; grouping them by function makes it possible to reconstruct
Read article Execution plansEstimated rows and actual rows
SQL Server calculates how many rows it expects at each operator and, when we capture an actual plan, records how many it observed during that execution. Comparing both figures helps locate an assumption that did not matc
Read article Execution plansIndex Seek, Index Scan, and Table Scan
Two queries can return the same row while traversing different structures to find it. The operator name summarizes that access; its properties show how much it read, what it returned, and which condition it applied.
Read article Execution plansKey Lookup and repeated work
An index can locate the requested rows without containing every column needed by the query. SQL Server then uses the locator stored in that index to return to the complete row. The plan represents that second access as a
Read article Execution plansFilter, Compute Scalar, and Top
Not every operator finds data or relates tables. Some receive an existing stream and transform it: discard rows, calculate a column, or stop returning rows after reaching a limit.
Read article Execution plansNested Loops, Hash Match, and Merge Join
A query can contain a single INNER JOIN and produce the same result through different physical strategies. The JOIN type belongs to the logical expression; Nested Loops, Hash Match, and Merge Join describe how SQL Server
Read article Execution plansSort, Segment, and Sequence Project
A window function preserves every row and adds a value calculated across related rows. To produce numbering within groups, the plan must arrange the stream, recognize where each group changes, and maintain the sequence s
Read article Execution plansStream Aggregate and Hash Match Aggregate
GROUP BY states that several rows must become one result per group. The plan must still choose how to maintain those groups while calculating COUNT, SUM, or another aggregate.
Read article Execution plansSpool: materialization and reuse
Some branches produce rows that another operator may request repeatedly. SQL Server can preserve that intermediate result in a work structure and read it again instead of rebuilding it from the source every time.
Read article Execution plansParallelism: distributing, repartitioning, and gathering rows
A serial plan performs work through one stream. A parallel plan can divide part of that work among several workers and then coordinate the rows they produce.
Read article Execution plansExecution plan warnings that require investigation
A yellow triangle draws attention to a condition SQL Server identified while compiling or executing a query. The warning identifies where to look; by itself, it does not prove what caused a delay or prescribe a correctio
Read article Execution plansActions available in the SSMS graphical plan
The graphical plan is more than an image. SSMS can inspect an operator, open the XML representation, save the plan, and prepare comparisons or analysis. Each action exposes different evidence; none turns the context menu
Read article Execution plansHow Showplan XML is organized
The SSMS graphical plan is built from an XML document. The graphic makes orientation easier; the XML preserves the complete structure, the attributes of each operator, and, for an actual plan, the measurements observed d
Read article Execution plansWalk through Analyze Actual Execution Plan
Analyze Actual Execution Plan reviews an actual plan and highlights patterns that SSMS recognizes in the observed execution. The tool helps locate a point that deserves investigation; it does not confirm the cause or dec
Read article Execution plansReads with SET STATISTICS IO
The plan shows the strategy chosen to answer a query. SET STATISTICS IO adds another piece of evidence: how many pages SQL Server read from each object during that execution.
Read article Execution plansCPU and elapsed time with SET STATISTICS TIME
A slow query does not necessarily keep the processor busy for its entire duration. SET STATISTICS TIME separates CPU work from elapsed time and also reports how long parsing and compilation took.
Read article Execution plansBuild a repeatable baseline
A single measurement describes one execution. A baseline preserves the relevant conditions, repeats the query, and records its normal variation before anything changes.
Read article Execution plansSave and compare two plans
Two queries can return the same result through different strategies. Saving each plan as .sqlplan preserves the evidence and lets Compare Showplan align both structures.
Read article Execution plansHistory and regressions with Query Store
The plan visible in one execution cannot explain how the query behaved yesterday or which strategy it used in another interval. Query Store persists query text, plans, and runtime statistics in the database so those chan
Read article Execution plansTurn an Analyze Actual Execution Plan scenario into a hypothesis
An Analyze Actual Execution Plan scenario points to a difference that deserves investigation. Turning that signal into a hypothesis requires a proposed explanation, a prediction of what should change if it is correct, an
Read article Execution plansRead a Missing Index suggestion in an execution plan
A Missing Index suggestion means the optimizer found a hypothetical structure for which it estimated a lower query cost. The signal deserves review, but it does not prove that the index should be created or that the disp
Read article Execution plansUnderstand the CREATE INDEX generated by SSMS
Missing Index Details turns an optimizer request into a readable statement. The statement helps identify a possible structure, but it is not yet an index ready to create: it keeps a placeholder name and does not resolve
Read article Execution plansDecide, create, and verify an index
An index suggestion becomes a decision only after it is compared with existing structures, tested with the same query, and documented with both its benefit and its added cost. In this test, the candidate reduces reads fo
Read article Execution plansFrom a slow query to a testable hypothesis
A slow query is a symptom. Turning it into a useful investigation requires preserving the query, gathering evidence, and writing an explanation that can fail. The first signal in the plan is not yet the cause.
Read article Execution plansEvaluate a change without optimizing for one execution
An alternative is not validated merely because one execution finishes quickly. It must preserve the result, repeat the benefit with different parameters, and show which aspects improve, which remain unchanged, and which
Read article Execution plansGuided analysis of a reproducible business query
An optimization ends when we can explain what the business needed, what work SQL Server performed, what changed, and what still requires verification. This case follows the complete process without claiming more than one
Read articleNo entries match those words.