SQL Server – Unique Constraint Syntax Explained

nonclustered-indexsql servert-sqlunique-constraint

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.

Best Answer

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

Related Question