Taking it back to SQL 101 today because I recently saw something that floored me. I’m a big fan of temp tables. I use ’em all over the place to stash intermediate results, break a dataset down into more manageable chunks, or even share them between procedures. And as it turns out, there’s more than…
Read More Three Ways to Create a Temp TableThis is one of several posts on modernizing T-SQL code with new features and functionality available in SQL Server. Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all of…
Read More Modernizing Your T-SQL: Trimming StringsThis is one of several posts on modernizing T-SQL code with new features and functionality available in SQL Server. Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all of…
Read More Modernizing Your T-SQL: The Sequence ObjectThis is another in a group of several posts on modernizing T-SQL code with new features and functionality available in SQL Server. SQL Server 2016 gave us the STRING_SPLIT() function, but what about the reverse – compiling a set of values into one delimited string? We only had to wait 15 months for the release…
Read More Modernizing Your T-SQL: String AggregationThis is the first of several posts on modernizing T-SQL code with new features and functionality available in SQL Server. Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all…
Read More Modernizing Your T-SQL: Splitting StringsThis month’s T-SQL Tuesday is hosted by Bert Wagner and he asks us to write about code we’ve written that we would hate to live without. First off, “hate” is a pretty strong word so let’s go with “code you couldn’t bear to live without”. The first bit of code I couldn’t live without is…
Read More T-SQL Tuesday #104 – Code You Would Hate To Live WithoutTriggers can be really useful in your database but you have to be careful with them. We often use them to record history to a separate table (at least, until we can implement temporal tables) or keep track of when a record was last updated. It’s usually implemented via an AFTER UPDATE trigger. Several times…
Read More Triggers vs. Default Constraints: Choose WiselyIn the course of testing a major upgrade, one of my users in Accounting happened upon a problem with one of her scenarios. The web app we were working on had a habit of “locking up” on people when loading some pages; in most cases, it was because the server was pushing a huge HTML…
Read More Parens (Really) Matter for UnionsFor years I’ve looked at various queries online in sample code, diagnostic queries using DMVs, and the like and seen CROSS APPLY in the FROM clauses. But I’ve never really managed to comprehend what it was for or how it worked because I never saw a case where it was directly applied to something I…
Read More I Finally Get Cross Apply!This post is in support of Tim Ford’s (b|t) #iwanttohelp challenge. And also written because this has burned me twice in the past 3 months and by blogging about it, hopefully it’ll stick in my mind. Setup I’ve recently been doing a bunch of work with stored procedures, trying to improve performance that’s been suffering due…
Read More Don’t Count on Me