SysIndex - difference b / w is_unique and is_unique_constraint

Question

running a select query on SQL Server 2008, sys.indexes gives me information about the index definition for the database.

There are 2 fields is_unique and is_unique_constraint. I do not understand the difference between the two.

Answer 1

Hope this simple demonstration makes you clearer. The index of table X will have both values, and the index in table Y will have only the value is_unique.

create table X (
    id int CONSTRAINT x_is_unique UNIQUE
)

create table Y (
    id int
)

create unique index y_is_unique on Y(id)

select name, is_unique, is_unique_constraint
    from sys.indexes
    where object_id in (object_id('X'), object_id('Y'))
        and name is not null

drop table X
drop table Y