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

Question

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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?

Papillon Hall of Fame Points: 3303 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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?

Papillon Hall of Fame Points: 3303 More actions · Answer 4

Hey!

I cleared all points regarding indexes

Thanks Remi

Shashank

Regards,

Papillon