CE's Blog

Perhaps you have come across this paragraph before:

Acdrocing to a rcersaheer at Cdmiabrge Uterniisvy, it dens’ot mttear in waht odrer the letters in a word are, the only inapmtrot tinhg is that the frist and last lteetr be at the rghit plcae. The rset can be a ttoal mess and you can siltl raed it wtihuot plreobm. Tihs is baesuce the hmuan mind does not raed ervey lteetr by ilestf but the wrod as a wlohe.

When I saw this, I thought about how to write such a randomizer in T-SQL without using loops and variables:

WITH CTE1 AS(
	SELECT 'According to a researcher at Cambridge University, it doesn''t matter in what order the letters in a word are, the only important thing is that the first and last letter be at the right place. The rest can be a total mess and you can still read it without problem. This is because the human mind does not read every letter by itself but the word as a whole.' AS A
	UNION ALL
	SELECT 'Manche Leute sagen es spielt keine Rolle, in welcher Reihenfolge die Buchstaben in einem Wort angeordnet sind, solange der erste und der letzte Buchstabe nicht verändert wird.'
), CTE2 AS (
	SELECT ROW_NUMBER() OVER( ORDER BY LEFT( A, 0 ) ) AS ID1, * FROM CTE1
), CTE3 AS (
	SELECT ID1, 1 AS ID2, SUBSTRING( A, 1, 1 ) AS C, A FROM CTE2
	UNION ALL 
	SELECT ID1, ID2 + 1, SUBSTRING( A, ID2 + 1, 1 ), A FROM CTE3 WHERE ID2 < LEN( A )
), CTE4 AS (
	SELECT A.ID1, A.ID2, A.A, A.C, CASE WHEN B.C IS NOT NULL AND C.C IS NOT NULL THEN 0 ELSE 1 END AS ID3 FROM CTE3 AS A
		LEFT JOIN CTE3 AS B ON A.ID1 = B.ID1 AND A.ID2 - 1 = B.ID2 AND B.C NOT IN ( ' ', '.', ',', '[', '(', ']', ')' )
		LEFT JOIN CTE3 AS C ON A.ID1 = C.ID1 AND A.ID2 + 1 = C.ID2 AND C.C NOT IN ( ' ', '.', ',', '[', '(', ']', ')' )
), CTE5 AS (
	SELECT A.ID1, A.ID2, A.A, A.C, CASE A.ID3 WHEN 0 THEN ( SELECT MAX( B.ID2 ) + 1 FROM CTE4 AS B WHERE A.ID1 = B.ID1 AND B.ID2 < A.ID2 AND B.ID3 = 1 ) ELSE ID2 END AS ID3 FROM CTE4 AS A
), CTE6 AS (
	SELECT CTE2.ID1, ( SELECT CTE5.C + '' FROM CTE5 WHERE CTE5.ID1 = CTE2.ID1 ORDER BY CTE5.ID3, NEWID() FOR XML PATH(''), TYPE ).VALUE( 'text()[1]', 'NVARCHAR(MAX)' ) AS A
		FROM CTE2
)
SELECT * FROM CTE6 ORDER BY ID1 ASC OPTION( MAXRECURSION 32767 )

Here is a German example:

Mahnce Letue seagn es speilt kenie Rolle, in wcelher Renfhioglee die Bbetschuan in eniem Wort aegdnonret snid, solnage der erste und der ltteze Bsbctauhe nihct vedrreänt wrid.

Ok, this is not terribly efficient, but it proves that recursive CTEs have their right of existence at last ;-)

Returning XML data over a Linked Server connection in SQL Server 2005/2008 is not supported in general. However there are several workarounds for this issue which I would like to discuss in this post.

Recently we have encountered a major issue within an application that was supposed to retrieve XML data through a linked server. When the application was set up to connect and retrieve XML data directly from the SQL Server (2008 R2), the result was as expected. However when we executed the same stored procedure over a linked server connection, we were only able to retrieve binary data from the same call instead of proper XML.

Nothing wrong with the linked server though, since the standard “SQL Server” provider was used between both instances of SQL Server 2008 R2.
To reproduce the issue, it is not even necessary to have a second SQL Server instance:

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'

This is all it takes to set up a new linked server which points at the local SQL Server instance. The result of the following two queries is very diffferent:

EXEC           master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'
EXEC LOCALHOST.master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'

We would expect that both would return a single XML resultset in SQL Server Management Studio. Here is the result:

Result of the second query:

0x44014100440372006F0077000102020142FF00000043

Obviously the data returned from the second query is in binary format, always starting with 0×44. However it is not just an XML or NVARCHAR data type which was cast to XML, so any attempts to recover the string representation of the XML file by means of CAST or CONVERT will fail. It turns out that the linked server is not able to decode the SQL Server binary serialized XML format which is used for passing XML data over the TDS protocol to clients. Instead, this data is interpreted as VARBINARY and therefore fails to convert into text or xml on the client side.

Interestingly, the issue does not exist if we are using a very old SQL Server provider which is using ODBC over OLEDB in 32 bit mode. It is really not recommended to use this provider for setting up a linked server in a 64-bit production environment, so I won’t go into details here on how to configure it.

If it is an option to change the code which is executed over a linked server connection, the easiest solution is to return XML data as NVARCHAR instead of in native XML format.

SELECT 255 AS A FOR XML RAW

So the above query can be rewritten to a more or less equivalent:

WITH CTE( X ) AS ( SELECT 255 AS A FOR XML RAW ) SELECT CONVERT( NVARCHAR(MAX), X ) AS X FROM CTE

Result:

Microsoft SQL Server product support has confirmed the above limitation of the linked server provider and suggested a reengineering of the system to get rid of linked server connections in general, which may not be an option for everyone running into this issue. It was also confirmed that it is not possible to convert the binary serialized XML back into textual representation by means of .NET functionality within the Microsoft.SqlServer namespace.

Not all is lost though, since there is a relatively old SQL Server XML API called SQLXML (based on COM) which is capable of converting binary serialized XML data back into its string representation. The API does not ship as part of the .NET framework. Although it also contains managed wrapper classes so it is not necessary to directly use COM interop for using the API in .NET. The latest version can be downloaded here:

Unfortunately, SQLXML does not provide a simple conversion function which allows us to pass in an array of binary serialized XML and retrieve an object oriented or textual representation of its content. The conversion logic can only be used when retrieving binary serialized XML data from the server. So the only way to convert the data is to make a round trip to a SQL Server instance in binary form and apply the conversion logic to the result.
Here is an example:

public string ConvertBinaryToXmlString( string sColumnName, byte[] data )
{
	if ( sColumnName != null )
		sColumnName = sColumnName.Replace( "]", "" ); 
	string sQuery = String.Format( "SELECT ? AS [{0}]", sColumnName );
	SqlXmlCommand cmd = new SqlXmlCommand( this.ConnectionString );
	cmd.CreateParameter().Value = data;
	cmd.CommandType = SqlXmlCommandType.Sql;
	cmd.CommandText = sQuery;
	using ( System.IO.Stream s = cmd.ExecuteStream() )
	using ( StreamReader r = new StreamReader( s ) )
		return r.ReadToEnd();
}

The above is not sufficient for converting XML data of arbitrary length into an XML string. As a matter of fact, it will only work with XML data when the binary representation is smaller than approximately 2 KiB.

In order to convert larger resultsets which contain XML data, we have to add more logic to the actual retrieval of the binary serialized XML.

I could have named this “most annoying limitations” as well:

SQL Server 2008

 
No automatic cascading updates of foreign keys with multiple references.

The only workaround for this common scenario is to use triggers for the second column. Ouch. This even applies if the foreign keys pointing to the same primary key are not in the same table, but in different tables which have FK-relationships.
The following will not work, even when we specify ON UPDATE SET NULL for the second USER_ID foreign key.

CREATE TABLE USERS ( ID INT PRIMARY KEY IDENTITY( 0, 1 ), SURNAME INT )
CREATE TABLE ACTIONS ( 
    ID INT PRIMARY KEY IDENTITY( 0, 1 ), 
    CREATING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE, 
    EXECUTING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE,
    VALUE1 INT )

Fails with error: “Introducing FOREIGN KEY constraint ‘XXX’ on table ‘ACTIONS’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.”. The only thing permitted is ON UPDATE NO ACTION.

Using Table Valued Parameters With Any Type

TVPs offer big benefits when sending mass data from a client application to a SQL Server. However they are restricted to be used only in conjunction with custom types. It is not possible to use TVPs without prior declaration of a custom type – which necessitates additional changes to the database schema and permissions for every usage of a TVP.
Supported example:

CREATE TYPE dbo.MyType AS TABLE( MyColumn INT ); 
GRANT EXEC ON TYPE::dbo.MyType TO MyRole;

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "dbo.MyType";

More flexible solution, but not supported:

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "TABLE( MyColumn INT )";

Declaring Variables With Block Scope

It is as if you are forced to work with global variables as in the good old times of programming. The following looks weird, but will actually compile and run, although variable declaration and initialization are always skipped:

CREATE PROCEDURE spTEST AS
IF 0 = 1
BEGIN
    DECLARE @v INT
    SET @v = 0
END
SELECT @v

CREATE OR REPLACE / CREATE OR ALTER

No create or replace / create or alter for user defined objects. Something that makes a developer temporarily switch from ALTER PROCEDURE to CREATE PROCEDURE and back every morning after the dev database has been wiped clean.

There is a workaround, however it is a bad one:

IF OBJECT_ID( "database.schema.mytable" ) IS NOT NULL
    DROP TABLE mytable
CREATE TABLE mytable ...

In this case we do not need to modify our code depending on if the object exists or not. But what if the object exists and there are already some permissions assigned to it? Go figure.

SQL Server 2005

 
Capturing non-inserted Columns

When doing a mass insert using INSERT … SELECT, it is not possible to capture columns from the source or destination table which are not part of the insert. Is is especially useful when inserting into a table with an IDENTITY PRIMARY KEY. SQL Server 2008 improves this by introducing the MERGE statement. For a workaround, see my post on this subject.

Variable Declaration and Assignment

Only available from SQL Server 2008.

DECLARE @v INT = 0;

Instead of:

DECLARE @v INT
SET @v = 0

SQL Server 2000

 
Builtin Paging in Result Sets

Has to be done the hard way. Resolved in SQL Server 2005 by introduction of the ROW_NUMBER() OVER (…) expression:

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER ( ORDER BY id ASC ) AS ROW FROM syscolumns )
SELECT * FROM CTE WHERE ROW BETWEEN 13 AND 26

This is a workaround for SQL Server 2000, assuming that row numbers start with 1:

CREATE TABLE #TMP( ID1 INT PRIMARY KEY IDENTITY( 1, 1 ), ID2 INT )
INSERT INTO #TMP ( ID2 ) SELECT id FROM sysobjects ORDER BY crdate
SELECT o.* FROM sysobjects AS o JOIN #TMP AS t ON o.id = t.ID2 WHERE t.ID1 BETWEEN 3 AND 9
DROP TABLE #TMP