This is in continuation to yesterday’s post on T-SQL worst practices that affect the performance of the results returned/processed.

What have I done? T-SQL Worst Practices

I am attempting to summarize all of the worst T-SQL practices that Jacob demonstrated at Tech-Ed 2011 (India). The summarization has been done after classifying these practices into 3 major buckets:

1. Practices that can affect accuracy of data returned/processed (Part 01 – read here)
2. Practices that can adversely impact performance (Part 02 – read here)
3. Practices that can have unpredictable execution side-effects (Part 03 – today’s post)

While the points covered are exactly the same as covered during the Tech-Ed, I have taken hints from Jacob’s queries and have come up with my own versions using the AdventureWorks2008R2 database supplied as a sample database for SQL 11 (“Denali”) CTP01. In cases where I am directly using Jacob’s scripts, I have mentioned it as such.

Part 03 – Practices that contribute to execution errors

The previous two sessions focused on the efficiency and accuracy of the application. However, errors (logical or otherwise) during execution are a nightmare for technical support and any team assigned the task of maintaining the product after it’s release. Even worse is when these execution errors in the code are sporadic. Today, in the final part of this series, we will look at some of the coding practices which might result in an unstable product.

Using Ordinal positions in the ORDER BY clause

Manager: I need a list of the first and last initials of all contacts in our database. Can you do that?

You: Sure.That’s quite easy. Here you go:

USE [AdventureWorks2008R2]
GO

SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
       LEFT(p.LastName, 1) AS LastInitial
FROM Person.Person p
GO

Manager: But, I want it sorted by the First Initial.

You: No issues. It’s just a small change.

USE [AdventureWorks2008R2]
GO

SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
       LEFT(p.LastName, 1) AS LastInitial
FROM Person.Person p
ORDER BY 1
GO

Manager: That was fast! But, there might be situations where I need to sort it by the Last Name.

You: Okay, it looks like we will need to go beyond simple ad-hoc queries. I will create a stored procedure for you.

USE [AdventureWorks2008R2]
GO

CREATE PROCEDURE GetContacts
(
   @Sort VARCHAR(20)
)
AS
BEGIN
    SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
           LEFT(p.LastName, 1) AS LastInitial
    FROM Person.Person p
    ORDER BY CASE @Sort WHEN 'first' THEN 1
                        ELSE 2
                        END
END
GO

Manager (in the morning the very next day): The stored procedure you gave me yesterday does not work. Fix it and see that you always test your code before submitting to me.

You: How is this possible? Today is going to be a bad day. I missed the bus, and now my manager is angry at me. I must do something quickly……Let me try this:

SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
       LEFT(p.LastName, 1) AS LastInitial
FROM Person.Person p
ORDER BY FirstInitial
GO

You: Okay. This works, so let me put it inside the stored procedure to fix the issue that my manager is encountering.

ALTER PROCEDURE GetContacts
(
    @Sort VARCHAR(20)
)
AS
BEGIN
    SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
           LEFT(p.LastName, 1) AS LastInitial
    FROM Person.Person p
    ORDER BY CASE @Sort WHEN 'first' THEN FirstInitial
                        ELSE LastInitial
                        END
END
GO

When you execute the ALTER statement to begin your unit test:

Msg 207, Level 16, State 1, Procedure GetContacts, Line 10
Invalid column name ‘FirstInitial’.
Msg 207, Level 16, State 1, Procedure GetContacts, Line 11
Invalid column name ‘LastInitial’.

You (dumb-struck): ?? What is going on? Nothing is working as expected…

Actually, it’s not your fault entirely. It’s the way expressions are evaluated when used in the ORDER BY clause. When used in the ORDER BY clause, expressions need to be computed before-hand. In your case, you are using a column alias, which does not exist yet and hence the error. Here’s what you can do to resolve the ugly situation:

ALTER PROCEDURE GetContacts
(
	@Sort VARCHAR(20)
)
AS
BEGIN
    SELECT LEFT(p.FirstName, 1) AS FirstInitial, 
           LEFT(p.LastName, 1) AS LastInitial
    FROM Person.Person p
	ORDER BY CASE @Sort WHEN 'first' THEN LEFT(p.FirstName, 1)
                        ELSE LEFT(p.LastName, 1)
                        END
END
GO

Here’s the result – it works!

Maintaining Integrity during transactions

At the end of the day, anything and everything within a database is all about maintaining data integrity. Microsoft SQL Server ensures this and also provides all the basic components to the development community so that they can ensure that errors during code execution are handled properly and roll-backs issued so that anything that is inconsistent is not committed to the database.

However, we as developers tend to ignore a couple of things, or take things for granted – one of them is the fact that Microsoft SQL Server leaves the user to decide whether or not it should automatically abort a transaction in the case of an error.

Let’s set the stage by assuming that we have a table called Customers and have some basic data in it.

/*-----------------------------------------------------------------------------
  Date			: 23 March 2011
  SQL Version           : SQL Server 2005/2008/Denali
  Author		: Jacob Sebastian
  Email			: jacob@beyondrelational.com
  Twitter		: @jacobsebastian
  Blog			: http://beyondrelational.com/blogs/jacob
  Website		: http://beyondrelational.com

  Summary:
  This script is part of the demo presented by Jacob Sebastian during 
  Tech-ED India 2011 on "TSQL Worst Practices".

  Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
-----------------------------------------------------------------------------*/
USE TechEd; 
GO
IF OBJECT_ID('UpdateCustomer') IS NOT NULL DROP PROCEDURE UpdateCustomer; 
GO

IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
	DROP TABLE Customers 
END
GO

CREATE TABLE Customers (
	CustID INT IDENTITY,
	CustName VARCHAR(100),
	CustCity VARCHAR(100),
	LastOrder DATETIME
)
GO

INSERT INTO Customers (CustName, CustCity)
SELECT 'Jacob', 'Ahmedabad' UNION ALL
SELECT 'Pinal', 'Bangalore'

Now, let’s create a stored procedure to update the Customers table.

USE TechEd; 
GO

CREATE PROCEDURE UpdateCustomer(
	@CustID INT,
	@SalesAmount MONEY
)
AS

BEGIN TRY
	BEGIN TRAN

	UPDATE Customers SET LastOrder = GETDATE() WHERE CustID = @CustID 
	
	UPDATE customerss SET
		SalesAmount = SalesAmount + @SalesAmount 
	WHERE CustID = @CustID 

	COMMIT TRAN

END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 
		ROLLBACK TRAN
	PRINT 'Error Number: ' + CAST (ERROR_NUMBER() AS VARCHAR)
END CATCH
GO

Note that although we have an incorrect table name in the stored procedure, it was successfully created. (Do not go and correct it – this incorrect table name is intentional).

Now, in a SQL Server Management Studio window, run the following query. You will encounter a fatal error mentioned below – fatal enough for SQL Server to stop execution then and there. This is expected and not an accident.

EXEC UpdateCustomer 1, 100

(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure UpdateCustomer, Line 13
Invalid object name ‘customerss’.
Msg 266, Level 16, State 2, Procedure UpdateCustomer, Line 13
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Then, run the below query in another SQL Server Management Studio query window.

SELECT * FROM customers 

You will notice that the execution continues without producing any results for a long time.

So, what’s the problem?

The problem here is that although the execution of the stored procedure encountered an error, the transaction that we had started was never aborted or rolled-back. As a result, the Customers table remained under lock, causing the SELECT to wait indefinitely. As proof, you may run the following on query window #1:

SELECT @@trancount
ROLLBACK TRAN

You will see that we have one open transaction. Issuing the rollback will cause query window #2 to return results.

What’s the worst that can happen? Well, that depends on your definition of “worst”. According to me, you are already in big trouble – your code just broke. However, if that is not serious enough, your table is now under lock until and unless and external force (i.e. manual ROLLBACK) is applied (almost like Newton’s laws, isn’t it?). This can result in your entire application becoming unusable and ultimately a “site-down” (or P1) support call with the customer (and your manager) screaming on you.

Solution #01: Create a wrapper stored procedure

One of the solutions is to simulate what we just did, i.e. create a wrapper stored procedure, which would be able to:

• detect that the underlying procedure call failed
• check if there are any open transactions
• If yes, rollback all open transactions

We can implement this as under:

USE TechEd; 
GO

BEGIN TRY
	EXEC UpdateCustomer 1, 100
END TRY BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK TRAN
	PRINT 'Transaction Rolled Back'
END CATCH
GO

Try it out. It works, but there is a problem. In an enterprise class system, we may have hundreds of stored procedures – and it is simply not practical to have wrappers on each.

Solution #02: SET XACT_ABORT ON

This solution is actually, quite simple (and what I would recommend). All we need to tell Microsoft SQL Server is to automatically issue a ROLLBACK if the transaction encounters a fatal error.

All that one needs to do is to use a SET option called XACT_ABORT and set it to ON. (BOL link here)

USE TechEd; 
GO

ALTER PROCEDURE UpdateCustomer(
	@CustID INT,
	@SalesAmount MONEY
)
AS
SET XACT_ABORT ON
BEGIN TRY
	BEGIN TRAN

	UPDATE Customers SET LastOrder = GETDATE() WHERE CustID = @CustID 
	
	UPDATE customerss SET
		SalesAmount = SalesAmount + @SalesAmount 
	WHERE CustID = @CustID 

	COMMIT TRAN

END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 
		ROLLBACK TRAN
	PRINT 'Error Number: ' + CAST (ERROR_NUMBER() AS VARCHAR)
END CATCH
GO

Now, repeat the exercise of executing the following in two separate query editor windows. Notice that this time, we do not encounter locking and the second query succeeds.

EXEC UpdateCustomer 1, 100

-- run in another session
SELECT * FROM customers 

Summarizing – Part 03:

Finally, summarizing all the points that have been discussed today (Part 03) and during Tech-Ed 2011

1. Always test your code before it leaves your table! Although things “should” work, they don’t do so just that one time when you don’t test
2. If an ordinal position is specified in an expression, it may not produce the expected results
3. If an expression is used in ORDER BY, it will be evaluated when FILTERS are evaluated
4. Always use SET XACT_ABORT ON in stored procedures to rollback open transactions in case of unhandled errors

Thank-you, Jacob, for this really great session!

You can read my memories of Tech-Ed by going through the following series:

Tech Ed 2011 (India) – Day 01 – Connect, Learn, Explore, Evolve!

Tech Ed 2011 (India) – Day 02 – IE 9 – India launch – Fast is now beautiful!

Tech Ed 2011 (India) – Day 03 – For the DBAs – Contained Databases, Extended Events, Virtualization, Wait Stats, Wait Queues & High Availability

Until we meet next time,

Be courteous. Drive responsibly.

Want to ask me a question? Do so at: http://beyondrelational.com/ask/nakul/default.aspx