T-SQL Tuesday #20 – T-SQL Best Practices

Here we go again! It’s T-SQL Tuesday #20 – a monthly blog party, this time hosted by Amit Banerjee. (Thanks Amit!) This month, we’re talking about “T-SQL Best Practices”.

I used to write a lot more T-SQL than I do now. I had lots of rules for how T-SQL was written in my shop. But rather than sharing that extensive document, today I want to share with you my top three pieces of T-SQL advice.

KISS (Keep It Simple, Developer)

There are a lot of ways to solve problems in T-SQL. Once you get beyond a simple SELECT or UPDATE, you can really start playing. Derived tables, Common Table Expressions, CASE statements, aggregation, cursors, window functions, functions, pivoting…the things you can play with are endless. One of the beautiful things about T-SQL is that there is almost always more than one way to solve a problem.

Remember, though, to keep it simple. Someday, someone else will need to read that code, and interpret it, and possibly (gasp) edit it. It could even be you.

Table Aliases

No, you don’t need to ask people in the office to start calling you by your Twitter handle (no matter how funny that would be). SQL Server includes a “correlation name” or “range variable” to make FROM statements more easily readable. It’s also known as a “table alias”.

A table alias is easy to implement, and saves trouble in the long run. How? Let’s look at this simple query:

    SELECT Customer.CustomerID, SalesOrderID, OrderDate, TotalValue
    FROM Customer
    INNER JOIN SalesOrder ON SalesOrder.CustomerID = Customer.CustomerID

It’s a pain to reference a field with the full table name. And while this query is readable, what table does “OrderDate” reside in? Can you easily tell?

Try this version instead:

    SELECT CUST.CustomerID, SO.SalesOrderID, SO.OrderDate, SO.TotalValue
    FROM Customer CUST
    INNER JOIN SalesOrder SO ON SO.CustomerID = CUST.CustomerID

Read more: Less than dot
QR:

Posted via email from Jasper-Net