Turn key-value pairs into reporting columns with conditional aggregation and PIVOT, handle missing values and conversions.
SQL SERVER – Simple Puzzle with UNION Part 5
In Puzzle with UNION Part 5, the final part, two almost identical queries return the same answer, yet their execution plans are very different. Why?
SQL SERVER – Simple Puzzle with UNION
Try this simple puzzle with UNION and ORDER BY. Guess what each of the three queries returns, and see why one of them fails with Msg 108 and Msg 104.
Table-Valued Parameters: Sending Thousands of Rows in One Call
Send thousands of rows in one call with table-valued parameters, compare round trips, and handle missing column statistics accurately.
Read-Only Filegroups: Locking Away Old Data
Read-only filegroups let you lock old data so nothing can change it by accident. This post moves old rows to their own filegroup, makes it read-only, shows what still works and what fails, and explains how partial backups skip the locked data.
Database Roles: Give Permissions to Groups, Not People
Database roles let you grant permissions to a group once, then add and remove members as jobs change. This post covers the fixed roles and the risk of db_owner, shows how to create a custom role, and checks role membership and permissions with queries.
Script Table As in SSMS: CREATE, INSERT and SELECT in One Click
Script Table As is the right-click menu in SSMS that writes CREATE, SELECT, INSERT, UPDATE and DELETE statements for a table. This post shows what each option writes, what the scripts leave out, and how to run them safely.