T-SQL Tuesday #118 – SQL Server Fantasy Feature

This month’s T-SQL Tuesday invitation comes from Kevin Chant and is named “Your Fantasy SQL Feature”. Kevin writes:

“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.”

The problem

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.

Alternative ideas

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.

Next-level TRUNCATE

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.

SQL Saturday #851 – A new thing in town…

“Why don’t we have a SQL Saturday in Stockholm?” a friend asked me a few years ago. “I honestly don’t know”, was my answer. “There is a lot of people in Stockholm, but for some reason they don’t seem to be able to organize one. There is one in Gothenburg that’s been working fine for a few years, maybe you could ask Mikael Wedham?”

I didn’t know the main organizer Daniel Hutmacher very well back then, but we’ve met a few times through the years and despite him being ill he made it to the last SQL Saturday Gothenburg. I think that he had already started considering a Stockholm-based SQL Saturday already, as he unveiled his idea a bit later. Here we are today, and in two days’ time – Saturday May the 4th – SQL Saturday #851 is going to happen in Stockholm! We’re close to 300 attendees and I an very excited to deliver “Arguing with myself – self-service BI from an infrastructure perspective” at 4.30. The speaker lineup is exceptional: Kevin Kline, Grant Fritchey, Matthew Roche, Pinal Dave, Ruth Pozuelo Martinez, Uwe Ricken, Mikael Wedham, Ola Hallengren and several other internationally renowned speakers are standing by.

I’ve been a (very) small part of the organizing team for SQL Saturday #851, but as my schedule has been absolutely bonkers I haven’t had much time to contribute at all. Daniel and the others have done all the heavy lifting but I am looking forward to contributing onsite during the day. The funny thing with Daniel is that he doesn’t care very much if someone says something can’t be done – yet another reason to work with him or employ his services. He gets things done like few others.

If you’re into SQL Server, like great speakers, free, amazing content, are close to Stockholm and have the Saturday unplanned – come join!

 

SQLSaturday #851 - Stockholm 2019