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: SET vs. SELECT.

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before. Assigning multiple values via SELECT performs better, and you don’t have to repeat your code several times, as described by Tony Rogerson here and here. However, if you have ambiguities, SET will raise an error, but SELECT will not detect them, unless you use a smart trick described by Adam Machanic. Suppose, however, that you have a subquery that cannot have ambiguities at the time when you are developing it. For example, consider the following table and script:

 

USE Test

GO

DROP TABLE data.Customers;

GO

/****** Object:  Table data.Customers    Script Date: 01/25/2009 11:00:31 ******/

CREATE TABLE Data.Customers(

      CustomerId int NOT NULL,

      FirstName varchar(50) NOT NULL,

      LastName varchar(50) NOT NULL,

      PhoneNumber varchar(50) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY(CustomerId),

      CONSTRAINT UNQ_Customers UNIQUE(PhoneNumber)

)

GO

INSERT INTO data.Customers(

      CustomerId,

      FirstName,

      LastName,

      PhoneNumber)

SELECT 1, 'Darrel', 'Ling', '(123)456-7890' UNION ALL

SELECT 2, 'Peter', 'Hansen', '(234)123-4567';

GO

DECLARE     @FirstName varchar(50),

      @LastName varchar(50),

      @PhoneNumber varchar(50);

 

SET @PhoneNumber = '(123)456-7890';

 

-- You can assign one variable at a time using SET:

 

SET @LastName = (SELECT LastName FROM Data.Customers WHERE PhoneNumber = @PhoneNumber);

SET @FirstName = (SELECT FirstName FROM Data.Customers WHERE PhoneNumber = @PhoneNumber);

 

-- But assigning both variables in one SELECT is faster

-- and you don't duplicate your code:

 

SELECT @LastName = LastName,

       @FirstName = FirstName

FROM Data.Customers

WHERE PhoneNumber = @PhoneNumber;

GO

 

 

At the time this code is being developed, ambiguities are impossible – the uniqueness of condition WHERE PhoneNumber = @PhoneNumber is guaranteed by the UNIQUE constraint UNQ_Customers. In this case assigning both variables via a single SELECT is simpler and performs better; there is no reason to use SETs. If later on the uniqueness is no longer guaranteed, the code will no longer be safe, but right now there is no need to complicate your query just in case. However, if you decide to use a single SELECT, you actually made an assumption – you assumed that the condition WHERE PhoneNumber = @PhoneNumber guarantees uniqueness. As long as this assumption is true, your code is perfectly safe to use. Yet you need to document your assumption – a unit test is a very good way to document it. You can run the following two queries and make sure that they both return 1:

 

-- there is a UNIQUE or PK constraint on PhoneNumber

SELECT COUNT(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u

WHERE u.TABLE_NAME='Customers'

  AND u.TABLE_SCHEMA='Data'

  AND u.CONSTRAINT_NAME='UNQ_Customers'

  AND u.COLUMN_NAME='PhoneNumber'

  AND (SELECT CONSTRAINT_TYPE

            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

            WHERE TABLE_NAME='Customers'

            AND TABLE_SCHEMA='Data'

            AND CONSTRAINT_NAME='UNQ_Customers') IN ('PRIMARY KEY', 'UNIQUE');

 

-- This constraint does not include other columns

SELECT COUNT(*) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE TABLE_NAME='Customers'

  AND TABLE_SCHEMA='Data'

  AND CONSTRAINT_NAME='UNQ_Customers';

 

 

Suppose that at some time later on the database schema has changed, as follows:

 

ALTER TABLE Data.Customers ADD CountryCode VARCHAR(5) NOT NULL

  CONSTRAINT DF_Customers_CountryCode DEFAULT('US');

 

ALTER TABLE Data.Customers DROP CONSTRAINT UNQ_Customers;

ALTER TABLE Data.Customers ADD CONSTRAINT UNQ_Customers UNIQUE(PhoneNumber, CountryCode);

 

 

After this change phone number alone no longer uniquely identifies a customer, and your unit test will fail. The failed unit test will alert you to adjust your code to fit the changed database schema. For instance, you can add a condition on CountryCode to ensure uniqueness, as follows:

 

-- add some ambiguity: a person from another country

-- with the same phone number

INSERT INTO data.Customers(

      CustomerId,

      FirstName,

      LastName,

      PhoneNumber,

    CountryCode)

SELECT 3, 'Drew', 'Magnusson', '(123)456-7890', 'UK';

 

 

DECLARE     @FirstName varchar(50),

      @LastName varchar(50),

      @PhoneNumber varchar(50),

      @CountryCode VARCHAR(5);

 

SELECT @PhoneNumber = '(123)456-7890', @CountryCode = 'US';

 

SELECT @LastName = LastName,

       @FirstName = FirstName

FROM Data.Customers

WHERE PhoneNumber = @PhoneNumber

AND CountryCode = @CountryCode;

 

 

Alternatively, you can use a trick described by Adam Machanic, so that you get an exception whenever there is an ambiguity.

 

 

This post continues my series on defensive database programming. My next post:

Summarizing previous posts about defensive database programming

Here are the previous posts  from the series:

 

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().

Published Sunday, January 25, 2009 1:40 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

 

Uri Dimant said:

Hi Alex

I like also reading

http://vyaskn.tripod.com/differences_between_set_and_select.htm

See the use of the system variables @@ERROR and @@ROWCOUNT

January 26, 2009 12:57 AM
 

Alexander Kuznetsov said:

Hi Uri,

Yes, that's another nice article on the same popular topic. Thank you for pointing it out.

January 27, 2009 3:40 PM
 

Vladimir said:

Change first last name, please! =)

January 28, 2009 6:05 AM
 

Alexander Kuznetsov said:

Vladimir,

I do not understand your comment.

January 28, 2009 9:36 AM
 

Vladimir said:

Darrel Hui, LOL

January 29, 2009 5:49 AM
 

Alexander Kuznetsov said:

Vladimir,

Thank you for pointing it out. Fixed.

January 29, 2009 9:18 AM
 

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 : Stress testing UPSERTs said:

October 9, 2009 4:47 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