Converting a Hexadecimal String to a Varbinary Value

Congratulations to John T. Keller, who won first prize of $100 for the best solution to the December Reader Challenge, "Improve T-SQL to Prevent Deadlocks" and to Bill Twomey who won second prize of $50. John is a database developer for Softek Solutions, and Bill is a Microsoft SQL Server DBA for Database Solutions. Here's a recap of the problem and the solution to the December Reader Challenge.

Problem:

Manoj is a database developer for a company that develops business applications that use SQL Server 2000 as a database server. In Manoj's environment, the database that the applications use contains a table that stores sequential numbers used by various features. The table stores the name of the sequence and the next number. The schema of the table is shown in the following code:

USE tempdb
GO
CREATE TABLE dbo.Sequences ( SeqName varchar(30) NOT NULL PRIMARY KEY, NextNum 
bigint NOT NULL DEFAULT 0)
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #1', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #2', DEFAULT )
INSERT INTO dbo.Sequences (SeqName, NextNum) VALUES( 'Sequence #3', DEFAULT )
GO

The database also contains a GetNextSeqNum stored procedure, which the applications use to get the next number in a particular sequence. The stored procedure is shown in the following code:

USE tempdb
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName varchar(30), @NextNum bigint = 
NULL OUTPUT)
AS
BEGIN
    BEGIN TRANSACTION
    SET @NextNum = (SELECT NextNum FROM dbo.Sequences WHERE SeqName = @SeqName)
    UPDATE dbo.Sequences
    SET NextNum = NextNum + 1
    WHERE SeqName = @SeqName
    COMMIT TRANSACTION
END
GO

When Manoj tests the stored procedure with concurrent SQL Server connections, he notices that some of the calls encounter deadlocks and fail. Modify the stored procedure logic to help Manoj resolve the deadlock problem.

Solution:

Manoj realizes that the stored procedure logic that contains the SELECT and UPDATE statements is flawed because concurrent calls are made for the same sequence. When the SELECT statements from different connections acquire the shared lock on the sequence row, the connections will get into a deadlock state because they try to update the same row. One way to eliminate the deadlock would be to serialize access to the sequence table. Alternatively, Manoj can use the T-SQL UPDATE statement extension to get the next sequence number and increment the column in a single statement. The modified stored procedure is shown in the following code:

USE tempdb
GO
IF object_id('dbo.GetNextSeqNum') IS NOT NULL
 DROP PROCEDURE dbo.GetNextSeqNum
GO
CREATE PROCEDURE dbo.GetNextSeqNum (@SeqName VARCHAR(30), @NextNum bigint = 
NULL OUTPUT)
AS
BEGIN
    UPDATE dbo.Sequences
    SET @NextNum = NextNum, NextNum = NextNum + 1
    WHERE SeqName = @SeqName
END
GO

Now, the UPDATE statement ensures that only one connection can update the row and the other concurrent calls will be blocked. This revision will prevent the deadlock and provide better performance because the entire operation in the stored procedure is performed in a single statement.

JANUARY READER CHALLENGE:

Now, test your SQL Server savvy in the January Reader Challenge, "Parse and Insert Data from Uploaded Image Files" (below). Submit your solution in an email message to [email protected] by December 21. 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:

Bart is a DBA who maintains several data warehouses, which are hosted on SQL Server 2000. One data warehouse has a table called ExtFeeds that periodically receives data as flat files loaded from an external source. One of the columns in the flat file has a hexadecimal string value, ranging from 4 to 32 bytes, which is stored in the ExtFeeds table’s DataHdr column. The external source can't be modified easily, and other applications rely on the DataHdr values to be strings, so Bart can't manipulate the value or convert to binary data. Instead he loads the files directly into the table by using the bcp utility.

However, Bart needs to periodically write queries against the ExtFeeds table that involve accessing the DataHdr column. The queries perform various operations such as converting the hexadecimal string value to varbinary and joining the ExtFeeds table with other tables.

Help Bart design a solution that helps convert the hexadecimal strings to varbinary values. He would need to perform the conversion in the SELECT list of queries, in the logic in stored procedures, and in INSERT and UPDATE statements. The solution should be able to support values ranging from 4 to 32 bytes and perform efficient