Congratulations to Dan Barrett and Anith Sen, an independent database consultant in Cordova, Tennessee. Dan won first prize of $100 for the best solution to the December Reader Challenge, "A Dashboard Application." Anith won second prize of $50. Here’s a recap of the problem and the solution to the December Reader Challenge.


Terry, a systems analyst for the IT department, is developing a Web-based management dashboard. The data for the dashboard is stored in a SQL Server 2000 database. As part of one administrative function in the dashboard, the application requires the database to store user-defined attributes. The following code shows the table’s schema including the relevant columns to store the attribute values for various objects in the system.

CREATE TABLE AttribVals (                                 Id int NOT NULL,                                 Val varchar(30) NOT NULL,                                 PRIMARY KEY( Id, Val )                              )

The column ID in the AttribVals table represents generic objects in the dashboard application. The following script creates sample data for the table.

INSERT INTO AttribVals (Id, Val)                              VALUES ( 1, 'A' )                              INSERT INTO  AttribVals (Id, Val)                              VALUES ( 1, 'B' )                              INSERT INTO  AttribVals (Id, Val)                              VALUES ( 1, 'C' )                              INSERT INTO  AttribVals (Id, Val)                              VALUES ( 2, 'B' )                              INSERT INTO  AttribVals (Id, Val)                              VALUES ( 3, 'A' )                              INSERT INTO  AttribVals (Id, Val)                              VALUES ( 3, 'C' )

Terry is designing a stored procedure that the Web page will call to manage the user-defined attributes. The Web page needs to pass a list of attributes and get the corresponding objects’ ID value. Help Terry define the stored procedure with the required parameters. The stored procedure has to handle any number of attributes. For simplicity, assume that the length of all the values will be less than 8000. The stored procedure also has to retrieve the ID only if all the specified attributes are configured for the ID value. Using the sample data shown earlier, specifying A and C as attribute values should return 1 and 3. Similarly, specifying B should return 1 and 2.


Terry can define the stored procedure with a varchar data type parameter, he uses to pass a list of attribute values as a comma-separated string to the stored procedure. This lets the stored procedure process any number of attribute values as specified by the Web page. To retrieve the IDs based on two attribute values, A and C, Terry can use the following query:

SELECT DISTINCT a1.Id                                 FROM AttribVals AS a1                               WHERE NOT EXISTS (SELECT *                                    FROM (SELECT 'A' UNION ALL SELECT 'B'                                    UNION ALL SELECT 'C') AS f(Value)                                    WHERE NOT EXISTS(SELECT *                                 FROM AttribVals AS a2                                    WHERE a1.\[ID\] = a2.\[ID\]                                 AND f.Value = a2.Val))

The query checks that there are no values being searched for which are also not present in the table. This double-negative approach gives only the matching IDs that have all values searched for. Terry can also use a query like the following to do the same:

SELECT a.Id                                FROM AttribVals AS a                               WHERE a.Val IN ( 'A', 'C' )                               GROUP BY a.Id                              HAVING count(*) = 2

This query can be generalized as following:

SELECT a.Id                                FROM AttribVals AS a                               WHERE a.Val IN (  )                               GROUP BY a.Id                              HAVING count(*) = 

The comma-separated string of attribute values can be parsed into individual values by using a table-valued function. This lets Terry normalize the string into a set of values. The table-valued function that performs this operation follows:

IF objectproperty( object_id( 'fn_SplitStr' ) , 'IsTableFunction' ) = 1                                  DROP FUNCTION fn_SplitStr                              GO                              CREATE FUNCTION fn_SplitStr (                                  @SourceString varchar( 8000 ) ,                                  @Delimiter varchar( 5 ) = ','                              )                              RETURNS @VALUES TABLE( Position smallint IDENTITY NOT NULL, Value varchar( 8000 ) NOT NULL )                              AS                              BEGIN                                  DECLARE @Start smallint, @End smallint                                  SET @Delimiter = coalesce( @Delimiter, ',' )                                  SET @Start     = len( @Delimiter )                                  IF LEFT( @SourceString, len( @Delimiter ) )  @Delimiter                                    SET @SourceString = @Delimiter + @SourceString                                  IF RIGHT( @SourceString, len( @Delimiter ) )  @Delimiter                                    SET @SourceString = @SourceString + @Delimiter                                  WHILE( 1 = 1 )                                  BEGIN                                      SET @End = charindex( @Delimiter, @SourceString, @Start + 1 )                                      IF  @End = 0 BREAK                                      INSERT INTO @VALUES( Value ) VALUES( substring( @SourceString, @Start + 1, @End - @Start - 1 ) )                                      SET @SourceString = stuff( @SourceString, 1, @End - 1, '' )                                  END                                  RETURN                              END                              GO

A sample usage of this user-defined function (UDF) follows.

SELECT * FROM dbo.fn_SplitStr( 'a,b,c', default )                              SELECT * FROM dbo.fn_SplitStr( 'x~y~z', '~' )

This table-valued function can now be used in the stored procedure to return the list of IDs in a generic manner. The definition of the stored procedure follows:

CREATE PROCEDURE GetIdsForAttributes                              (@AttribVals varchar(8000))                              AS                              BEGIN                                  SELECT DISTINCT a1.Id                                    FROM AttribVals AS a1                                   WHERE NOT EXISTS (SELECT *                                    FROM fn_SplitStr(@AttribVals, DEFAULT) AS f                                   WHERE NOT EXISTS(SELECT *                                    FROM AttribVals AS a2                                   WHERE a1.\[ID\] = a2.\[ID\]                                 AND f.Value = a2.Val))                              END                              GO

The stored procedure GetIdsForAttributes can now be invoked by the Web page with a comma-separated list of values to get the matching IDs. Some of the sample calls for the stored procedure follow:

EXEC GetIdsForAttributes 'A,C'                              EXEC GetIdsForAttributes 'A,B,C'

The following is another implementation of the stored procedure using the GROUP BY approach:

CREATE PROCEDURE GetIdsForAttributes2                              (@AttribVals varchar(8000))                              AS                              BEGIN                                 SELECT a.Id                                        FROM AttribVals AS a                                       WHERE a.Val IN (SELECT f1.Value                                        FROM fn_SplitStr(@AttribVals, DEFAULT) AS f1)                                    GROUP BY a.Id                                      HAVING count(*) = (SELECT count(*)                                        FROM fn_SplitStr(@AttribVals, DEFAULT) AS f2)                              END                              GO

Lastly, the comma-separated list of values can also be parsed using a set-based logic with a table of numbers. The numbers table for this purpose can be created using the following statements:

IF OBJECT_ID('Nbrs') IS NOT NULL                                 DROP TABLE Nbrs                              GO                              SELECT TOP 8000 IDENTITY(INT) "n"                                INTO Nbrs                                FROM master..syscolumns s1, master..syscolumns s2                              GO 

The stored procedure that uses the table of numbers to parse the comma-separated list of values in a GROUP BY query to get the results follows:

CREATE PROCEDURE GetIdsForAttributes3                              (@AttribVals varchar(8000))                              AS                              BEGIN                                 DECLARE @delimiter char(1)                                 SET @delimiter = ','                                 SELECT a1.Id                                    FROM AttribVals a1                                    JOIN Nbrs                                      ON a1.Val = SUBSTRING( @AttribVals, n,                                       CHARINDEX( @delimiter, @AttribVals + @delimiter, n ) - n )                                   WHERE SUBSTRING(@delimiter + @AttribVals, n, 1 ) = @delimiter                                     AND n                               


Now, test your SQL Server savvy in the January Reader Challenge, "Eliminating Recompilation" (below). Submit your solution in an email message to by December 16. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.


Sam, a database architect for a company that auctions products online has a database hosted primarily on SQL Server 2000. The Web site for the auctions is transactional in nature. While investigating a performance problem, Sam notices lots of stored procedures performing various operations with temporary tables. By using SQL Server Profiler, Sam determines that several large stored procedures' performance problems are caused by excessive recompilations during execution. To see the kind of problem Sam's dealing with, run the sample stored procedure:

USE Northwind                              GO                              IF object_id('dbo.ReportOrdersByProduct') IS NOT NULL                                  DROP PROCEDURE dbo.ReportOrdersByProduct                              GO                              CREATE PROCEDURE dbo.ReportOrdersByProduct                              AS                              BEGIN                                  CREATE TABLE #od1 ( OrderID int, ProductID int )                                  INSERT INTO #od1 ( OrderID, ProductID )                                  SELECT od.OrderID, od.ProductID                                    FROM "Order Details" AS od                                  SELECT p.ProductName, count(*) AS #Orders                                    FROM #od1 AS od                                    JOIN Products AS p                                      ON p.ProductID = od.ProductID                                GROUP BY p.ProductName                                  HAVING count(*) >= 25                              END                              GO

You can observe the behavior in Sam's production environment by setting up a Profiler trace that has the following events:

   SP:Starting                                 RPC:Starting                                 SP:StmtStarting                                 SP:Recompile                                 SP:Completed                                 RPC:Complete                                 Objects:Auto Stats                              

Observe the events during the sample stored procedure's first execution because the sample code is simple, so subsequent executions will use the cached plan. The recompilation of the stored procedure when it hits the SELECT statement mimics the behavior in the production system. Assuming that Sam wants to retain the temporary table logic and make minimal changes to the stored procedure, how can he reduce or eliminate the recompilation of the stored procedure for the SELECT statements that are accessing the temporary tables?