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 11:01 Say I have field A, B, C. A and B are the ones being used in Key join and where-clause and I would create an index for it. Field C combined with B and C is to make the row unique and field C is frequently updated. With this in mind, will unique index (contraint) on all three fields be still more efficient (less overhead) as trigger ((have an index on A and B only and use trigger to maintain the uniqueness) or other ways? od Jay wrote: >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. > >> I have three fields in a table that I need to make sure they are unique. >> I >[quoted text clipped - 6 lines] >> >> OD -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1
From: Jay on 11 Feb 2010 12:07 If A & B are the key, then by definition, they must be unique. If, however, C is required to make the value unique, then A, B & C would be the key. Is your PK column A & B and you also have column C, which must be unique to the A & B combination, but could also exist for a different A & B? Or, must the columns A & B, as well as A, B & C be unique in the entire table, the difference being that you only want A & B to be in any FK's? "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a37adbb735f48(a)uwe... > Say I have field A, B, C. A and B are the ones being used in Key join and > where-clause and I would create an index for it. Field C combined with B > and > C is to make the row unique and field C is frequently updated. With this > in > mind, will unique index (contraint) on all three fields be still more > efficient (less overhead) as trigger ((have an index on A and B only and > use > trigger to maintain the uniqueness) or other ways? > > od > > Jay wrote: >>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. >> >>> I have three fields in a table that I need to make sure they are unique. >>> I >>[quoted text clipped - 6 lines] >>> >>> OD > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >
From: Tom Cooper on 11 Feb 2010 12:14 It's hard to say which would be more efficient. Even if one of us ran a test, it wouldn't necessarily apply to your situation because, as always, YMMV. My guess is that the performance will be close either way. Given that, I absolutely would go with the unique index. It's easy and quick to implement. Also, unique constraints are ALWAYS enforced. If you use a trigger, there are ways to insert rows where the trigger doesn't fire. So I would definitely go with the unique index. That's easy and quick to implement. Then, if your testing indicates you have performance problems, and that this index is the cause, only then try the trigger. I suspect, though, that if you have performance problems, it won't be helped significantly by using a trigger, and you will have to look elsewhere for the performance fix. Tom "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a37adbb735f48(a)uwe... > Say I have field A, B, C. A and B are the ones being used in Key join and > where-clause and I would create an index for it. Field C combined with B > and > C is to make the row unique and field C is frequently updated. With this > in > mind, will unique index (contraint) on all three fields be still more > efficient (less overhead) as trigger ((have an index on A and B only and > use > trigger to maintain the uniqueness) or other ways? > > od > > Jay wrote: >>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. >> >>> I have three fields in a table that I need to make sure they are unique. >>> I >>[quoted text clipped - 6 lines] >>> >>> OD > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >
From: Jay on 11 Feb 2010 12:15 Or, does column C have to be unique in the entire table, as well as A & B? "Jay" <spam(a)nospam.org> wrote in message news:OpQyryzqKHA.3800(a)TK2MSFTNGP06.phx.gbl... > If A & B are the key, then by definition, they must be unique. If, > however, C is required to make the value unique, then A, B & C would be > the key. > > Is your PK column A & B and you also have column C, which must be unique > to the A & B combination, but could also exist for a different A & B? > > Or, must the columns A & B, as well as A, B & C be unique in the entire > table, the difference being that you only want A & B to be in any FK's? > > "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message > news:a37adbb735f48(a)uwe... >> Say I have field A, B, C. A and B are the ones being used in Key join >> and >> where-clause and I would create an index for it. Field C combined with B >> and >> C is to make the row unique and field C is frequently updated. With >> this in >> mind, will unique index (contraint) on all three fields be still more >> efficient (less overhead) as trigger ((have an index on A and B only and >> use >> trigger to maintain the uniqueness) or other ways? >> >> od >> >> Jay wrote: >>>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. >>> >>>> I have three fields in a table that I need to make sure they are >>>> unique. >>>> I >>>[quoted text clipped - 6 lines] >>>> >>>> OD >> >> -- >> Message posted via SQLMonster.com >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >> > >
From: Jay on 11 Feb 2010 13:40
+1 "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:OIzdJ3zqKHA.5036(a)TK2MSFTNGP02.phx.gbl... > It's hard to say which would be more efficient. Even if one of us ran a > test, it wouldn't necessarily apply to your situation because, as always, > YMMV. My guess is that the performance will be close either way. Given > that, I absolutely would go with the unique index. It's easy and quick to > implement. Also, unique constraints are ALWAYS enforced. If you use a > trigger, there are ways to insert rows where the trigger doesn't fire. > > So I would definitely go with the unique index. That's easy and quick to > implement. Then, if your testing indicates you have performance problems, > and that this index is the cause, only then try the trigger. I suspect, > though, that if you have performance problems, it won't be helped > significantly by using a trigger, and you will have to look elsewhere for > the performance fix. > > Tom > > "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message > news:a37adbb735f48(a)uwe... >> Say I have field A, B, C. A and B are the ones being used in Key join >> and >> where-clause and I would create an index for it. Field C combined with B >> and >> C is to make the row unique and field C is frequently updated. With >> this in >> mind, will unique index (contraint) on all three fields be still more >> efficient (less overhead) as trigger ((have an index on A and B only and >> use >> trigger to maintain the uniqueness) or other ways? >> >> od >> >> Jay wrote: >>>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. >>> >>>> I have three fields in a table that I need to make sure they are >>>> unique. >>>> I >>>[quoted text clipped - 6 lines] >>>> >>>> OD >> >> -- >> Message posted via SQLMonster.com >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >> > |