I gathered the 4 most common bugs I find DBA are using in their code. Feel free to send me a list of your common bugs And of course don’t forget to check your code doesn’t contain any of the following:
NULL bugs are not easy to escape when you start writing T-SQL Code.
Look at the following code:
DECLARE @MyString NVARCHAR(100) SET @MyString = @MyString + N' This is very important' PRINT @MyString
Why doesn’t it print anything?
This one was easy, you immediately see there’s a problem. But when you join rows according to columns that contain NULLs you might even not know there’s a problem. In this code we create a table and look for rows that appear only once.
CREATE TABLE A ( ID INT IDENTITY NOT NULL, Name NVARCHAR(100) NOT NULL, Phone NVARCHAR(100) NOT NULL, Fax NVARCHAR(100) NULL ) INSERT INTO A (Name, Phone, Fax) VALUES ('Moshe', '00000', NULL), ('Moshe', '00000', NULL) SELECT * FROM A a1 LEFT JOIN A a2 ON (a1.ID <> a2.ID AND a1.Name = a2.Name AND a1.Phone = a2.Phone AND a1.Fax = a2.Fax) WHERE a2.ID IS NULL
Why does the query return two rows that are the same?? It should have returned rows that appear only once. You know the answer: it’s because NULL never equals to anything, not even to itself! To solve this problem you need to change the query, for example:
SELECT * FROM A a1 LEFT JOIN A a2 ON (a1.ID <> a2.ID AND a1.Name = a2.Name AND a1.Phone = a2.Phone AND (a1.Fax = a2.Fax OR (a1.Fax IS NULL AND a2.Fax IS NULL))) WHERE a2.ID IS NULL
2. ROLLBACK in CATCH
There are actually two common bugs related to ROLLBACK in CATCH. The first is very important, if you open an explicit transaction (BEGIN TRANSACTION) don’t forget, but really don’t forget to write ROLLBACK in the CATCH clause!
But that’s not enough. Look at the following scenario:
CREATE PROCEDURE SP1 AS BEGIN TRY BEGIN TRAN SELECT 1/0 COMMIT END TRY BEGIN CATCH ROLLBACK END CATCH GO CREATE PROCEDURE SP2 AS BEGIN TRY BEGIN TRAN EXEC SP1 COMMIT END TRY BEGIN CATCH ROLLBACK END CATCH GO EXEC SP2
Although both Stored Procedures contain a ROLLBACK in the CATCH clause, we get an error!
The reason is that ROLLBACK causes all the nested transactions to rollback and therefore the ROLLBACK isn’t necessary anymore and raises an exception. The solution is to write before each ROLLBACK:
IF (@@TRANCOUNT > 0) ROLLBACK
3. Simultaneous SELECTs
Think of a very simple INSERT that copies rows from Table B into A, but only rows that don’t already exist in A:
INSERT INTO A ( Name, Phone, Fax ) SELECT B.Name, B.Phone, B.Fax FROM B LEFT JOIN A ON (A.ID = B.ID) WHERE A.ID IS NULL
This code in itself is correct, but if it runs simultaneously through different sessions, it might enter the same row twice. The reason is the SELECT doesn’t prevent another SELECT from happening at the same time and hence both SELECTs will insert the row. To prevent this bug, use the following query hint:
INSERT INTO A ( Name, Phone, Fax ) SELECT B.Name, B.Phone, B.Fax FROM B WITH (UPDLOCK, HOLDLOCK) LEFT JOIN A ON (A.ID = B.ID) WHERE A.ID IS NULL
Now, the SELECT can’t happen simultaneously!
4. Use of UNION instead of UNION ALL.
You probably think this is really dumb, and you’re right! But still, I see it so often. Don’t forget they are not the same! Use UNION only if you intend to remove duplicate rows, otherwise use UNION ALL!!
Have a great bugless week..