Tag Archives: t-sql

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.

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;

Building a Nums table (quickly)

Posted by timvw on July 13, 2011 None comments

A while ago i presented my approach to generate a nums table here. 

DECLARE @count INT = 1000;

WITH
    [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
  , [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
  , [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
  , [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
  , [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];

Because we only use this code once to fill the table we don’t really care that it is not very fast. Today i discovered there is a way to speed it up in this wonderful book: Inside Microsoft® SQL Server® 2008: T-SQL Querying:

WITH
    [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
  , [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
  , [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
  , [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
  , [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];

Yay for features like ORDER BY (SELECT <Constant>).

Using User-Defined Table Type with Identity column in ADO.NET

Posted by timvw on July 13, 2011 None comments

A while ago i wanted to use a User-Defined Table Type to pass in a set of records. Nothing special about this except that the first column of the UDTT was an Identity column:

CREATE TYPE [Star].[example] AS TABLE(
  [Ordinal] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
)

After finding a lot of posts saying that this is not supported a colleague of mine, Stephen Horsfield, found a way to do it as following:

var sqlMetaData = new[]
{
  new SqlMetaData("Ordinal", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),
  new SqlMetaData("Name", SqlDbType.NVarChar, 200)
};

sqlRecords = new HashSet<SqlDataRecord>(usersToInclude.Select(user =>
{
  var record = new SqlDataRecord(sqlMetaData);
  record.SetString(1, user.Name);
  return record;
}));

T-SQL: Remove all rows from all tables in your schema

Posted by timvw on September 7, 2010 None comments

Sometimes i want to quickly clean up a database and start from fresh. Here is a small script that does exactly that.. (I just run the script a couple of times, untill no affected rows remain… Far more efficient than figuring out which constraints exist, building up a dependency tree, and cleanly deleting all rows):

-- Remove all rows from all tables
DECLARE @tableName VARCHAR(255)
DECLARE tableNames CURSOR FOR SELECT name FROM sys.Tables;
OPEN tableNames
FETCH NEXT FROM tableNames INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
	EXEC('DELETE FROM [' +  @tableName + ']')
	FETCH NEXT FROM tableNames INTO @tableName
END
CLOSE tableNames
DEALLOCATE tableNames