SQL Tidbits

Sometimes we need to have optional parameters to a query. This is a very common scenario for searches, for instances. So how do you handle optional parameters? I’ve created a small script below where I query a table with two parameters, where I can use either one or two. By using ISNULL() I get a clean and nice way to set the parameters if necessary.

Please note query #1, where both parameters are null. Then all rows are returned.

CREATE TABLE #temp (n NVARCHAR(1), f NVARCHAR(1))

INSERT INTO #temp VALUES ('a', 'b')
INSERT INTO #temp VALUES ('a', 'c')
INSERT INTO #temp VALUES ('b', 'c')
INSERT INTO #temp VALUES ('e', 'd')

DECLARE @n AS NVARCHAR(1)
DECLARE @f AS NVARCHAR(1)

SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @n = 'a'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @f = 'c'
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

SET @n = NULL
SELECT * FROM #temp WHERE n = ISNULL(@n, n) AND f = ISNULL(@f, f)

DROP TABLE #temp   

Results:

n    f
---- ----
a    b
a    c
b    c
e    d

n    f
---- ----
a    b
a    c

n    f
---- ----
a    c

n    f
---- ----
a    c
b    c

When working on a project at a client, I some problems with a few functions in my code that wouldn’t work when we uploaded the code to our test environment. It turned out that the functions all contained transactions which were escalated to use the DTC (Distributed Transaction Coordinator) instead of plain old transactions. This made me quite confused, because all connections and transactions were against the same database, which should keep them out of the DTC. Anyways, to fix the problem in the short term, we started the DTC in the test environment and everything worked again. But I decided to take a closer look at the problem.

First, our setup. We wrote c# code in .Net 3.0, using a SQL Server 2005 Standard Edition database. Some actions in our business layer needed transactions, so we used TransactionScopes, developed just for this scenario. As we were using SQL Server 2005, which supports light-weight transactions (2000 does not), we shouldn’t ever need the DTC since we were using only one data source. But apparently, this wasn’t the case. So what was happening?

Here’s a snippet showing our situation:

using (TransactionScope ts = new TransactionScope())
{
    // First database connection, might happen in Business Logic -> Data Access Layer or anywhere
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    // Second database connection, might happen in Business Logic -> Data Access Layer or anywhere. This is escalated to DTC. But why?
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    ts.Complete();
}

When running this code in debug mode, reaching the second using-scope triggered an escalation to the DTC. But why? Apparently, we have two connections running at the same time, but that seems weird, since we’ve already left the using-scope so that connection should be closed in the dispose()-part of the using-scope.

It’s obvious when you think about it, really. When leaving the first using-scope, the connection should be closed. But that doesn’t happen, since we are in a transaction. The transaction scope needs to keep the connection open when we either commit or rollback. So when we reach the second using-scope, a second connection is opened and we are escalated to the DTC.

So, what can we do about this? There are at least two solutions:

• We overload the TransactionScope class with an implementation that re-uses the connection if a connection is already in use.
• Instead of calling new SqlConnection()... you can call a wrapper which checks if there already is one connection running and reuses that one.

If your are using SQL Server 2008 and Visual Studio 2008, this is implemented from the beginning and you don’t need any wrappers or overloads.

Recommended reading:

• http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3083270&SiteID=1
• http://blogs.msdn.com/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx
• http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

SP3 for SQL Server has been released! Microsoft has a list of what’s new and a list of the bug fixes. Go on and download SP3 now!

When declaring a variable or parameter as varchar or nvarchar, you might get into some nasty surprises if you don’t specify the number of characters in the data. If you are declaring a variable like DECLARE @string AS VARCHAR, you are going to end up with a string that only holds 1 character. That’s probably not what you intended, but rather DECLARE @string AS VARCHAR(50) or similar.

The behaviour is similar when using CAST and CONVERT. Then, the default is to limit the data to 30 characters.

Some examples of this:

DECLARE @string AS VARCHAR
SET @string = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN('Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [varchar],
CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR) AS [Casted varchar],
LEN(CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR)) AS [Length of casted varchar]
GO

DECLARE @string AS NVARCHAR
SET @string = N'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [nvarchar],
CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR) AS [Casted nvarchar],
LEN(CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR)) AS [Length of casted nvarchar]

Results:

Original string                                          Length of original string varchar Casted varchar                 Length of casted varchar
-------------------------------------------------------- ------------------------- ------- ------------------------------ ------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L       Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)

Original string                                          Length of original string nvarchar Casted nvarchar                Length of casted nvarchar
-------------------------------------------------------- ------------------------- -------- ------------------------------ -------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L        Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)