The OUTPUT clause shows the exact rows an INSERT, UPDATE or DELETE just changed, using the inserted and deleted tables. This post covers the output clause in simple examples with a small table of seed packets. It also shows how to save the rows with INTO, what error you get for the wrong table, and why triggers change the rules.
SQL Server emergency? Email pinal@sqlauthority.com with URGENT in the subject line.
NTILE Groups: How Uneven Rows and Ties Land in Each Tile
NTILE groups your sorted rows into the tiles you ask for, but the groups aren’t always the same size. This post uses ten hiking trails to show the sizes when rows do not divide evenly, how tied rows land in different tiles, and what happens with more tiles than rows. It also shows how to pull the top 2 percent of a list.
RANK( ), DENSE_RANK( ), and ROW_NUMBER( ): How Each Handles Ties
RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) return the same numbers until your data has a tie. This post ranks eight teas with all three functions and shows how each one handles tied scores. It also shows how to list the top three distinct scores and why ROW_NUMBER needs a tiebreaker.
geometry STLength: A Path Is Longer Than Its Chord
geometry STLength measures the segments of a line. The example contrasts that path length with the shortest distance between its endpoints, including a closed line.
Partitioned Aggregates: Compare Each Row to Its Own Group
Partitioned aggregates let every row see a number for its own group, such as how many plants share its garden bed. This post uses thirteen seedlings in four beds to show PARTITION BY with COUNT and AVG. It also shows the integer division trap that turns every share into 0, and how to get one row per group.
FOR XML XSINIL: Emit Missing Fields as Explicit Nil Elements
FOR XML XSINIL can represent SQL NULL with an explicit element carrying the xsi:nil attribute. I compare that output with ordinary element omission. An empty string stays a separate input in both versions.
Aggregates With the OVER Clause: A Total Beside Every Row
Aggregates with the OVER clause put a total, an average or a minimum next to every row, so the rows do not collapse into one. This post uses eight trip costs to show the error a plain SUM gives, the fix, and the percent of the total for each row. It also shows how WHERE changes the total and how to filter on the result.
SQL Server slow? Let's fix it together in about four hours.
In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.
You keep every script, and the know-how to fix it yourself next time.
Want your whole team to learn it?
My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.
Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.