How do I use ALTER TABLE to ADD CONSTRAINT as an INDEX

Question

I would like to use the ALTER TABLE command to ADD CONSTRAINT. I do this often with no issues but in this case I need to add it as an INDEX as well.

From the Microsoft website it shows the following...

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count)}

<table_index> ::=
  INDEX index_name
{[ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [WITH ( COMPRESSION_DELAY = {0 | delay [Minutes]})]
      [ ON filegroup_name | default ]
}

I usually do something like the following for adding a primary key and it works great (EX#1):

ALTER TABLE [table_one]
ADD CONSTRAINT [PK_7_table_one] 
PRIMARY KEY CLUSTERED ([id] ASC) 
WITH(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) 
ON[PRIMARY];

The SQL Server Management Studio scripts the table using the following (EX#2):

CREATE UNIQUE NONCLUSTERED INDEX [IX_table_one]
ON [dbo].[table_one] ( [guid] ASC )
WITH (
    PAD_INDEX = OFF
    STATISTICS_NORECOMPUTE = OFF
    SORT_IN_TEMPDB = OFF
    IGNORE_DUP_KEY = OFF
    DROP_EXISTING = OFF
    ONLINE = OFF
    ALLOW_ROW_LOCKS = ON
    ALLOW_PAGE_LOCKS = ON
    OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY]

So at the moment I'm doing the following (EX#3):

ALTER TABLE [table_one]
ADD CONSTRAINT [IX_7_table_one_guid] 
UNIQUE NONCLUSTERED ([id] ASC) 
WITH(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) 
ON[PRIMARY];

However, I feel like I'm missing something. Like I need to have UNIQUE NONCLUSTERED INDEX ([id] ASC) (which I know will not work). How do I make it show "Index" like this?

The issue being, when I create my new constraint using the above method (EX#3) the results of the Type on the constraint is a Unique Key rather than an Index like the previous image.

In the end, my goal is to use something like EX#3 to create the constraint using ALTER TABLE while ending up with a Type of Index instead of Unique Key.

Answer 1

In the end, my goal is to use something like EX#3 to create the constraint using ALTER TABLE while ending up with a Type of Index instead of Unique Key.

This is not possible in the Syntax for disk-based tables.

You can use ALTER TABLE ADD <table_constraint> to add a unique or primary key constraint.

Both of these are backed by indexes and you can define a lot of the possible index options but not all (no included columns or filter predicates for example).

In order to get something that shows up as type "index" it either needs to have been created at the same time as the table (as CREATE TABLE does support defining inline indexes) or added subsequently with CREATE INDEX.