Basics – Page 6 – sqlsunday.com

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.

One of the most basic concepts to master in T-SQL (or any dialect of SQL, for that matter) is understanding how the JOIN operator works. This post goes through the fundamentals of joining, and I highly recommend it if you’re just starting out with T-SQL.

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.

Catalog views: Database objects

Catalog views are system views that expose most facets of the server and its databases in a tabular form. In this tutorial, we’ll take a closer look at database objects and how they’re represented in a database’s catalog views.

Top 10 T-SQL worst practices

Bomb As part of an improvement process (any process, not just database development), you can start by asking yourself “How could I make this process as bad as possible”. In fact, when you think of how to royally screw something up, all you have to do is flip the answer around, and there you have a good starting point for improving your process, work, whatever.

Here are a few T-SQL “worst practices” to get you started on improving your database design and development practices. I’ve personally seen all of these in the field, and I think you might recognize some of them, too.

A short introduction to application roles

Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.

Date formats and conversions

Have you noticed how date conversions can seem a bit arbitrary at times? How a string value is translated to a date depends on a number of factors, including how you perform the conversion and what language settings you have set for your connection. But there are ways to limit your conversion headaches.

Category: Basics

What you didn’t know about GO

Beginner’s guide to JOIN

Using NULLIF() to avoid division by zero

Catalog views: Database objects

Top 10 T-SQL worst practices

Date formats and conversions

A short post on SQL injection.

A prime number challenge.

Different kinds of temp tables