SQL Server Performance

definition ofindexes

Discussion in 'General DBA Questions' started by sqlfn, Apr 20, 2006.

  1. sqlfn New Member

    i have a table with :
    Prim_key
    Foreign_key1
    Foreign_key2
    and otherdata

    foreign_key1 is filled in if foreign_key2 is empty
    foreign_key2 is filled in if foreign_ket1 is empty

    Can I put an non-unique index on foreign_key1 : this will only take the filled in values of foreign_key1 in the index. Does is work the same way in sql-server and in other database like oracle,informix ?

    Can I put an non-unique index on Prim_key + Foreign_key1 ?what will it containt in the index? only the entries where Foreign_key1 is filled in

    Can I put n unique index in Prim_key+ Foreign_key1 ?
    Or must make asupplementary foreign_key3 which contains either foreign_key1 or foreign_key2,due tothe fact than one of the 2foreignkeys is filled in


    thanks.

  2. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Can I put an non-unique index on foreign_key1 : this will only take the filled in values of foreign_key1 in the index. Does is work the same way in sql-server and in other database like oracle,informix ? <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes, you can put an index on foreign_key1 but that will contain an entry for each row in the corresponding table. That might mean that there are a lot of NULL markers present. I think SQL Server differs here from Oracle (and even Access)<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Can I put an non-unique index on Prim_key + Foreign_key1 ?what will it containt in the index? only the entries where Foreign_key1 is filled in <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Again, yes you can. This index will contain an entry for each PRIMARY KEY in the underlying table and in addition entries distinct from NULL when there is an actual value for Foreign_Key1.<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Can I put n unique index in Prim_key+ Foreign_key1 ?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes! But it makes no sense since Prim_key alone already enforces uniqueness.<br /><br />Maybe you should describe what you are actually trying to achieve? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Share This Page