An introduction to the Rollup Clause in SQL Server, which aggregates at several levels of a hierarchy. A population table by country, state and city shows how.
geometry STOverlaps: Intersection Alone Is Not Enough
geometry STOverlaps asks a narrower question than whether two shapes share any points. I test equal and contained regions alongside a partial overlap. Those intersecting cases do not all satisfy the method’s overlap definition.
SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)
Is IF EXISTS(Select null from table) faster than IF EXISTS(Select 1 from table)? A great comment thread by Divya, Brian and Marko answers it.
SQL SERVER – Recompile Stored Procedure at Run Time
Two ways to recompile a stored procedure at run time in SQL Server: add WITH RECOMPILE when you create it, or ask for a one-time recompile.
SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning
Plan recompilation hurts performance just like a first compile. Learn what causes it, from schema changes to SET options, and how to reduce recompilation.
SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Cannot drop a database because it is in use? SET SINGLE_USER WITH ROLLBACK IMMEDIATE rolls back open work first. Be careful with it on production.
smalldatetime: Minute Rounding Can Change the Day
smalldatetime does more than hide seconds. These explicit conversions show minute rounding and a midnight carry, with complete original timestamps retained.