“My invitation to yourselves this month is to write a post about a fantasy SQL Server feature you’ve got in mind.
It can be absolutely anything at all related to SQL Server. For example, it could be about a new SQL Server operator to improve queries or a new service that does something amazing.
So, you can stretch your imagination as far as you want to. In fact, I actively encourage it.”
Okay, he basically told us to go to town with crazy ideas for SQL Server, and while I have plenty of those, I have one fantasy feature that would make the life of several of my clients much easier – improvements to TRUNCATE. Just think about it: nothing is faster than TRUNCATE when it comes to getting rid of a large amount of data in a table. TRUNCATE is a completely non-logged operation. That means that the transaction log won’t blow up trying to remove a million rows of data. However – TRUNCATE only works on a whole table. If you want to remove data from a specific date range, you’re back to plain ol’ DELETE – and that WILL blow up your transaction log. Not only that – it will introduce the risk of blocking as well.
A way around this is of course to use partitioning. Unfortunately partitioning will introduce its fair share of complexity by and of itself. It is a powerful tool, but I highly suggest you read the manual before attempting anything with it.
How about adding a WHERE statement to TRUNCATE? How awesome would it be to have the ability to chose which rows are affected by the TRUNCATE operation? This function would make pruning data that much simpler. No more complexities of partitioning. No more transaction log in the terabyte range. No more pesky blocking. Having this ability would vastly simplify design and management of large databases.
I doubt we will ever see this feature – in fact, considering how SQL Server (and every other database engines out there) I know it would mean a radical redesign. But one can dream, right?
A huge thank you to Kevin Chant for this fun challenge. I also like to extend an equally huge thank you to Adam Machanic (who started T-SQL Tuesdays almost 10 years ago), and Steve Jones who is currently hosting the monthly challenge.