Can i include the same columns in unique and non-unique indexes!!
-
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
-
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
under2811 wrote: I have one table having two primary keys You cannot have two primary keys. Unless you mean that you have a primary key with two columns. You can have as many unique and non-unique indexes as you wish using what ever combination of columns you wish (including using the same column in two, or more, indexes). As I recall, there is a maximum of 16 columns per index. under2811 wrote: 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? The only impact on performance by adding extra indexes is when you do an INSERT, UPDATE and DELETE commands because these will result in the indexes changing. Obviously SELECT statements get faster because they can use the indexes to get at the data they need more efficiently.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
under2811 wrote: I have one table having two primary keys You cannot have two primary keys. Unless you mean that you have a primary key with two columns. You can have as many unique and non-unique indexes as you wish using what ever combination of columns you wish (including using the same column in two, or more, indexes). As I recall, there is a maximum of 16 columns per index. under2811 wrote: 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? The only impact on performance by adding extra indexes is when you do an INSERT, UPDATE and DELETE commands because these will result in the indexes changing. Obviously SELECT statements get faster because they can use the indexes to get at the data they need more efficiently.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Hello friends!! Here I am giving you some details of table and indexes 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