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:

1. NULL

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..

Image by Skunkworks Photographic, “Another Bug” CC BY-NC-SA 2.0

The post SELECT Top 4 Bugs FROM DBAs IN SQL Server appeared first on Madeira Data Solutions.