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: Transact-SQL 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…
Read More

Brent’s Bad Idea Jeans: Stop People From Changing Tables with This One Trick

Developers hate his bad idea jeans: Transact-SQL CREATE VIEW dbo.StopThemDead WITH SCHEMABINDING AS SELECT COUNT(*) FROM dbo.Table1 UNION ALL SELECT COUNT(*) FROM dbo.Table2 UNION ALL.... 123456 CREATE VIEW dbo.StopThemDead WITH SCHEMABINDINGASSELECT COUNT(*) FROM dbo.Table1UNION ALLSELECT COUNT(*) FROM dbo.Table2UNION ALL.... Presto, the schemabinding option means no one can change the underlying tables. (At least, until they…
Read More

Brent’s Bad Idea Jeans [Video]

Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including: Warming up SQL Server’s buffer pool by selecting all the data from all the tables Creating indexes with triggers Adding nonclustered indexes to clustered columnstore indexes Compressing backups the hard way Putting databases on a RAM drive In this 20-minute…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}