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:

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().