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:
- The query mechanism should be efficient and easy to maintain.
- The query mechanism should let Adam easily query the AdminConfig table on each hosted database.
- The database name must pass as a parameter.
- If the database name is NULL, the query should return the AdminConfig-table data from all the hosted databases.
- Adam should be able to use a SELECT statement to join the query results with other tables.
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 dbmaster
GO
SET showplan_text ON
GO
SELECT * 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
GO
DECLARE @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')
GO
DECLARE @db nvarchar(128)
SET @db = N'db1'
SELECT * FROM GetAdminConfig(@db)
GO
SELECT * FROM GetAdminConfig(NULL)
GO
SELECT dbname, COUNT(*) AS cnt FROM GetAdminConfig(NULL) GROUP BY dbname
GO
CREATE 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:
- Generates unique numeric values between 1000 and 999,999 that have six digits with leading zeroes.
- Employs the six-digit file number column as the primary search mechanism so that any queries on the table that use a specific file number or set of file numbers will be executed in the most efficient manner possible.
