Earlier this year, I embarked on a bit of a project to tidy up the indexes in a sizeable database. This database has over 900 tables, and there are quite a few indexes which I have long suspected don’t need to exist for a variety of reasons. Some indexes are redundant, others don’t get used, still others could be combined into a single index. By reducing the number of indexes, we can improve write performance in the database, and the size of the database itself.
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.
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.
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.
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.
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.
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.
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.
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 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.