T-SQL Tuesday #104 : Code I Would Hate to Live Without
T-SQL Tuesday is a great monthly tradition where a bunch of people in the community can contribute to a blog party along a general (or sometimes specific) topic. This month, Bert Wagner invites us to share our most cherished body of work:
For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.
I've written a lot of code over the years. To show for it, I have plenty of blog posts (here, on SQLPerformance.com, and on SQLBlog.org), 3,000+ answers on Stack Overflow, 2,000+ answers on Database Administrators, and over 100 tips on MSSQLTips.com. I end up borrowing from my own examples from time to time; occasionally I come across it accidentally, after searching and having forgotten I wrote the thing in the first place. Not all of those involve code explicitly, but you can see how I might lose track of things once in a while.
None of these things is all that hard to re-write; once you have the concepts down for things, most of the tedium is syntax. There is, however, one piece of reusable code that I go back to again and again.
One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don't make it to the public, and documentation for these builds is typically sparse. In order to get a head start on testing things out, I often need to explore on my own. And so I wrote some scripts for that, which I've talked about in previous blog posts:
- How I spot not-yet-documented features in SQL Server CTPs
- More ways to discover changes in new versions of SQL Server
When I install a new version of SQL Server (be it a cumulative update, the final service pack for a major version, or the first CTP of vNext), there are two steps:
- Create a linked server to the build that came before it
- Create local synonyms referencing the important catalog views in the linked server
Assume I have just installed CTP 1 of vNext, and I want to compare that to an existing SQL Server 2017 instance:
USE [master]; -- on .\CTP1 DECLARE @previous sysname = N'.\SQL2017'; EXEC dbo.sp_addlinkedserver @server = @previous, @srvproduct = N'SQL Server'; EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname = @previous, @useself = N'True'; EXEC dbo.sp_serveroption @server = @previous, @optname = N'collation compatible', @optvalue = N'true'; EXEC dbo.sp_serveroption @server = @previous, @optname = N'data access', @optvalue = N'true';
Next I create the synonyms, such as:
CREATE SYNONYM dbo.OlderCTP_all_objects FOR [.\SQL2017].master.sys.all_objects; CREATE SYNONYM dbo.OlderCTP_all_columns FOR [.\SQL2017].master.sys.all_columns; CREATE SYNONYM dbo.OlderCTP_all_sql_modules FOR [.\SQL2017].master.sys.all_sql_modules; ...
I can then do all kinds of things to see what objects are new, what columns have been added, how catalog views have changed, and a whole slew of other things I explain in the previous posts.
There are tools that can be bent and massaged to help with this type of comparison, of course, and I do need to give DBA xPress a thorough try. But in the meantime, I like being able to fully control both the output and the join criteria.
Anyway, this has been a very useful method, and I'll let you guess why it came to mind this week. :-)