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.
Overriding Identity Fields: Put a Deleted Row Back With Its Old ID
Overriding identity fields is rare, but restoring a deleted row with its old ID needs it. This post uses IDENTITY_INSERT to put two workshops back with their old numbers. It shows the five errors you will meet, what happens to the next automatic number, and why gaps in an identity column are usually fine.
Multi-Row INSERT in SQL Server: One Statement, All or Nothing
A multi-row INSERT adds several rows with one statement, using a list of value groups separated by commas. This post compares it with separate INSERT statements on a small table of books. It shows that one bad row undoes the whole statement, and it covers the errors and the 1,000 row limit.
Loading Data From an API Into SQL Server
Loading data from an API into SQL Server takes paging, JSON staging, retries, and durable checkpoints so a failed request can resume safely.
SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31
A guest post by Rick Morelan, author of the Joes 2 Pros series, on the few interview questions that come up again and again and separate good answers from great.
SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31
Paras Doshi’s Azure interview contribution remains part of the 2011 series. Historical answers are clearly separated from current editorial context.