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
Posts
