Bad Idea Jeans

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

Last Updated February 13, 2017

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…

Brent’s Bad Idea Jeans [Video]

Last Updated February 13, 2017

Brent Ozar

Bad Idea Jeans, Humor, SQL Server

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…

Using Triggers to Automatically Add Indexes

Last Updated February 13, 2017

Brent Ozar

Bad Idea Jeans, Indexing, SQL Server

Let’s say you’ve got a vendor application that creates tables, loads them, queries them, and drops them all the time. You can’t change the code. The tables involved desperately need an index in order to go faster, and they’re your biggest performance bottleneck. Enter DDL triggers. Regular DML triggers fire when you insert/update/delete rows in a…

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

Last Updated February 13, 2017

Brent Ozar

Bad Idea Jeans, Columnstore Indexes, Indexing, SQL Server

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they’re…