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:

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:

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' )

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.

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:

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.