THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Defensive database programming: fun with changing column widths.

In Transact SQL you can assign a 10-character value to a VARCHAR(5) variable, as follows:

 

DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c

12345

 

The value will be silently truncated without raising an error. Because of this behavior it may be very easy to make mistakes. For example, consider the following table and stored procedure:

 

CREATE TABLE Data.Codes(

      Code VARCHAR(5),

      Description VARCHAR(40)

);

GO

CREATE PROCEDURE Readers.SelectCodes

  @Code VARCHAR(5)

AS

BEGIN

  SELECT Description FROM Data.Codes WHERE Code = @Code

END

GO

 

Although the procedure works correctly, it is very easy to render it incorrect by changing the table it selects from. Suppose that you have to increase the width of Code column, as follows:

 

ALTER TABLE Data.Codes ALTER COLUMN Code VARCHAR(10);

GO

 

If you have not adjusted the width of the corresponding parameter accordingly, then you have just introduced a new bug into your system. The procedure would fail to retrieve some rows. You can see for yourself:

 

INSERT INTO Data.Codes VALUES('1234567890', 'Tets data for boundary case');

EXEC Readers.SelectCodes @Code = '1234567890';

 

It easy to demonstrate that the procedure has failed to retrieve because your parameter has been silently truncated to 12345:

 

ALTER PROCEDURE Readers.SelectCodes

  @Code VARCHAR(5)

AS

BEGIN

  SELECT @Code AS PassedValue

  SELECT Description FROM Data.Codes WHERE Code = @Code

END

GO

EXEC Readers.SelectCodes @Code = '1234567890'

 

12345

 

There are several ways to limit your exposure to such problems. For instance, Oracle’s PL/SQL has built in %TYPE syntax which guarantees that the parameter’s type matches the type of the corresponding column, and apparently that feature is very popular with Oracle developers. Alternatively, some developers create their own types, but this approach seems to be unpopular.

 

Also there are a couple of suggestions on Connect requesting similar functionality in SQL Server.

 

Also to avoid such problems, SQL Server MVP Simon Sabin recommends using LINQ.


Anyway, if you want to improve the robustness of an existing system without a major overhaul, then you are stuck with your VARCHAR columns, because neither of these approaches would work for your existing tables and procedures.

This is one of those cases where boundary values testing proves to be very useful. Boundary values tests are supposed to verify that the procedure handles all the extreme cases, including the case when the code is of maximum allowed width. At the time when you are developing your stored procedure, create a boundary value test, as follows:

 

        [Test]

        private void SelectCodesMaxWidthTest()

        {

            string maxWidthCode = "12345";

            //verify that maxWidthCode is indeed a boundary value

            Assert.AreEqual(maxWidthCode.Length, GetColumnWidth("Data", "Codes", "Code"));

            //execute the procedure and check its results

 

 

That done, when you change the Code column’s width, the test will fail and remind you to change the procedure too.

 

Next post in the series:

Defensive database programming: eliminating IF statements.

Published Wednesday, November 19, 2008 2:34 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

reminds me somewhat of something me and a dev did a while back

I had a bit column and told him to pass in 1 or 0, somehow he passed in 1 or 2, meaning the result was always 1 :-(

declare @b bit

select @b =2

select @b

also reminds me of this

declare @v varchar(5)

select coalesce(@v, 'not available'),isnull(@v, 'not available')

you would be amazed at how many people fall into this trap by seeing a truncated message on their screen and then try to 'debug' this

November 19, 2008 3:01 PM
 

Madhivanan said:

November 21, 2008 6:01 AM
 

simple solution said:

declare varchar variables as nvarchar(max) in stored procs.  check the length in the code BEFORE it gets to the SQL server (as should always be the case).

November 25, 2008 11:14 AM
 

Alexander Kuznetsov said:

Oh yeah, that's simple. Also it can kill your performance.

Suppose you have a table SomeTable with a highly selective VARCHAR(10) column SomeColumn, and an index on that column. You can run the following script:

DECLARE @S1 NVARCHAR(MAX)

SET @s1 = N'SomeCode'

DECLARE @S2 VARCHAR(10)

SET @s2 = 'SomeCode'

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT <some columns> FROM SomeTable

WHERE SomeColumn = @s1

SELECT <some columns> FROM SomeTable

WHERE SomeColumn = @s2

You can also hit Ctrl+L and see that the first select scans the whole table while the second select uses the index if the condition is selective enough.

November 25, 2008 12:07 PM
 

Alexander Kuznetsov said:

The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN DO SOMETHING;

November 27, 2008 10:29 PM
 

Alexander Kuznetsov said:

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI

December 8, 2008 11:04 AM
 

Alexander Kuznetsov said:

Suppose that you need to implement the following logic: IF(row exists) Update the row ELSE Insert a new

December 14, 2008 8:37 PM
 

Alexander Kuznetsov said:

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.

January 25, 2009 5:57 PM
 

Alexander Kuznetsov said:

I have been posting examples of defensive database programming for some time now. I am by no means done

March 8, 2009 9:49 PM
 

Alexander Kuznetsov said:

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure

March 21, 2009 11:05 PM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

Alexander Kuznetsov said:

My query used to work, but it blows up after I have added an index? The following query is not safe:

July 11, 2009 11:14 PM
 

Alexander Kuznetsov said:

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order -

July 16, 2009 5:41 PM
 

Alexander Kuznetsov said:

Stored procedures using old-style error handling and savepoints may not work as intended when they are

October 9, 2009 4:42 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement