Generating 6-Digit All-Numeric File Numbers for Search - 13 Jul 2006

The July Reader Challenge, "Creating an Easy Query Mechanism for Gathering Customer Data," had no winners. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem:

Adam is a DBA for a hosting company that provides free and paid database access for its customers. The databases reside on SQL Server 2000 instances. Each instance can host up to 100 databases and contains a dbmaster database, which collects and maintains various statistics from the databases. A database-creation utility creates each customer's database and an AdminConfig table in each database. The AdminConfig table maintains different configuration information that the customer creates.

For ad-hoc reporting purposes or as part of a monitoring application, Adam often queries the AdminConfig information from each hosted database. Help Adam create a simple query mechanism in the dbmaster database while adhering to the following requirements:

To create the dbmaster database and several databases with the AdminConfig table, use the is as following sample setup:

CREATE DATABASE dbmaster  CREATE DATABASE db1  CREATE DATABASE db2  CREATE DATABASE db3  GO  CREATE TABLE db1.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )  INSERT INTO db1.dbo.AdminConfig VALUES(1, 'a')  CREATE TABLE db2.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )  INSERT INTO db2.dbo.AdminConfig VALUES(1, 'a')  CREATE TABLE db3.dbo.AdminConfig ( id INT NOT NULL PRIMARY KEY, configname VARCHAR(30) NOT NULL, dbname AS db_name() )  INSERT INTO db3.dbo.AdminConfig VALUES(1, 'a')  GO

Solution:

To get the results from each hosted database's AdminConfig table, Adam can use the UNION operator in the following query:

USE dbmaster  GO  SELECT id, configname, dbname FROM db1.dbo.AdminConfig  UNION ALL  SELECT id, configname, dbname FROM db2.dbo.AdminConfig  UNION ALL  SELECT id, configname, dbname FROM db3.dbo.AdminConfig  GO

To parameterize the dbname option, Adam can use an inline table-valued function. The following code shows the definition of the table-valued function:

USE dbmaster  GO  CREATE FUNCTION GetAdminConfig (@db nvarchar(128) = NULL)  RETURNS TABLE  AS  RETURN (      SELECT id, configname, dbname FROM db1.dbo.AdminConfig WHERE @db = N'db1' OR @db IS NULL      UNION ALL      SELECT id, configname, dbname FROM db2.dbo.AdminConfig WHERE @db = N'db2' OR @db IS NULL      UNION ALL      SELECT id, configname, dbname FROM db3.dbo.AdminConfig WHERE @db = N'db3' OR @db IS NULL  )  GO

In this code, the GetAdminConfig table-valued function retrieves the AdminConfig table from the specified database or, if the database name is unspecified, from all the hosted databases. The search predicate uses the @db variable, instead of the dbname column, as a filter. Because the check can be done against only the @db variable, the query optimizer can eliminate the SELECT statements that don’t match the passed database name. This check is performed either at compile-time, in which case only one SELECT statement gets executed if the correct database name is specified, or at run time before execution by using a special filter called STARTUP EXPR.

The showplan output for the SELECT statement below shows that if the @db database name is specified as 'db1,' only the SELECT statement from db1 database is executed:

USE dbmasterGOSET showplan_text ONGOSELECT * FROM GetAdminConfig( N'db1' )GO    SET showplan_text OFF    GO      /*      |--COMPUTE Scalar(DEFINE:([AdminConfig].[id]=[AdminConfig].[id],   [AdminConfig].[name]=[AdminConfig].[name], [Expr1012]=db_name(CONVERT(NULL))))          |--COMPUTE Scalar(DEFINE:([Expr1012]=db_name(CONVERT(NULL))))                |--CLUSTERED INDEX Scan(OBJECT:([db1].[dbo].[AdminConfig].  [PK__AdminConfig__76CBA758]))    */

If the database name is specified in a variable, the check for @db is performed at run time as shown in the following showplan output (look for the STARTUP EXPR filters):

SET showplan_text ON   GODECLARE @db nvarchar(128)    SET @db = N'db1'    SELECT * FROM GetAdminConfig( @db )   GO    SET showplan_text OFF  GO      /*     |--Concatenation          |--Filter(WHERE:(STARTUP EXPR([@db]='db1' OR [@db]=NULL)))          |    |--COMPUTE Scalar(DEFINE:([Expr1015]=db_name(CONVERT(NULL))))       |         |--CLUSTERED INDEX Scan(OBJECT:([db1].[dbo].[AdminConfig].  [PK__AdminConfig__76CBA758]))          |--Filter(WHERE:(STARTUP EXPR([@db]='db2' OR [@db]=NULL)))           |    |--COMPUTE Scalar(DEFINE:([Expr1016]=db_name(CONVERT(NULL))))         |         |--CLUSTERED INDEX Scan(OBJECT:([db2].[dbo].[AdminConfig].  [PK__AdminConfig__76CBA758]))          |--Filter(WHERE:(STARTUP EXPR([@db]='db3' OR [@db]=NULL)))                |--COMPUTE Scalar(DEFINE:([Expr1017]=db_name(CONVERT(NULL))))                     |--CLUSTERED INDEX Scan(OBJECT:([db3].[dbo].[AdminConfig].  [PK__AdminConfig__76CBA758]))    */

When the database name is specified as a variable in a query of the following GetAdminConfig table-valued function, Adam can use the SET STATISTICS IO output to determine whether he's accessed only the db1 database's AdminConfig table.

SET STATISTICS IO ON   DECLARE @db nvarchar(128)    SET @db = N'db1'    SELECT * FROM GetAdminConfig( @db )    SET STATISTICS IO OFF    GO      /*TABLE 'AdminConfig'. Scan COUNT 0, logical reads 0, physical reads 0, read-ahead reads 0.TABLE 'AdminConfig'. Scan COUNT 0, logical reads 0, physical reads 0, read-ahead reads 0.TABLE 'AdminConfig'. Scan COUNT 1, logical reads 2, physical reads 0, read-ahead reads 0.*/

Now, Adam can efficiently use the inline table-valued function to query the AdminConfig table in each of the hosted databases because the query resolves to only one table when the database name is specified. When a new database is created, Adam can recreate the GetAdminConfig table-valued function as part of the database-creation utility, so that the new database includes the SELECT statement that refers to the AdminConfig table.

Using the inline table-valued function, now Adam can easily and efficiently perform the following queries:

SELECT * FROM GetAdminConfig(DEFAULT)GO   SELECT * FROM GetAdminConfig(N'db1')GODECLARE @db nvarchar(128)   SET @db = N'db1'SELECT * FROM GetAdminConfig(@db)  GO  SELECT * FROM GetAdminConfig(NULL)GOSELECT dbname, COUNT(*) AS cnt FROM GetAdminConfig(NULL) GROUP BY dbnameGOCREATE TABLE #config ( configname VARCHAR(30) NOT NULL, param VARCHAR(255) NOT   NULL )INSERT INTO #config VALUES('a', '%p1, %p2')  SELECT *    FROM #config AS c   JOIN GetAdminConfig(DEFAULT) AS g    ON g.configname = c.configname  DROP TABLE #config   GO

AUGUST READER CHALLENGE:

Now, test your SQL Server savvy in the August Reader Challenge, "Generating 6-Digit All-Numeric File Numbers for Search" (below). Submit your solution in an email message to [email protected] by July 20. 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.

Problem:

Roger is a database architect for a company that builds SQL Server 2005-based applications for law firms. One such application that generates case-file numbers and maintains client records. User requests from the application, such as adding a new case record, will trigger the generation of sequential, six-digit, all-numeric case file numbers, which the application's screen will display. Users can then search for a case or look up a particular record by using the generated case file number.

Help Roger design a CaseFiles table that fulfills the following requirements: