What is Live Lock in SQL Server? How a write request can keep waiting behind a stream of reads, how it differs from a deadlock and how to reduce it.
Solving SQL Puzzles With Set-Based Thinking
Set-based thinking solves three SQL puzzles with grouping, anti-joins, and windows, then turns each solution into a repeatable query habit.
SQL SERVER – Add Column With Default Column Constraint to Table
Add a NOT NULL column with a named default Column Constraint in one ALTER TABLE statement. I always name constraints instead of letting SQL Server pick one.
Simple CASE and NULL: Use a Searched Condition
Simple CASE and NULL do not match through equality. Use a searched IS NULL branch, preserve empty text, and make the result type explicit.
Porting Stored Procedures Between Platforms
Porting stored procedures means preserving error, transaction, temporary-table, sequence, and date behavior instead of translating tokens.
SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys objects Database
The sys.objects catalog view has a row for every schema-scoped object, so you can retrieve any user defined object details, like foreign keys, by object type.
SQL SERVER – 2005 – Retrieve Processes Using Specified Database
Jim Sz shared a quick script to retrieve processes that use a specific database from sys.sysprocesses, with spid, status, host name and login.