学步园

SQL using IN

The SQL IN statement allows returning a set of records based on a variable (unknown at design time) number of keys. But IN doesn't allow parameters, which leaves us with either fixed lists or dynamic SQL statements. The problem with fixed lists is they don't provide the flexibility needed at times. But dynamic SQL requires the statement to be compiled and a new plan to be cached for each new set of keys. The result is CPU and memory resources are required to compile and cache queries that will never be reused (see my article on parameterized SQL).

It would be nice to be able to pass an array of values as a parameter to a stored procedure or ad hoc statement, but T-SQL doesn't support arrays because it is a set-based language. When I first learned about table variables in SQL 2000 I thought they were the answer. But I quickly discovered that you cannot define them as parameters, they only exist as local variables and their scope is limited to the batch where they are declared. However, I did eventually discover I could use table-valued functions to create and return a table variable.

Table-valued functions

Table valued functions have been available since SQL 2000. A table-valued function is a user-defined function which returns a result set as a table variable. The output can be the result of some SELECT statement or a temp table populated within the function. A user-defined function (table-valued or scalar) takes a set of parameters just like a stored procedure and returns a result without side-effects (DML operations are not allowed). This means we can pass a parameter to a table-valued function that returns a result set without resorting to dynamic SQL. The temp table returned by our function will act as an array of values which can be referenced by the IN clause. And because there's no dynamic SQL involved, the function itself will not cause our stored procedure to be recompiled or our ad hoc batch to be compiled every time it is submitted.

CREATE FUNCTION [dbo].[function_string_to_table]

(

    @string VARCHAR(MAX),

    @delimiter CHAR(1)

)

RETURNS @output TABLE(

    data VARCHAR(256)

)

BEGIN

 

    DECLARE @start INT, @end INT

    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

 

    WHILE @start < LEN(@string) BEGIN

        IF @end = 0

            SET @end = LEN(@string) + 1

 

        INSERT INTO @output (data)

        VALUES(SUBSTRING(@string, @start, @end - @start))

        SET @start = @end + 1

        SET @end = CHARINDEX(@delimiter, @string, @start)

    END

 

    RETURN

 

END

The above function takes a VARCHAR(MAX) and CHAR(1) as parameters. The VARCHAR is the list of keys, concatenated and sent as a parameter. The CHAR allows for the use of any desired character as a delimiter (comma, pipe, colon, semi-colon or whatever you may choose). The function uses the @delimiter parameter to parse the @string parameter and insert each delimited value into the defined temp table (@output). Now we can use the function as follows:

--JUST RETURN THE KEYS

SELECT * FROM dbo.function_string_to_table('key1|key2|key3|key4|key5', '|')

 

--USE THE FUNCTION AS A SUBQUERY

SELECT * FROM table1 WHERE sID IN (SELECT *

    FROM dbo.function_string_to_table('key1|key2|key3|key4|key5', '|'))

As an additional benefit, you can now pass an "array" to a SQL server stored procedure or batch. Take the following stored procedure as an example:

CREATE PROCEDURE procedure_takes_array

    @string VARCHAR(MAX),

    @delimiter CHAR(1)

AS

 

    SELECT * FROM table1 WHERE sID IN

    (SELECT * FROM dbo.function_string_to_table(@string, @delimiter))

 

GO

This can then be called from a client application as follows:

Collapse

void GetData(string[] keys){

using (SqlConnection oconn = new SqlConnection

        ("Application Name=SQLArrayTest;Server=(local);

    Database=MaxPreps_v2;Trusted_Connection=Yes;"))

      {

          oconn.Open();

 

            string SQL = "SELECT * FROM

        dbo.function_string_to_table(@keys, '|')";

                   

            using (SqlCommand cmd = oconn.CreateCommand())

            {

                cmd.CommandText = sql;

                cmd.CommandType = CommandType.Text;

                  cmd.Parameters.Add("@keys", SqlDbType.VarChar, -1);

                  cmd.Parameters["@keys"].Value = string.Join("|", keys);

 

                  //execute the command

                  SqlDataReader rdr = cmd.ExecuteReader();

                  rdr.Close();

            }

      }

}

But this functionality does not come without a price. T-SQL is not very efficient at string manipulation because it is largely a procedural operation, not a set-based operation. The above technique is not an expensive one other than the cost in time due to the manual splitting of the string so it will not rob SQL server of its much needed resources. However if your process runs frequently you may want to test and make sure this method has acceptable performance.

Using the CLR

When I first started writing this article, I debated whether to break this into two separate articles instead of one single article. Even though SQL Server 2005 is almost a year old now it seems as though any article on the topic of the integrated CLR should provide some background first. I suspect that DBAs are still not comfortable with the idea and so it has not been enabled on most systems. But what I found when researching the CLR for this article is that a good understanding of where the CLR can help and where it will hurt will go a long way towards reaping great benefits from this new feature. I can't speak for the security implications at this point and time, but as you will see the CLR can provide huge benefits in performance over T-SQL operations like the one in the previous section. Just make sure you understand when and where to use the CLR. For some background check out this whitepaper: Using CLR Integration in SQL Server 2005.

First, you'll need to make sure the CLR has been enabled on your test server. If it has not already been enabled, run the following script:

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

Now to write our CLR table-valued function we'll use Visual Studio 2005. Follow these steps:

1. Open VS 2005 and select File – New – Project…

2. Expand the node ( [+] ) next to "Visual C#" and select "Database"

3. Select "SQL Server Project" and name your project "SQLArray"

4. Click "OK"

5. If you have not created a database reference before you will be prompted for this information. If you have, select the reference you want to use for debugging and deployment. Otherwise, enter the server name, authentication credentials, and select the database you want to use.

6. To create a user-defined function select Project – Add user-defined function…

7. Name the file "SQLArray" and click "Add"

Now replace the contents of the file with the following code:

Collapse

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

 

public partial class UserDefinedFunctions

{

    [SqlFunction(Name="clrfunction_string_to_table",

    FillRowMethodName="FillRow", TableDefinition="ID NVARCHAR(50)")]

    public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)

    {

        //return single element array if no delimiter is specified

        if(delimiter.Length == 0)

            return new string[1] { str.Value };

 

 

        //split the string and return a string array

        return str.Value.Split(delimiter[0]);

    }

 

    public static void FillRow(object row, out SqlString str)

    {

        //set the column value

        str = new SqlString( (string) row );

    }

}

First, to point out a few things: The method which will be used for the "main" function must return System.Collections.IEnumerable, so a using statement has been added for the System.Collections namespace. Also the SqlFunction attribute must define the following values:

• Name: This is the name of the table-valued function to be created on the specified instance of SQL Server.

• TableDefinition: This is the definition for the temp table to be returned. NOTE: timestamp and non-unicode string types (char, varchar and text) are not allowed as part of the table definition. Your code will build, but it will not deploy. Instead you will receive the following error message: "Cannot use 'varchar' column in the result table of a streaming user-defined function".

• FillRowMethodName: The method to be used to populate the temp table which will be returned by the function. The CLR will use the IEnumerable reference and iterate over the result. For each item in the enumerator the method defined by this property will be called by the CLR. The signature of the method must include an object which will be used to pass the current item and then an out parameter for each column in the defined temp table.

NOTE: Always use SqlType types because they support the INullable interface and this allows you to be consistent with SQL standards.

Now build the project, then to deploy it select "Build" – "Deploy SqlArray". Now you can call the table-valued function just like you would any other function:

--JUST RETURN THE KEYS

SELECT * FROM dbo.clrfunction_string_to_table

        ('key1|key2|key3|key4|key5', '|')

 

--USE THE FUNCTION AS A SUBQUERY

SELECT * FROM table1 WHERE sID IN

    (SELECT * FROM dbo.clrfunction_string_to_table

        ('key1|key2|key3|key4|key5', '|'))

But the result is much faster as we'll see next.

Test Project

The test project is just a simple "load test" application which connects to the database and repeatedly calls the function, but does not use the function to return results from any table. The reason for this is to demonstrate the raw difference in performance between the T-SQL and the CLR versions of the function. My test sample was 1000 iterations and below are the results:

Test Project (1000 Iterations)

- T-SQL 17 min 20 sec 765 ms

- CLR 0 min 3 sec 453 ms

As you can see the difference is drastic. The CLR version is much faster than the T-SQL version. There are two reasons for this. First, the CLR is much faster at string manipulation. A better programmer might be able to come up with a more efficient T-SQL algorithm for splitting the string than I have, but it will never be as efficient as the System.String.Split() method. The second reason for the performance improvement is that SQL Server will generate a temp table for the T-SQL version which involves I/O to tempdb. While the CLR version will stream the results as they become available. This makes it even more efficient for large result sets because SQL Server doesn't have to wait for the entire result and it doesn't need to keep the entire result in memory. The stream can be discarded as it is read.

Conclusion

While the CLR is obviously the better choice here, you may not have the option of using the CLR on your instance of SQL Server due to the policies of your team. But this doesn't mean you can't benefit from the T-SQL version. Either method can conserve both CPU cycles and memory by reducing compiles/recompiles due to differences in values passed to the IN statement .

Before I wrote this article, I didn't really see the benefit of using the integrated CLR on SQL Server. But now I intend to further investigate th