December 12, 2010 at 1:00 am
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.???
December 12, 2010 at 1:15 am
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 ?????????
December 12, 2010 at 2:30 am
ahmed7.bi (12/12/2010)
Hii 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."
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 12, 2010 at 2:33 am
ahmed7.bi (12/12/2010)
as mentioned above i have inserted below recordsinsert 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].
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 12, 2010 at 4:29 am
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
Learn to play, play to learn !
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[/url]
- 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
December 12, 2010 at 5:45 am
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
December 12, 2010 at 7:13 am
ahmed7.bi (12/12/2010)
i have read that only one unique clustered index can be created on a table and many non clustered indexthen 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
December 14, 2010 at 3:44 am
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.
December 14, 2010 at 5:17 am
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
December 14, 2010 at 11:33 pm
thanks GilaMonster
those are really good articles.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply