70-432: Unique Constraints

Let’s cover another scenario that uses knowledge you’ll need in order to pass the 70-432.  This time we’re going to work with constraints.  We all know that you can declare a unique constraint on a column you where you want to be sure you won’t get any duplicates. But do you know how to deal with unique constraints an columns that allow null values?

Can you create a UNIQUE CONSTRAINT on a NVARCHAR(50) NULL column?

create table table1 (
 col1 nvarchar(50) NULL CONSTRAINT uc_col1 UNIQUE
)

Yup!  Just remember, you’re only going to be able to insert one row with a null value for col1.

Can you create a UNIQUE CONSTRAINT on a NVARCHAR(50) NOT NULL?

create table table1 (
 col1 nvarchar(50) NOT NULL CONSTRAINT uc_col1 UNIQUE
)

Of course!  Since you know every row will have a non-null value, you know you won’t even have to deal with nulls.

Can you create a UNIQUE CONSTRAINT on a NVARCHAR(50) SPARSE NULL column?

CREATE TABLE table1 (
 col1 NVARCHAR(50) SPARSE NULL CONSTRAINT uc_col1 UNIQUE
)

No, you can’t.  SPARSE columns by definition are optimized to store NULL values.  Meaning more than one.  Yeah, that means you can’t have a unique constraint on any SPARSE column, no matter what data type you choose.

If you attempt the query above you will get the following result:

Msg 1919, Level 16, State 2, Line 1
Column 'col1' in table 'table1' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Can you create a UNIQUE CONSTRAINT on a NVARCHAR(MAX) column?

CREATE TABLE table1 (
 col1 NVARCHAR(MAX) CONSTRAINT uc_col1 UNIQUE
)

No, you cannot.  If you try, you’ll get an error.

Msg 1919, Level 16, State 1, Line 1
Column 'col1' in table 'table1' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

The reason you can’t create a UNIQUE CONSTRAINT on a NVARCHAR (or VARCHAR) MAX field has to do with how the data for those datatypes are stored.  If you think back to SQL 2005 when the MAX option was first introduced for the variable text fields, it was created to replace the TEXT data types.

In VARCHAR(MAX) columns, when the length becomes too great (when the row length exceeds 8000 bytes), that data is stored outside the row.  Since the data may not be in stored in the table itself, you can’t index it properly.

Since UNIQUE CONSTRAINTS are built like an index, you won’t be able to create the UNIQUE CONSTRAINT.

 

Tags: ,

One Response to “70-432: Unique Constraints”

  1. Jeff Thomson 20120318 at 16:13 #

    Hi lowder,

    Really sounds good,

    Exact Examples
    Perfect Explanations,
    Highlighted Error Messages.

    Very nice.. Keep rocking…

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.