Congratulations to Yaniv Mor, a DBA for Toyota Financial Services in Milsons Point, NSW, Australia. Yaniv won first prize of $100 for the best solution to the September Reader Challenge, "Foreign Punctuation." Here's a recap of the problem and the solution to the September Reader Challenge.
David is a database programmer for an advertising company that deals with international data. The database that holds data for each customer contains a table that stores names in Unicode format. The script below contains the table and sample data:
CREATE TABLE #Temp ( nc nvarchar(30) COLLATE Latin1_General_CI_AS ) CREATE CLUSTERED INDEX Idx_Temp_nc on #Temp( nc ) INSERT INTO #Temp VALUES( N'MRKT' + nchar(0x3000) + N'Name') INSERT INTO #Temp VALUES( N'TEST String' ) INSERT INTO #Temp VALUES( N'MRKT' + nchar(0x3000) + N'Name' + nchar(0x3000) )
In the sample data, the Unicode character 0x3000 represents a punctuation symbol in languages such as Chinese. While writing a query using an equality search condition, David notices that it doesn't work as expected. For example, the query below returns two rows instead of one:
SELECT * FROM #Temp WHERE nc = N'MRKT' + nchar(0x3000) + N'Name' + nchar(0x3000)
What is incorrect in the query? And how can David modify the query to return the correct data without ignoring punctuation or whitespace characters in any language?
David knows that a Unicode character, such as 0x3000, represents a punctuation symbol or whitespace in languages like Chinese, Japanese, or Korean. The equality operator, by default, ignores trailing whitespace and thus returns two rows for the sample query. David can perform the search by using the LIKE operator instead of the = (equals) operator. When performing string comparisons, the LIKE operator considers trailing and leading spaces and doesn't ignore the punctuation symbol in the Unicode data. The following query uses the LIKE operator to consider all trailing characters for comparison purposes and return the correct row:
SELECT * FROM #Temp WHERE nc LIKE N'MRKT' + nchar(0x3000) + N'Name' + nchar(0x3000)
To perform his searches, David can also use a width-sensitive collation, such as Latin1_General_CI_AS_WS, for the column. However, this approach requires him to modify his schema. Another option is for David to define a computed column for the column with a width-sensitive COLLATE clause and index that column instead. But, this approach only works with SQL Server 2000 Service Pack 2 (SP2) or later, which lets you build indexes on computed columns with the COLLATE clause.
OCTOBER READER CHALLENGE:
Now, test your SQL Server savvy in the October Reader Challenge, "Optimizing a Stored Procedure" (below). Submit your solution in an email message to [email protected] by September 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.
Tom, the database developer for a company that sells its products online, has a database that contains customer information and order details. For simplicity, assume that the database is similar to SQL Server 2000's Northwind sample database. The following script creates the sample data that we can use to solve Tom's problem:
USE Northwind GO SELECT * INTO _Customers FROM Customers SELECT * INTO _Orders FROM Orders ALTER TABLE _Customers ADD PRIMARY KEY(CustomerId) ALTER TABLE _Orders ADD PRIMARY KEY(OrderId), FOREIGN KEY (CustomerId) REFERENCES _Customers(CustomerId) GO
Tom wants to optimize the stored procedure ProcessCustomerOrders, which takes an XML stream and processes the customer orders. The Web application code invokes the stored procedure, which performs the following actions:
1. Adds new customers
2. Adds new orders for customers
3. Updates contact information for existing customers
4. Updates shipping details for existing orders
Listing 1 shows how the ProcessCustomerOrders stored procedure uses the sample tables. The Web application can contain any number of orders (up to a 100-order maximum) when it invokes the stored procedure. While monitoring database performance under heavy load conditions, Tom notices that the connections that call the stored procedure consume a lot of memory on the server. He also notices that under heavy-stress conditions, some of the calls fail, which indicates that the Web application couldn't successfully create the XML document.
Tom needs to improve the stored procedure's overall performance and retain the XML input capability. How can Tom modify the stored procedure to avoid the memory consumption problem? And should Tom check any environment-related problems to prevent the failed calls from happening frequently?