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,
BrandonThere'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
>sql