making columns unique

We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
There's no big difference between a unique index and a unique constraint.
When you create the unique constraint, a unique index gets created in the
background. However, if you want a foreign key to use that table, you must
use a primary key or unique constraint - a unique index by itself isn't
sufficient.
The above are sufficient to enforce uniqueness. There is no reason to use a
trigger here. Also, don't worry about the size of the table. Much large
tables have been built without problems.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
|||I would not use triggers. Triggers have more overhead to OLTP applications.
Unique constraint should take care of the issue. 3 column 6 columns should
not be a bit concern with the size/groth you are looking for.
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
> We have a table in our database that has 6 columns that as a whole are not
> indexed together however we would like for them to be unique. Would it be
> more efficent or better to create a unique constraint...or a unique
> index...or to create a insert trigger that would perform a look up for a
> record with the same values you are wanting to insert. It would do this
> using an index on 3 of the 6 fields that would not always narrow it down
to
> one row. I am asking this because there is some concern in the office
that
> adding a 6 column index on this table would add a good bit of burdern and
> adding 3 columns to the existing index wouldn't be a whole lot better.
This
> table could contain 100s of 1000s of records. What would you recommend.
> Thanks,
> Brandon
>