To count a particular value across all columns of a table, use a CASE sum, CROSS APPLY with VALUES, UNPIVOT or UNION ALL. This post tests all four on a staff contact table, compares their reads on 200,000 rows, and builds the column list with dynamic SQL.
EXCEPT Operator in SQL Server: Distinct Rows and Differences
The EXCEPT operator returns the rows of one query that are missing from another, with duplicates removed. This post shows the difference between two small order tables, the old WHERE 1 = 0 trick for distinct rows, and how NULLs behave.
IIF in SQL Server: IF THEN Logic Compared With CASE
IIF in SQL Server is a short way to write IF THEN logic with two outcomes. This post compares it with CASE in a small juice shop table, shows that both produce the same plan, and covers NULL, data types and the WHERE clause.
How to Track Autogrowth of Any Database? – Interview Question of the Week #205
How do you track autogrowth of any database? Constant autogrowth was slowing a client’s busy e-commerce database, and here is how we found when and who caused it.
SQL SERVER – T-SQL Script to Check SQL Server Job History
SQL Server Agent Job History can be queried from msdb. A New Zealand customer requested this report during our weekly health check.
SQL SERVER – How to Write Correlated Subquery?
A Correlated Subquery references the outer query. I use a stock-item comparison after the earlier single-table aggregate lesson.
SQL SERVER – Two Different Requirements for a Single Table Subquery
A subquery can compare rows with an aggregate from the same table. A junior DBA asked about this over lunch.