Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

unique clustered index on 2 columns Expand / Collapse
Author
Message
Posted Sunday, December 12, 2010 1:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 5:55 AM
Points: 19, Visits: 69

Hi

i have read that only one unique clustered index can be created on a table and many non clustered index

then how come the below commands works fine as it is creating a unique clustered index on 2 columns of a table


create table test4 (id int,pid int,pname char(25))

go

create unique clustered index indx_test on test4 (id, pid) --
if the above case is right then how many columns can we include in a clustered index.???

Post #1033436
Posted Sunday, December 12, 2010 1:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 5:55 AM
Points: 19, Visits: 69
as mentioned above i have inserted below records


insert into test4 values (1,1,'asd')
go
insert into test4 values (2,1,'asd')

the command executed successfully

my query is that as i have included first two columns in the unique clustered index then how can it accepts duplicates in column 2 ..the what is the use of defining column 2 in unique clustered index ?????????
Post #1033437
Posted Sunday, December 12, 2010 2:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:18 AM
Points: 1,095, Visits: 6,669
ahmed7.bi (12/12/2010)

Hi

i have read that only one unique clustered index can be created on a table and many non clustered index

then how come the below commands works fine as it is creating a unique clustered index on 2 columns of a table


create table test4 (id int,pid int,pname char(25))

go

create unique clustered index indx_test on test4 (id, pid) --
if the above case is right then how many columns can we include in a clustered index.???



From BOL section "Clustered Index Design Guidelines" -
"Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order."
and -
"Generally, you should define the clustered index key with as few columns as possible."



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1033444
Posted Sunday, December 12, 2010 2:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:18 AM
Points: 1,095, Visits: 6,669
ahmed7.bi (12/12/2010)
as mentioned above i have inserted below records


insert into test4 values (1,1,'asd')
go
insert into test4 values (2,1,'asd')

the command executed successfully

my query is that as i have included first two columns in the unique clustered index then how can it accepts duplicates in column 2 ..the what is the use of defining column 2 in unique clustered index ?????????


Your clustered index is unique on column1 AND column2, not column1 OR column2: [1,1] is different to [2,1].



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1033445
Posted Sunday, December 12, 2010 4:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 7,010, Visits: 8,462
There is no need for a clustered index to be unique !
However, if it isn't unique, sqlserver will "uniquify" it for you by adding a 4-byte part to it.

The clustered index actually contains the data pages at its leaf level.
All non-clustered indexes refer to the row using the uniquified clustered index key ! (so the wider your clix columns, the wider your ncix refs)
If there is no clustering index, these indexes refer to the row using the RID.

Books online has great info on index structures and usage. !!

You need to keep in mind an index is unique over the combination of its columns, in the order the columns are specified !

If you need every column to be unique, you'll need to declare a unique index on each of the columns (or a unique constraint at column level)

guidelines:
- create a clustered index !
- keep your clix key columns small
- keep your clix unique if possible (avoind the uniquification by the engine)
- making your clix keys growing sequential may avoid run time page splits
- create a clustered index that serves you well ( range scans ). If you cannot find one to serve you well, just pick the one that goes for the previous guidelines.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1033453
Posted Sunday, December 12, 2010 5:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 8,841, Visits: 9,399
ahmed7.bi (12/12/2010)

if the above case is right then how many columns can we include in a clustered index.???

You can have up to 16 key columns in a clustered index, but the column sizes have to add up to less than 900 bytes.


Tom
Post #1033460
Posted Sunday, December 12, 2010 7:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
ahmed7.bi (12/12/2010)
i have read that only one unique clustered index can be created on a table and many non clustered index

then how come the below commands works fine as it is creating a unique clustered index on 2 columns of a table


You can only have one clustered index per table, though it doesn't have to be unique. Your command works because it's only creating one clustered index that consists of two columns. If you tried to create two single column clustered indexes, the second create index would fail.

For info on clustered index, have a read over this: http://www.sqlservercentral.com/articles/Indexing/68563/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1033470
Posted Tuesday, December 14, 2010 3:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:02 AM
Points: 271, Visits: 389
beginners SQL book will really help to understand the concept of Indexes.

Like why only one clustered index per table
When we say clustered index can also have non unique rows, so how sql manages to search a row.
Post #1034271
Posted Tuesday, December 14, 2010 5:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 43,028, Visits: 36,193
Abhijeet Chavan (12/14/2010)
beginners SQL book will really help to understand the concept of Indexes.


Or maybe the introductory series I wrote on indexes?
http://www.sqlservercentral.com/articles/Indexing/68439/

When we say clustered index can also have non unique rows, so how sql manages to search a row.

Covered here: http://www.sqlservercentral.com/articles/Indexing/68563/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1034302
Posted Tuesday, December 14, 2010 11:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:02 AM
Points: 271, Visits: 389
thanks GilaMonster

those are really good articles.
Post #1034902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse