SQL Server - Validation During Function Creation

Now here's a little thing I didn't know until recently. Seems like this is happening a lot lately and I really should get around to documenting these at some point. Anyhoo, my latest finding is as follows...

We have a process that, every time a script is checked into our version control system, rebuilds all of the databases from scratch, starting from Release 1 and then applying all updates in order. This normally ensures a problem free release into production, however, this most recent time was different. This time the release into production failed when trying to create a function in one of the databases. So what was different between production and the build process? If I mention that we have a stored procedure that creates a temporary table that is used by the function, does that prompt any ideas of what went wrong?

Well, here's the thing...
For inline table functions, attempting to create one when the underlying object does not exist will fail. However, for scalar and multi-statement table functions things are a little different. If the underlying objects do not exist the functions will be created with no issue. However, if the underlying objects do exist, a check is performed to ensure that their definition matches that expected by the function.

So in our case, because the system is live in production the SP has been called many times and thus the table has been created and dropped many times. However, in the new update the structure of this table has been changed. Now because the SP is never actually called during the build process no table has been created and the function can be deployed. In production however, the SP has been called and the old version of the table does exist, causing the installation to fail.

Stored procedures exhibit this same behaviour but don't have the differences related to inline and multi-statement/scalar functions.

Try it yourself:

/**
 * Create functions.
 */ 
-- Successful.
CREATE FUNCTION [dbo].[udf_scalar_works]() 
RETURNS INT 
AS
BEGIN 
  RETURN ( SELECT TOP 1 [col2] FROM [dbo].[bar] );
END;
GO

-- Successful.
CREATE FUNCTION [dbo].[udf_multi_works]()
RETURNS @x TABLE (aaa INT)
AS
BEGIN
  INSERT INTO @x (aaa)
  SELECT [col2]
  FROM [dbo].[bar];
  
  RETURN;
END;
GO

-- Error: Invalid object name 'dbo.bar'.
CREATE FUNCTION [dbo].[udf_inline_doesnt_work]()
RETURNS TABLE
AS
  RETURN ( SELECT [col2]
           FROM [dbo].[bar] );
GO


/**
 * Create table without the required column.
 */
CREATE TABLE [dbo].[bar] ( [col1] INT );GO


/**
 * Try create functions again.
 */
-- Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_scalar_doesnt_work]()
RETURNS INT
AS
BEGIN
  RETURN ( SELECT TOP 1 [col2] FROM [dbo].[bar] );
END;
GO

-- Error: Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_multi_doesnt_work]()
RETURNS @x TABLE (aaa INT)
AS
BEGIN
  INSERT INTO @x (aaa)
  SELECT [col2]
  FROM [dbo].[bar];
  
  RETURN; 
END;
GO

-- Error: Invalid column name 'col2'.
CREATE FUNCTION [dbo].[udf_inline_doesnt_work]()
RETURNS TABLE
AS
  RETURN ( SELECT [col2]
           FROM [dbo].[bar] );
GO

SQL Server - Identifying Control Characters

Recently a query against our Analysis Services database failed with an error about invalid XML characters and thus prompted me to start the investigation as to which members contained these. I know that in Analysis Services you can set how you want to handle invalid XML characters but as we shouldn’t receive these I'm using the defaults and I don’t want to use “replace” etc.

Interestingly though, during my investigation I came across some unexpected behaviour with regards the 0x00 control code. At that point I just needed to identify the culprit members and prevent it from entering the database in the future, so I left that peculiarity and wrote the following function:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[udf_string_contains_control_character]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

  DROP FUNCTION [dbo].[udf_string_contains_control_character];

END;

GO

CREATE FUNCTION [dbo].[udf_find_name_with_control_character] (@i_InputText NVARCHAR(MAX))

RETURNS INT

AS

BEGIN

  RETURN ( SELECT CASE WHEN (-- First control code set (i.e. 1 to 31)

                             PATINDEX('%[' + CHAR(0x01) + '-' + CHAR(0x1F) + ']%', @i_InputText COLLATE DATABASE_DEFAULT) +

                             -- Control character 127 (i.e. DEL)

                             PATINDEX('%[' + CHAR(0x7F) + '-' + CHAR(0x7F) + ']%', @i_InputText COLLATE DATABASE_DEFAULT) ) > 0 THEN

                    1

                  ELSE

                    0

                  END );

END;

GO

Clearly the function doesn’t handle every “dodgy” character but it handles some of the more common control characters and can be easily extended.

I then never found the time to get back to the issue with regards the 0x00 control character until now, thanks largely to a question posted on the Analysis Services forum which you can see here: http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/619a0d73-455c-404f-99ad-bbebfab2cf18)

In this particular case, the error message was:

The server sent an unrecognizable response. _'.', hexadecimal value 0x00, is an invalid character. Line , position .

This prompted me to revisit why, when I used the 0x00 control character in the filter in the above function, the function didn’t behave as I initially expected. Clearly it appears that the control character 0x00 in SQL Server is a little problematic but I didn’t realise just how problematic until I started digging a little deeper.

So how hard can it be to find control characters in SQL? Answer, harder than you might think. So, in SSMS run this in grid view (i.e. CTRL-D) and then in test view (i.e. CTRL-T).

SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test" ) a;

You should hopefully see the differences there. The grid view hides the underlying artefact, though the lengths clearly show something isn't right, but switching to text view shines a bit more light on the issue. So let’s try finding the row using LIKE…

SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test" ) a

WHERE [test] LIKE '%' + CHAR(0x00) + '%';

Er, nope, that doesn’t work. Aha, maybe we need to escape it...

SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test"

      UNION ALL

       SELECT 'bar' AS "test" ) a

WHERE [test] LIKE '%\' + CHAR(0x00) + '%' ESCAPE '\';

Groan, grumble, no that’s still not the right answer. Note how we get both rows. I suspect all of this is due to the fact that C Strings are “Nul” terminated (i.e. \0) so all rows match. I could go on for ages about trying to use REPLACE, PATINDEX, CHARINDEX etc. as I did, but I’ll save you the time, they don’t work.

For example, trying to use REPLACE:

SELECT REPLACE([test], CHAR(0x00), '') AS "test",

       LEN(REPLACE([test], CHAR(0x00), '')) AS "len",

       DATALENGTH(REPLACE([test], CHAR(0x00), '')) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test" ) a;

I then had one of those eureka moments whilst looking through the list of string functions in Books Online. What happens if I use the QUOTENAME function? I figured that that might do something interesting based on the fact that it is meant to delimit object names, and indeed it does do something interesting. If a column contains the 0x00 control character, wrapping that value with QUOTENAME returns NULL. So, to find rows that contain the 0x00 character in the data as is, I found the following works reliably:

SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test"

      UNION ALL

       SELECT 'bar' AS "test" ) a

WHERE [test] IS NOT NULL AND

      QUOTENAME([test]) IS NULL;

However, this only identifies the culprits. We still haven’t discovered a way to fix the data by removing the control code. We know that we can’t use the standard string functions such as REPLACE etc but what happens if we use XML?

So, using XML we can identify the control character by looking for "�" within the xml fragment.  See what’s returned by the following code:

SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test"

      UNION ALL

       SELECT 'bar' AS "test" ) a

FOR XML AUTO;

Okay, so now that we can identify the control character we should be able to remove it with the standard text functions. Something like the following should do it:

SELECT dta.[xmltest].value('(/a/@test)[1]', 'VARCHAR(20)') AS "test",

       LEN(dta.[xmltest].value('(/a/@test)[1]', 'VARCHAR(20)')) AS "len",

       DATALENGTH(dta.[xmltest].value('(/a/@test)[1]', 'VARCHAR(20)')) AS "data_length",

       dta.[xmltest].value('(/a/@len)[1]', 'INT') AS "original_len",

       dta.[xmltest].value('(/a/@data_length)[1]', 'INT')  AS "original_data_length"

FROM (

SELECT CAST(REPLACE([xmltest], '�', '') AS XML) AS "xmltest"

FROM ( -- Retrieve sample data with lengths etc.

       SELECT [test], LEN([test]) AS "len", DATALENGTH([test]) AS "data_length"

       FROM ( SELECT 'foo' + CHAR(0x00) + 'bar' AS "test"

             UNION ALL

              SELECT 'bar' AS "test" ) a

       -- Don't specify TYPE as it will fail due to the 0x00 character

       -- not allowed in XML. We'll convert it later after removing it.

       FOR XML AUTO ) xmled(xmltest) ) dta;

Clearly you could also write a function to iterate through each character within the string and remove the control character, but where’s the fun in that?