What you didn’t know about GO

The GO keyword isn’t strictly speaking T-SQL – it’s really just a batch terminator used in Management Studio and other tools. But did you know that you can use GO to run a batch multiple times?

Turns out, GO has an optional numeric argument that specifies how many times you want to repeat the batch.

SELECT GETDATE() AS currentDateTime;
GO 5

With a little bit of imagination, you could for instance use this to replace dreary old WHILE loops, to populate test values into tables or run a stored procedure multiple times to test performance.

Shrinking tempdb without restarting SQL Server

Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.

Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.

Cool MERGE features you may not know about

The MERGE statement is a really powerful way to create what’s called “upserts”. In this article, I’ll take a more detailed look at how you can make the best use of MERGE and I’ll also show you some cool tricks I picked up along the way.

Using NULLIF() to avoid division by zero

When you perform divisions, you need to make sure that the divisor, the number to the right of the division operator, isn’t zero. If it is, you will end up with a division-by-zero error message:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Dividing by NULL, however, doesn’t. So to avoid division-by-zero errors, simply use NULLIF() to replace zeros in the divisor with a NULL value.

A function to calculate recurring dates

When you’re using Microsoft Outlook, or pretty much any other personal information manager, you can create calendar appointments that are “recurring”, i.e. you can have them repeat at a defined frequency. This, however may not only apply to your project meeting appointments, but also to some database solution. I decided to give it a go at building a table value function that returns a list of dates, based on a given set of parameters.

Weeks and weekday parts in dates

Here are two common pitfalls that you may encounter when it comes to working with dates, specifically with week numbers and weekdays. The first is to mistake week numbers in SQL Server for ISO week numbers (which are the weeknumbers used in Sweden, for instance). The other is to make assumptions about which day of the week is the first. The good news is that these are relatively easy fixes.