SQL Server 2008 R2 – Difference Between is_unique and is_unique_constraint Columns in sys.indexes

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.

Constraints and indexes are two entirely different things. A constraint defines what can go in the column and an index creates an internal structure to help performance.

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

This is part of the table/column definition. Probably the easiest and most common way to restrict a column from null values.

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

This is a constraint. This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

This is an index. As a side effect it is disallowing null but really it's purpose is is to speed up your queries.

I hate to say it but lazy isn't going to work. I would put null/not null definitions into the column definition. Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not. Then last but not least add indexes where needed for query performance.