Congratulations to Chris Hedgate and Marcos Kirchner. Chris Hedgate won first prize of $100 for the best solution to the March Reader Challenge, "Enforcing Restricted Uniqueness on Columns." Marcos won second prize of $50. Here's a recap of the problem and the solution to the March Reader Challenge.
Related: Querying Database Tables and Views and Linked Servers
Problem
John is a database architect for a company that uses a sales application populated with customer data from various data sources. The customer data is stored in a SQL Server 2000 database table called Customers. The following column shows some of the columns in the table:
CREATE TABLE Customers ( CustomerId int identity(1,1) NOT NULL primary key, CompanyCode varchar(10) NULL /* ... other columns ...*/ )
The CompanyCode column isn’t always present in the data that SQL Server imports into the table. In some cases, the value can be empty or NULL. John wants to enforce uniqueness on the CompanyCode column—but only for non-null values—and exclude empty values. How can John enforce uniqueness with minimal coding and without affecting the legacy applications that provide the customer data?
Solution
John can’t define a unique constraint on the CompanyCode column because SQL Server doesn’t allow multiple NULL values or multiple empty values. He can enforce the uniqueness programmatically by using a trigger, but this technique requires modifying bulk insert processes so that they will fire the trigger. This technique can affect the insert/update operations’ performance. SQL Server 2000 has a feature called indexed view, which lets users create indexes on certain views. Once an indexed view is created, any changes to the base table will result in automatic maintenance of the indexes on the view. John can leverage this functionality to create a view that selects non-null and non-empty CompanyCode values from the Customers table. The following code shows the view definition:
CREATE VIEW Customers_CompanyCodes WITH SCHEMABINDING AS SELECT CompanyCode FROM dbo.Customers WHERE CompanyCode IS NOT NULL AND CompanyCode '
John can also create a unique clustered index on the CompanyCode column of the Customers_CompanyCodes view. This action indirectly creates a constraint that enforces uniqueness on the CompanyCode values in the Customers table. The script below shows how the enforcement of the uniqueness property on the indexed view prevents bad data from appearing in the Customers table:
CREATE UNIQUE CLUSTERED INDEX idx_Customers_CompanyCode
ON Customers_CompanyCodes(CompanyCode)
GO
-- Succeeds
INSERT INTO Customers(CompanyCode) VALUES(NULL)
INSERT INTO Customers(CompanyCode) VALUES(NULL)
INSERT INTO Customers(CompanyCode) VALUES(')
INSERT INTO Customers(CompanyCode) VALUES(')
INSERT INTO Customers(CompanyCode) VALUES('A')
-- Fails
INSERT INTO Customers(companycode) VALUES('A')
GO
-- Succeeds
UPDATE Customers
SET companycode = NULL
WHERE companycode = '
GO
-- Fails
UPDATE Customers
SET companycode = 'A'
WHERE companycode IS NULL
GO
APRIL READER CHALLENGE:
Now, test your SQL Server savvy in the April Reader Challenge, "Querying Tables and Views on a Linked Server" (below). Submit your solution in an email message to [email protected] by March 16. 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
Michael is a database consultant who’s working on a project at a client’s site. He needs to create a SQL Server 2000 data warehouse that integrates data from the company’s other databases. Michael plans to use the linked server feature to write some of the scripts that will migrate the data and perform distributed queries.
One data source that he must integrate is an IBM DB2 database. He used the IBM OLE DB Provider for DB2 to create a linked server called DB2_SRVR1 for the database. There are certain tables and views that he wants to query from the DB2 server on an ad hoc basis or use in his scripts, but when he tries to query one such table using the following four-part name
SELECT * FROM DB2_SRVR1.DB_CAT.SALES.SALES_SUMMARY
he gets the following error
Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "IBMDADB2" for linked server "DB2_SRVR1" does not contain the table ""DB_CAT"."SALES"."SALES_SUMMARY"". The table either does not exist or the current user does not have permissions on that table.
Help Michael do the following:
1. Find the list of tables or views as exposed by the data source that he can easily query from SQL Server on a linked server
2. Quickly determine the correct way to specify the four-part name for the DB2 table (i.e., the catalog and schema part of the four-part name)
