Need field uniqueness but don't want index
in [Server Configuration]
|
Prev: Page Splits
Next: RPC vs. RPC Out for Linked Servers
From: OceanDeep via SQLMonster.com on 11 Feb 2010 09:14 I have three fields in a table that I need to make sure they are unique. I could create an unqiue contraint (index) for it but one of the three fields in there is to maintain the uniqueness and doesn't involve in any key join or search argurment and this field is often updated as well. Is there a way that I can maintain uniqueness without creating unique contraint (index)? OD -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1
From: TheSQLGuru on 11 Feb 2010 09:26 You can use a DML trigger to enforce uniqueness on the same table the trigger exists on. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a379eb7cd4b32(a)uwe... > I have three fields in a table that I need to make sure they are unique. > I > could create an unqiue contraint (index) for it but one of the three > fields > in there is to maintain the uniqueness and doesn't involve in any key join > or > search argurment and this field is often updated as well. Is there a way > that I can maintain uniqueness without creating unique contraint (index)? > > OD > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >
From: Uri Dimant on 11 Feb 2010 09:37 Or using popular IF NOT EXISTS () then INSERT......... "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:f96dnXYEwNsnj-nWnZ2dnUVZ_iydnZ2d(a)earthlink.com... > You can use a DML trigger to enforce uniqueness on the same table the > trigger exists on. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message > news:a379eb7cd4b32(a)uwe... >> I have three fields in a table that I need to make sure they are unique. >> I >> could create an unqiue contraint (index) for it but one of the three >> fields >> in there is to maintain the uniqueness and doesn't involve in any key >> join or >> search argurment and this field is often updated as well. Is there a way >> that I can maintain uniqueness without creating unique contraint (index)? >> >> OD >> >> -- >> Message posted via SQLMonster.com >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >> > >
From: Plamen Ratchev on 11 Feb 2010 10:03 You should test with creating UNIQUE constraint because this is the best way to enforce uniqueness. The overhead to maintain the index may be less than using triggers and code to enforce uniqueness. -- Plamen Ratchev http://www.SQLStudio.com
From: Jay on 11 Feb 2010 10:07
I have to ask why you want to do this without a unique index? My best guess is that you would like to avoid the overhead of the index. While both Kevin's and Uri's responses will work, they (and any other solution) will ultimately require a full table scan each time you want to insert, or update the field(s). So, one way, or another, you're going to get overhead. The cheapest still looks like a unique AK index. "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a379eb7cd4b32(a)uwe... > I have three fields in a table that I need to make sure they are unique. > I > could create an unqiue contraint (index) for it but one of the three > fields > in there is to maintain the uniqueness and doesn't involve in any key join > or > search argurment and this field is often updated as well. Is there a way > that I can maintain uniqueness without creating unique contraint (index)? > > OD > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 > |