SQL 2005 T-SQL programming - possible pitfalls to be aware of

Question

You might be aware of the Halloween problem in which a an SQL cursor can be a nightmare for you if not properly used.

What are the other subtle problems (difficult to debug and detect) that might be introduced if one does not carefully make use of SQL 2005 features?

Answer 1

First of all cursors should not be used unless there absolutely is no other way to do it. Almost everything can be done set based. A numbers table will help with that. Check XACT_STATE for doomed transactions. there are many more things to be aware of

Read here about some of SQL Server Programming Pitfalls

Answer 2

Trying to treat SQL like a programming environment is probably the biggest mistake. SQL is all about declaring what you want. If you ever are using cursors or looping constructs, ask yourself if there was a way you could transform it into an INSERT/UPDATE

Answer 3

1. Doing NOT IN on a column which can have NULLS does not give result.
2. Doing count(cloumn) on a column which can have nulls would only give you count of non null values.

Answer 4

There are a number of issues that come to mind:

• NULL handling - a lot of folks have trouble understanding that NULL is a very different beast than "0" (zero), and that you CANNOT usually compare IF myCol = NULL - you need to use "IS NULL" or "IS NOT NULL" and so forth.

NULL has its place - when it really means the absence of a choice or value - but in many cases, you're probably better off defining a default value (on an INT column often "0") that will represent "nothing" or "no choice has been made" etc.

• Indexing - a lot of confusion around indexing and what the pros and cons, benefits and drawbacks are. There's no point in indexing every column - and even if you've identified a column to be used often in a SELECT ... WHERE clause, if the selectivity of the column is not sufficiently small (some experts say if a single value of a column doesn't select out 1% or less of your data, don't bother indexing), often it won't really help since a full-table scan (index scan) will be cheaper.

And also, with indexing - if you use T-SQL functions in your WHERE clauses, like "UPPER(myCol) = (value)" or "LEFT(myCol, 10) = (value)", then you will not see the intended benefit from an index, either.

Indexing can be a huge boost to your performance - but index wisely!

Cheers, Marc