Audit who has sysadmin in SQL Server, including direct members and Windows groups, then investigate nested access and forgotten logins.
SQL SERVER – What is – DML, DDL, DCL and TCL – Introduction and Examples
What are DML, DDL, DCL and TCL? A short introduction to the four groups of SQL commands, with examples like SELECT, CREATE, GRANT and COMMIT.
SQL SERVER – Time Out Due to Executing DELETE on Large RecordSet
Why does a DELETE on large recordset time out? When you want every row gone, use TRUNCATE instead, but keep in mind it resets the identity value.
Conditional COUNT: ELSE 0 Counts Nonmatching Rows
Conditional COUNT includes zero when CASE returns it. I compare an omitted ELSE with a zero branch before aggregating. A no-match group and an empty input expose the difference from SUM.
SQL SERVER – 2005 – Change Compatibility Level – T-SQL Procedure
The safe way to change compatibility level in SQL Server 2005: single user mode, sp_dbcmptlevel, then multi user again, and how to check it.
SQL SERVER – Reclaim Space After Dropping Variable – Length Columns Using DBCC CLEANTABLE
DBCC CLEANTABLE can reclaim selected space left after dropping variable-length columns. I distinguish that table operation from shrinking a database file.
SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
How do you find Fragmentation Information for a table in SQL Server 2005? DBCC SHOWCONTIG has limits, so I use sys.dm_db_index_physical_stats instead.
SQL SERVER – Execute Same Query and Statement Multiple Times Using Command GO
Need to run the same statement several times without copy and paste? Put a number after the command GO, like GO 5, and SQL Server runs the batch that many times.
Matching Two Customer Lists When Names and Emails Are Messy
Clean the text, climb from safe rules to fuzzy ones, and keep one best answer per subscriber. A ladder of joins you can paste into SSMS today.
SQL SERVER – 2005 – Introduction and Explanation to SYNONYM – Helpful T-SQL Feature for Developer
SQL Server 2005 added SYNONYM, a single-part name that replaces a long four part object name. I explain how it saves typing and shields code from changes.