As I mentioned in a prior post, increasing my Powershell skills is on my list of techie resolutions for this year. I haven’t done much with it yet, so that leaves me with writing about T-SQL. That’s fine because I love working with T-SQL. I’ve been fortunate to have worked on some really fun automation projects with T-SQL and SQL Server Agent over the years. My first thought for this post was to describe one of my favorites, but I decided to take a different route.
So let me ask this… Have you used T-SQL? Yes? Then you’ve done automation with T-SQL! Here’s why I would say such a thing.
Several years ago I was brought in to meet with a development team that was dealing with performance issues in some stored procedures. They had decided that the solution was to do processing outside of SQL Server (for context I should mention that this team included the type of Oracle fans who think that SQL Server isn’t a serious database management system). They began describing their plan, and how it would be fast because it would be written in C++, all processing would take place in memory, etc. I asked how big the data set would be during processing. They replied that it would be larger than the amount of RAM on the server. However, the developers had come up with a paging scheme involving a file on disk (the operating system already has disk paging, but they wanted to control when this would happen), access methods, etc.
At some level you have to admire this amount of ambition. However, in some ways they were proposing building a database management system, but without the good and important things like ACID properties.
So as you might imagine, I eventually let this development team know that everything they were proposing was already in SQL Server, and no complex C++ application needed to be developed and maintained for years to come. Using features in the Standard Edition of SQL Server, T-SQL and existing hardware, we were able to solve the performance problems while still using stored procedures and the existing process flow.
I started down this path by saying that just using T-SQL is enough to be considered “automation”. To those of us who use it every day, that might not seem appropriate. However, consider all that’s going on under-the-hood each time you submit a query. Or from another angle, if you don’t know what’s available and waiting to be utilized on your server, then you might set out to build a similar automated solution from scratch. Is “automation” in the eye of the beholder?
I’m looking forward to reading the contributions to this month’s T-SQL Tuesday and seeing all the ideas for better living through automation. Thank you very much to Pat Wright for hosting T-SQL Tuesday #15, and thanks again to Adam Machanic (Blog|Twitter) for creating this monthly blog event!