Generating 6-Digit All-Numeric File Numbers for Search - 10 Aug 2006

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:

1. Generates unique numeric values between 1000 and 999,999 that have six digits with leading zeroes. 2. 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.

Solution:

Roger can use an identity column as part of the CaseFiles table definition to create an automatic sequential number generator that starts with 1000. He can enforce a constraint so that the generated value doesn't exceed 999999. Now, he can use the identity column in a computed column to generate the user-friendly 6-digit display number. Additionally, he can create a primary key constraint on the computed column to enforce uniqueness. Because the primary key constraint is enforced by using a unique index, searches on the six-digit file number will be efficient.

Roger can create the CaseFiles table by usingthe schema in the following code example (only relevant columns are present):

CREATE TABLE CaseFiles (
fileid INT IDENTITY(1000, 1) NOT NULL CHECK(fileid BETWEEN 1000 AND 999999),
filenum AS ISMULLl(RIGHT(REPLICATE('0', 6) + CAST(fileid AS VARCHAR), 6), '),
CONSTRAINT PK_CaseFiles_Num PRIMARY KEY (filenum)
)
INSERT INTO CaseFiles DEFAULT VALUES
GO
-- Show use of index on searches based on the file number value:
SET showplan_text ON
GO
SELECT * FROM CaseFiles WHERE filenum = '001000'
SELECT * FROM CaseFiles WHERE filenum IN ( '001000', ‘002900’, ‘109494’)
GO
SET showplan_text OFF
GO

DROP TABLE CaseFiles
GO

SEPTEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the September Reader Challenge, "Fixing a Faulty Lookup Query" (below). Submit your solution in an email message to [email protected] by August 17. 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:
Jeff is a database developer who needs to develop an application that keeps track of Unicode strings in a database table. The Unicode strings are 40 characters long and include trailing blanks, which are significant for comparison purposes when looking up specific values in the table. Jeff uses the pubs sample database and creates the test table and data by using the following T-SQL batches:

USE pubs
GO
SELECT identity(int) AS id, CAST(au_lname AS nvarchar(40)) AS name1, CAST
(au_fname AS nvarchar(20)) AS name2
INTO _a
FROM dbo.authors
GO
CREATE CLUSTERED INDEX idx__a_name ON _a(name1)
GO
INSERT INTO _a VALUES(N'Test Name 1 ', N'Test Name 1')
INSERT INTO _a VALUES(N'Test Name 1', N'Test Name 1')
INSERT INTO _a SELECT a1.name1, a1.name2 FROM _a a1, _a a2
GO

To test how he can look up specific items using the name1 column from the table, Jeff writes the following simple query:

DECLARE @n nvarchar(40)
SET @n = N'Test Name 1 '
SELECT *, datalength(name1) FROM _a
WHERE name1 = @n
GO

But the query doesn't give the results he expected; Jeff gets the entries with and without trailing blanks. For the sample data shown, the query returns rows that have name1 as Test Name 1 without a trailing blank and Test Name 1 with a trailing blank. However, Jeff expects the equality operator to perform an exact match thereby returning only the row with name1 as Test Name 1.

What is the flaw in the query? Why doesn't the equality operator consider the blanks significant? And how can Jeff modify the query so that he gets the expected results efficiently?