5 SQL Tricks for making code disappear

Ask your CIO – the cost of maintaining a $1M project over the life time of a project is about $7M. This means that the less code you write, the less expensive it is to maintain your application Here are 5 quick tips for reducing the amount of code you have to write.



1) Try/Catch logic blocks

SQL Server 2005 introduced the “Try/Catch” code block. Instead of checking the value of @@error after every data modification statement, you can put all of your dml in a “try” block, and handle any exceptions in a “Catch” block. Note: This is NOT transactional, you’ll still need to manage transactions externally

2) Common Table Expressions (CTE)

We’ve seen performance both ways: CTEs can both eliminate performance issues or create them. That said, when properly implemented, we’ve also used CTEs to avoid some very extensive and complicated recursion logic.

3) File Stream

For those of you who are taking .doc, .pdf, .jpg, etc., apart & putting them into varchar(max) or varbinary(max) columns are not only working too hard, you’re using up too much storage. Databases don’t store that information efficiently. Instead, use the new (with SQL 2008) Filestream data type, store it in the database as itself.

4) Merge

a. Data warehousing (and keeping cubes, etc. up to date) has caused us to need to write a lot of “take the rows form this table that have been updated, update the corresponding rows from over there… unless there isn’t a row over there, in which case add a row” logic. Hey, we’re programmers, that’s what we do. The catch is, you don’t’ have to work that hard anymore. The new “Merge” statement even gives you error-processing.

5) Hierarchy data type

We’ve personally written a lot of code to define and manage hierarchy structures. We have one client in particular who might be able to eliminate as much as 75% of their code (all they do is manage hierarchies) by using the new hierarchy data type.