1

Is there a functional difference between the tables built in the following two ways?

CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD UNIQUE NONCLUSTERED(f1)

vs.

CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD CONSTRAINT UQ_f1 UNIQUE (f1);

I believe they're both implementing a unique constraint with a corresponding non-clustered index on the [f1] column but given the different syntax (both in creating and if you script the builds afterwards from SSMS) I'm wondering if one is different (or better?) somehow. Thanks for any feedback.

3

Tested - see picture below (renamed table in the second example to test_uq_2)

Creating unique contraints

Not much difference really, but - in my opinion, second example is better, because it gives you the ability to name the constraint/index, while in the first example SQL Server names it automatically

1
  • 1
    Thanks and agree on the naming capability. I was principally interested in knowing whether there was any functional difference, which there appears not to be.
    – MinnRick
    Mar 3 at 22:54
1

I prefer to create it as an index for 1) the explicit naming and 2) ability to ignore NULLs or use other filter conditions:

CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX uq_test ON test_uq(f1) WHERE f1 IS NOT NULL;
1

Consider the third variant:

CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL CONSTRAINT UQ_f1 UNIQUE
) ON [PRIMARY]
GO

Naming the constraint is a necessity when using VS Database Projects (DACPACs). If not named, the constraint gets rebuilt with every deployment because the name changes.

That said, a UNIQUE constraint on a NULLable column is not a good idea. NULLs count for uniqueness and more than one will bomb an INSERT or UPDATE.

2
  • 1
    Absolutely correct and agree. Neglected that when I whipped up the sample code.
    – MinnRick
    Mar 3 at 22:51
  • @MinnRick, added another reason for naming constraints to the answer.
    – Metaphor
    Mar 4 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.