Obvious SQL Tip #2

Thinking in sets:
The thought process that goes into set- based programming is slightly different from writing procedural code.

Say, I have 2 tables -one holding order header information and the other holding the details of the products ordered. I want to get the items ordered for all the orders:

• The procedural way of looking at it is "for each order in the orders table get the order details from the [order details] table".
• In DB terms, its "for all orders in the orders table get the order details from the [order details] table".

Its the subtleties that makes the difference.

Tip: When it comes to database programming, you no longer look at each row. You should understand that a table is a collection of rows with similar characterstics. Or its more like, "you seen one row.. you've seen 'em all".

Say, for example, you need only the orders placed in US and not the rest of the world.

• In procedural way "for each order check if the order is placed in US. If so, then get the corresponding order details else ignore the order".
• In sets "Get the order details for all the orders placed in US"

Thinking the procedural way will only lead to one conclusion for any requirement- CURSORS. Stop thinking the procedural way, if not possible, stop thinking :)

Tip: With your procedural code, you build your result set from scratch one row at a time. Don't do that. Take all the rows and eliminate whichever is not necessary.

Here is an analogy: Say you have a bag with 100 coins. Of that, 10 coins are silver and 90 are gold. You want to take the 10 silver coins out as fast as you can. How will you do it? If you think you will take one coin out at a time and check whether its silver or gold, then database programming is not the best option for you.

Get the procedural code out of your database:

• CURSORs with IF.. ELSE usually can be replaced with CASE statements in SELECT.
• Scalar user defined functions (S-UDFs) in SQL Server are the bridge between procedural code and T-SQL code. Or, to be precise, its just procedural code in T-SQL. Avoid it if possible.
• In a S-UDFs it might be tempting to use recursion for scenarios like converting integer to binary or getting the factorial. It might be an excellent code, but while loop performs better than recursion, CLR functions (SQL Server 2005) performs better than while loop, doing the calculation in the front end is definitely better than using a CLR function.