t-sql

T-SQL Tuesday #136: Your Favorite (or Least Favorite) Data Type

Andy Levy
T-SQL Tuesday is a monthly blog party hosted by a different community member each month, and this month Brent Ozar (blog | twitter) asks us to talk about data types. Your mission: write a blog post about your favorite data type, and schedule it for next Tuesday, March 9. Mad about money? Feverish about float? Tell us what you use your favorite data type for, and what people need to know before they get started using it.

A Non-Technical Reason to Not Use SELECT *

Andy Levy
I got a merge a while back that included a change I wasn’t expecting from one of my developers. All they were doing was renaming a column on a table. We had names settled a while ago. What’s up with that? Turns out, when I created the table, I named one of the fields BrithYear. This merge request corrected it to BirthYear. The misspelling slipped past me, past everyone else, it was only discovered because when this developer was building the a new query into their code, they spelled the field as one would expect, and the query threw an error.

Three Ways to Create a Temp Table

Andy Levy
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 one way to create them. The Traditional Way If you’re planning out your temp table usage, you’re probably creating them just like any other table, then populating them.

Modernizing Your T-SQL: Trimming Strings

Andy Levy
This 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 the T-SQL in your applications, I’m guessing not. This one seems pretty basic, but it’s got a trick up its sleeve - the TRIM() function.

Modernizing Your T-SQL: The Sequence Object

Andy Levy
This 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 the T-SQL in your applications, I’m guessing not. Let’s take a look at the SEQUENCE object, introduced with SQL Server 2012.

Modernizing Your T-SQL: String Aggregation

Andy Levy
This 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 of SQL Server 2017, and the STRING_AGG() function. Setup For this post, I’m using the set of North American telephone area codes sourced from the North American Numbering Plan Administrator.

Modernizing Your T-SQL: Splitting Strings

Andy Levy
This 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 of the T-SQL in your applications, I’m guessing not. At one time or another, we all find ourselves having to do some string parsing, especially splitting strings on a delimiter.

T-SQL Tuesday #104 - Code You Would Hate To Live Without

Andy Levy
This 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 reviled in some circles. More often it’s misunderstood and lamented, or…well, I’ll just show it to you.

Triggers vs. Default Constraints: Choose Wisely

Andy Levy
Triggers 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 a day, I’m alerted by our monitoring suite that two processes were deadlocked. This is a (mostly) normal event, but one that we’d like to avoid if we can.

Parens (Really) Matter for Unions

Andy Levy
In 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 table to the client, and most web browsers struggle when faced with several megabytes of markup and thousands of rows in a single table.