Code Focused

A CLR Alternative to the SQL Server ISNUMERIC function

CLR functions that perform string manipulation and substring extraction perform very well in SQL Server.

• By Joe Kunk
• 10/11/2011

The stored procedure was performing a calculation on a SQL Server column of type nvarchar(50), using the SQL Server ISNUMERIC function to determine which text values could safely be included in the calculation. For those values where the ISNUMERIC function returned 1 (true), it then used the CAST function to convert the values to DECIMAL(12,4). The code is below.

Declare @TestTable Table(SampleString nvarchar(50))

    

  -- AlL table values return ISNUMERIC()=1 (true)

  INSERT INTO @TestTable

  SELECT '12.37'  union

  SELECT '$12.37' union

  SELECT '$,1,.2' union

  SELECT '1.2e3'  union

  SELECT '1.2d3'  union

  SELECT '\'	union

  SELECT '+'	union

  SELECT '-'	union

  SELECT '.'	union

  SELECT CHAR(9)

  

  SELECT SUM(CAST(SampleString as Decimal(12,4))) as SampleSum 

  FROM @TestTable WHERE ISNUMERIC(SampleString)=1

The ISNUMERIC function returns 1 (true) for all the values inserted into @TestTable, yet only the first value of '12.37' can be successfully converted to Decimal via the CAST function. Any of these false numeric values would produce a SQL error and halt the SUM() operation. Therein lies the problem; there is no good way in native T-SQL to reliably exclude all text values that would cause an error in a particular CAST operation.

While T-SQL does have a TRY-CATCH construct, it isn't helpful in this instance. The desired behavior is to have CAST errors caught and corrected for individual values, but the T-SQL TRY-CATCH construct would transfer control to the CATCH block upon the first CAST error, effectively aborting the SUM operation.

A Transact SQL Solution
If interested in a purely T-SQL solution to this issue, you can alter the select in Listing 1 to read as follows:

SELECT SUM(CAST(SampleString as Decimal(12,4))) as SampleSum 

  FROM @TestTable WHERE ISNUMERIC(SampleString+'e0')=1

The addition of the trailing 'e0' will eliminate most false positives from the ISNUMERIC() function, but not all. A value of CHAR(9) (tab character) will still return a 1 and fail in the CAST operation. And don't forget to trim the string for trailing blanks before appending the 'e0'.

The Visual Basic Alternative
While the T-SQL solution above would be adequate for most situations, I wanted a more elegant alternative that not only would correctly test for numeric values, but also test for a specific numeric type, going so far as to indicate if a particular value would fit into the specified Decimal precision. T-SQL isn't particularly well suited to advanced string manipulation. I have found that small CLR functions that perform string manipulation and substring extraction perform very well in SQL Server.

To test for ISNUMERIC effectively, I needed to write a SQL Server CLR function and deploy the assembly into SQL Server itself. After confirming with the SQL Server DBA that a CLR function would be permitted on the production SQL Server, I wrote the function that appears in Listing 1. You'll notice it uses the Microsoft.Net framework SQL data types and a valid numeric value is determined by an attempted conversion to the indicted numeric type. Success means that it's a valid string representation for that numeric type. Using this approach delegates the validity decision to the framework itself; I haven't implemented any unnecessary conversion rules that could be in error.

NOTE: Some DBAs may be reluctant to allow CLR functions to be deployed to a production server. The reasons vary but may include a lack of visibility into the operations within the compiled assembly and the associated difficulty in profiling the performance of activities within the assembly.

To create a SQL Server CLR function, create a project of type "Visual Basic SQL CLR Database Project" under the Database, SQL Server template category. Then add a new item of type "User-Defined Function".

By default, Visual Studio 2010 will build to the Microsoft.Net Framework 4.0. To deploy an assembly to SQL Server, it must be compiled to the Framework 3.5. Go to the Project properties Compile tab and click on the Advanced Compile Options button at the bottom of the page to change the Target Framework.

With sufficient permissions, you can deploy directly to the server from within Visual Studio 2010. Right-click on the project and choose Deploy. It will build and then attempt to deploy to the database you specified when you created the project. Have the Output window open in order to see any messages generated during this process; you'll need them for debugging if the Deploy fails.
If you have admin rights to the server but are still having difficulty deploying the function to SQL Server, it may be because the CLR option isn't enabled on the server. The following T-SQL script is needed to enable CLR functions on the server; it's not enabled by default.

sp_configure 'show advanced options', 1;

  GO

  RECONFIGURE;

  GO

  sp_configure 'clr enabled', 1;

  GO

  RECONFIGURE;

  GO

If you don't have admin rights on the SQL Server, you'll need to supply the generated assembly to the DBA from the Bin folder, along with the <Project Name>.sql file generated during a deploy operation as a guide for what needs to be done.

Using the CLR Function
Once deployed to the SQL Server, the CLR function appears in the Programmability, Functions, Scalar-valued Functions folder of the database, much like a T-SQL user-defined function. See Figure 1 for a sample query that uses the function and illustrates the ability to test for numeric compatibility with the various size integer data types of tinyint, smallint, int, and bigint. The blank value in Row 1 is actually a CHAR(9) tab value which has no visual representation.

The last column shows the result of a Visual Basic CLR function ToDecimal, included in the code download, to convert from text to decimal as an alternative to the CAST function. The advantage is that the ToDecimal function will return a NULL on an invalid conversion and will not abort the transaction as CAST would. CLR functions must configure their output via static attributes on the method (see the SQLFacet attribute in the ToDecimal function source code), so all returned values are in Decimal(28,8) format to provide sufficient precision.

[Click on image for larger view.]

Figure 1. A sample CLR query

Visual Basic or C#?
I've illustrated the process of creating useful SQL Server CLR functions using Visual Basic, but functions written in C# work equally well. In fact, the code download for this article has full source code in both C# and Visual Basic projects. Simply deploy the one appropriate for your organization's coding standards.

Most database functions can be handled well in T-SQL; it should be the language of first choice for automation in SQL Server. Some operations, particularly string manipulations, are more effective with managed code. I've exercised these functions against tables with millions of rows and the performance is very good.

My use of CLR functions has been primarily limited to small scalar functions as shown in this article. I'm very interested to hear the experiences from other developers that have used the CLR capabilities in other ways. What has been your experience in terms of pros, cons, and performance? What types of CLR functions do you write? Does your DBA feel comfortable deploying CLR functions? Have you experienced any CLR function issues in production? Please use the online reader comments area of this article to share so that we all can become better educated on this technology.

• Get Code Download