Post reply

Can i include the same columns in unique and non-unique index?

  • June 17, 2005 at 3:42 am

    #60355

    Hello friends!

    I have one table having two primary keys here if i created one unique index and now i want to create another nonunique index taking another columns. now my questions are :

    1) can i take the columns which i already included in unique index, to build nonunique index?

    2) If i take these columns then what that cost on performance of query wheather time required to run query is more or less or no effect?

    T.I.A

    Shashank

     

    Regards,

    Papillon

  • June 17, 2005 at 6:24 am

    #566375

    The more indexes you add, the more it costs to maintain them. But if that costs is less than the cost of a slow query then it can be usefull.

    Can you post the table definition and some sample data along with the indexes you wish to create?

  • June 18, 2005 at 1:38 am

    #566590

    Hello friends!!

    I give you detail of my table and index

    CREATE TABLE TEST_EMP (

    SSN VARCHAR(9) NOT NULL,

    CCODE VARCHAR(4) NOT NULL,

    GRADE VARCHAR(2) NULL,

    CONSTRAINT GRADE_SSN_CCODE_PK

    PRIMARY KEY CLUSTERED (SSN, CCODE),

    EMPID VARCHAR(5) NOT NULL,

    EMPNAME VARCHAR(10))

    --------------------------------------------

    NOW I WANT TO ADD TWO COLUMNS MORE WHICH FREQUENTLY USED IN JOIN CONDITION for select & insert  i.e.

    AUTONUMBER int

    MODIFIEDDATE DATETIME

    NOW I WANT TO CREATE ANOTHER Index (IX_TEST_EMP)

    FOR COLUMNS

    SSN

    CCODE

    AUTONUMBER

    MODIFIEDDATE

    so including that two columns(SSN and CCODE) which already have index (GRADE_SSN_CCODE_PK),

    will that cause more cost on query or that doesnot matter?

    and if I exclude these two columns(SSN and CCODE) then wheather my select and insert clause

    will cause same cost as when i include these two columns(SSN,CCODE)?

    T.I.A

    Shashank

     

    Regards,

    Papillon

  • June 18, 2005 at 12:38 pm

    #566615

    SSN and CCODE are the primary key, and are therefore always included as a refference in the index.

    Also if autonumber and ModifiedDate are not used in the same where condition and or join, I see no point in making that a composite index.

    Futuremore, I don't see the use of the autonumber in this case. What were you planning to do with it?

  • June 20, 2005 at 12:33 am

    #566651

    Hey!

    I cleared all points regarding indexes

    Thanks Remi

    Shashank

     

    Regards,

    Papillon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply