Understand contained users in SQL Server databases, how they travel with a database, and the security trade-offs of partial containment.
SQL SERVER – Observation – Effect of Clustered Index over Nonclustered Index
An observation from my query optimization work: the effect of clustered index on a nonclustered index, shown with a sample table and execution plans.
SQLAuthority News – Download SQL Server 2008 System Views Poster – PDF – A Wall Poster
Microsoft published the SQL Server 2008 System Views Poster, a map of the key system views and how they relate. I keep it on my wall. Download the PDF.
SQL SERVER – T-SQL Script for FizzBuzz Logic
FizzBuzz is a common interview puzzle: print 1 to 100, with Fizz for multiples of three and Buzz for five. Here is my T-SQL answer using a WHILE loop.
Using sp_help and Friends to Explore a Database
Using sp_help, sp_helpindex, sp_helptext, and sp_spaceused gives a quick SQL Server tour before you reach for catalog views.
SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration
Msg 5834 says the affinity mask conflicts with the IO affinity mask when you change fill factor with sp_configure. The fix is RECONFIGURE WITH OVERRIDE.
SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
A quick script to rebuild every index of all tables in a database with a fillfactor of 80, plus a short note on what fillfactor does to leaf-level pages.