Writing a Stored Procedure

Downloads
39491.zip

Congratulations to Dave Wilson, a senior database administrator for Starbucks Coffee Company in Seattle, and Vadim Rapp, owner of Vadim Rapp Consulting in Park Ridge, Illinois. Dave won first prize of $100 for the best solution to the July Reader Challenge, "Writing a Stored Procedure." Vadim won second prize of $50. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem:

George is a database analyst for a company that provides realtime reporting applications. His databases run on SQL Server 2000 and 7.0. George is receiving timeouts from the stored procedure that the Web pages call. Upon investigation, he determines that a particular query on large aggregation tables under heavy loads is causing the timeouts. All these tables have a primary key or composite index consisting of several columns; the procedure partitions the tables of interest, then creates new tables. The timeouts are happening on these newly created tables as SQL Server creates auto-statistics on secondary columns (any column other than the first.) When the database server is under heavy load, multiple queries execute for the first time against the new tables, and the auto-statistics creation process can't complete quickly. Subsequently, users either get tired of waiting and cancel the execution of the Web page or the pages timeout.

George temporarily circumvents the problem by manually executing the stored procedure from Query Analyzer, where the query finishes without intervention. By executing the stored procedure manually, George makes sure that SQL Server creates the auto-statistics on the secondary columns of the index and that subsequent executions of the stored procedure from the Web page work within the timeout interval. To automate the process of creating statistics on the secondary columns of the index, George decides to write a stored procedure that does the following:

Retrieves a list of tables based on a text or specific search value (For convenience, the procedure uses a common prefix to partition the tables, hence the need for this parameter.)
Determines the index that contains a given set of columns
Creates statistics on the secondary columns of the index if none exist

Help George write this stored procedure.

Solution:

George can query the sysindexes system table to get details about indexes and statistics created on a particular table. Additionally, system metadata functions such as OBJECT_ID(), COL_NAME(), and INDEXPROPERTY() will help him write the code efficiently.

For example, to get the name of the index that contains columns orderid and productid from the Northwind OrderDetails, George uses the query:

USE Northwind
GO
SELECT i.name
  FROM sysindexes AS i
  JOIN sysindexkeys AS ik
    ON ik.id = i.id
   AND ik.indid = i.indid
 WHERE i.id = OBJECT_ID( '\[Order Details\]' )
   AND COL_NAME( i.id, ik.colid ) = CASE ik.keyno
                                      WHEN 1 THEN 'orderid'
                                      WHEN 2 THEN 'productid'
                                    END
 GROUP BY i.name
HAVING COUNT(*) = 2

He can then incorporate the preceding logic into a stored procedure to get the list of tables and indexes that contain a particular set of columns. The stored procedure create_auto_stats, which Listing 1 shows, uses this logic to let George provide a pattern for the tables and specify up to four columns for searching.

After George obtains the list of tables and indexes, he can use a cursor to loop through the key columns of each index and retrieve all secondary columns. The following query, which is part of the stored procedure, shows the cursor logic:

SELECT COL_NAME( sk.id, sk.colid )
FROM sysindexkeys AS sk
WHERE sk.id = object_id( @FullTableName )
AND sk.indid = @IndexID
AND sk.keyno > 1
AND NOT EXISTS( SELECT *
     FROM sysindexes AS i
     JOIN sysindexkeys AS ik
     ON ik.id = i.id
     AND ik.indid = i.indid
     WHERE i.id = sk.id
     AND ik.colid = sk.colid
     AND ik.keyno = 1
     AND INDEXPROPERTY( i.id, i.name, 'IsStatistics' ) = 1 )

By using this query as a cursor source, George can determine which columns he needs to create statistics on. He can then create the statistics by using the CREATE STATISTICS command.

AUGUST READER CHALLENGE:

Now, test your SQL Server savvy in the August Reader Challenge, "Trimming Blanks" (below). Submit your solution in an email message to [email protected] by July 17. SQL Server MVP 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.

Christine, a database analyst for a company that has several SQL Server installations, needs to trim leading and trailing blanks from the data in a table’s text column. The following sample table shows the data that she’s working with:

CREATE TABLE #t (
    id int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
    t TEXT NULL
)
INSERT INTO #t VALUES( 'a     ')
INSERT INTO #t VALUES ( 'b     ')
INSERT INTO #t VALUES ( 'c  d  ')
INSERT INTO #t VALUES ( 'e')
INSERT INTO #t VALUES ( '    ')
INSERT INTO #t VALUES ( '   f')
INSERT INTO #t VALUES ( ' g  ' + space(7000) )
INSERT INTO #t VALUES ( ' h j k l m ')
INSERT INTO #t VALUES ( 'This is a test')

Help Christine write the code to trim all leading and trailing blanks in the t column. The logic should accomplish the same result as applying LTRIM() and RTRIM() system functions on string data but cannot use these functions.

Comments

Plain text