Monthly Archives: August 2011

Use SQL Server Profiler to see if a connection is pooled

Posted by timvw on August 19, 2011 None comments

It took me a couple of websearches to discover how i can see in SQL Server Profiler whether or not a connection is pooled. Apparently you have to check ‘Show all columns’ and then you can check the ‘EventSubClass’ column:

This is how it looks like in your trace window:

SqlConnectionStringBuilder sets the Pooling property to true by default

Posted by timvw on August 18, 2011 2 comments

Here is something that surprised me: SqlConnectionStringBuilder sets the Pooling property to true by default.

TryGetResult

Posted by timvw on August 1, 2011 None comments

I think this entry has been in the pipeline for a couple of years now and today i have decided to finally post it ;) I got frustrated with the annoying out parameter in TryGet methods so i decided to use a different signature using TryGetResult:

public class TryGetResult<T>
{
 public TryGetResult()
 {
  Success = false;
 }

 public TryGetResult(T result)
 {
  Success = true;
  Result = result;
 }

 public bool Success { get; private set; }
 public T Result { get; private set; }
}

And now your TryGet methods can have the following signature:

public TryGetResult<Person> TryGetPersonByName(string name)
{
 // person is not available
 if(name.IsInvalidPersonName()) return new TryGetResult(); 
 // return the person
 return new TryGetResult(new Person(name)); 
}

Specialized solution for aggregate string concatenation

Posted by timvw on August 1, 2011 None comments

I have noticed that most people come up with the following solution to build a string in T-SQL:

WITH [Numbers] AS (
	SELECT TOP(10) [n] 
	FROM [Nums]
)
	SELECT @message = COALESCE(@message, '') + ', ' + CAST([n] AS nvarchar(2))
	FROM [Numbers];

SELECT @message = STUFF(@message, 1, 2, '');	
SELECT @message;

Important! Microsoft has no official documentation describing this aggregate concatenation
technique that is based on the assignment SELECT syntax. The behavior described here is
based on observation alone. The current implementation of the ConcatOrders function doesn’t
incorporate
an ORDER BY clause and does not guarantee the order of concatenation. According
to a blog entry by Microsoft’s Conor Cunningham, it seems that SQL Server will respect an
ORDER BY clause if specified (http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx).
Conor is a very credible source, but I should stress that besides
this blog entry I haven’t found
any official documentation describing how a multi-row assignment
SELECT should behave—with
or without an ORDER BY clause.

With the aid of FOR XML PATH (as mentionned in Inside Microsoft SQL Server 2008: T-SQL Programming we can solve this problem using a documented approach:

WITH [Numbers] AS (
	SELECT TOP(10) [n] 
	FROM [Nums]
)
	SELECT @message = (SELECT ', ' + CAST([n] AS nvarchar(2)) AS 1[/text]
	FROM [Numbers]
	FOR XML PATH(''));

SELECT @message = STUFF(@message, 1, 2, '');
SELECT @message;