Bad Idea Jeans: Building Big Query Plans

When you build a monitoring tool that sends advice via email, you discover two things: Some people have really, really big execution plans Email servers have reasonable limits on file attachment sizes Add those two things together, and Richie found himself working on a new feature for SQL ConstantCare® that would automatically break up advice…
Read More

Stupid T-SQL Tricks

Presented without comment: Transact-SQL CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO 1234 CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT);GOSELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE];GO Next up, can you break up a query with spaces? Yep: Transact-SQL SELECT * FROM…
Read More

How to Throttle Logins to SQL Server

So, uh, you can use WAITFOR in a logon trigger: Transact-SQL CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'NormTheNewGuy' WAITFOR DELAY '00:00:15'; END; GO 123456 CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON ASBEGINIF ORIGINAL_LOGIN()= 'NormTheNewGuy'    WAITFOR DELAY '00:00:15';END;GO You probably don’t want it to be TOO…
Read More

How to Drop All Your Indexes – Fast

Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: DropIndexes for SQL Server 2016 & Newer Transact-SQL CREATE OR ALTER PROCEDURE dbo.DropIndexes @SchemaName NVARCHAR(255) = 'dbo',…
Read More

Bad Idea Jeans Week: Building a Fork Bomb in SQL Server

Somewhat different than a sex bomb, a fork bomb is a denial-of-service attack that just starts a process that replicates itself, thereby starting more and more processes until the service goes down. Wikipedia’s fork bomb page lists examples on most operating systems (including Windows). I’ve always found fork bombs funny because of their elegant simplicity, so…
Read More

Creating Tables and Stored Procedures in TempDB – Permanently

No, not #tables – actual tables. Here’s how: USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can create…
Read More