SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


unique clustered index on 2 columns


unique clustered index on 2 columns

Author
Message
ahmed7.bi
ahmed7.bi
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 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.???
ahmed7.bi
ahmed7.bi
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 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 ?????????
ChrisM@home
ChrisM@home
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 10605
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
ChrisM@home
ChrisM@home
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 10605
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
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28467 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25254 Visits: 12488
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213895 Visits: 46263
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, MVP, M.Sc (Comp Sci)
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


Abhijeet Chavan
Abhijeet Chavan
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 396
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213895 Visits: 46263
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, MVP, M.Sc (Comp Sci)
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


Abhijeet Chavan
Abhijeet Chavan
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 396
thanks GilaMonster

those are really good articles.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search