July 24, 2009 at 5:31 am
Hi All,
Today while reading about Indexed Views, i encountered term 'UNIQUE CLUSTERED INDEX'. Can you please differentiate b/w the following:
Unique Clustered Index
Non Unique Clustered Index
Thanks
Ankur
July 24, 2009 at 6:01 am
It's pretty straight forward. They're both clustered indexes, with all that entails. One has a constraint that only allows unique values into the column or columns that make up it's key. The other does not, which means duplicate values can be inserted.
If you take this data:
A
B
C
D
D
You could not put it into a unique index because of the second 'D' but you could put it into just a clustered index.
There are also unique and non-unique nonclustered indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2009 at 6:08 am
Also if it's not created with UNIQUE the DB engine adds a 4 byte uniqueifier column. You can't see this column.
July 24, 2009 at 7:59 am
Thanks Grant and Allen for your valuable responses.
Does it mean Clustered index created on Primary Key is same as Unique Clustered Index?
Also it is said: PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table.
Please clearify it further.
July 24, 2009 at 8:15 am
I think at the physical level (on disk) the are the same but a primary key statement ceates a PK Constraint wheras the unique clusterd index does not create a constraint.
CREATE TABLE tblA (id int)
CREATE UNIQUE CLUSTERED INDEX ix1 ON tblA (id)
INSERT tblA (id) VALUES (1)
INSERT tblA (id) VALUES (1)
CREATE TABLE tblB (id int PRIMARY KEY)
INSERT tblB (id) VALUES (1)
INSERT tblB (id) VALUES (1)
July 24, 2009 at 8:50 am
ankur_libra (7/24/2009)
Thanks Grant and Allen for your valuable responses.Does it mean Clustered index created on Primary Key is same as Unique Clustered Index?
Also it is said: PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table.
Please clearify it further.
As Allen said, they are the same at a really low level. Primary key constraints that don't have nonclustered specified will be created as clustered if a clustered index does not exist on the table. Clustered primary keys are the default behavior, but you're not limited by that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2009 at 9:29 am
Lot of thanks to you guys.
One thing more- Allen you said if clustered index are built without UNIQUE word "DB engine adds a 4 byte uniqueifier column", please you clearify it further.
Ankur
July 24, 2009 at 9:43 am
A clustered index has to be unique. It's the row's 'address'. If the clustered index is not declared as UNIQUE then, behind the scenes, SQL adds an integer column to the clustered index. Whenever it finds a duplicate value, it uses that column to make the key as a whole unique.
It's called the 'Uniqueifier' and there is mention in Books Online and on MSDN if you want to search.
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
July 24, 2009 at 9:53 am
July 24, 2009 at 10:04 am
allen davidson (7/24/2009)
Because a clustered index determins the physical order of records on disk....
Clustered index determines the logical order of the data, not the physical. Rows can be stored physically on a page in an order differing from the clustered index order and, if there is any fragmentation, the physical order and logical order of the leaf pages will not match (the definition of fragmentation).
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
July 24, 2009 at 10:59 am
Thanks Gail and Allen for your generosity.
"
Clustered index determines the logical order of the data, not the physical"
But at link:
http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
The very first line explains that:
"A clustered index determines the physical order of data in a table."
So, can you please help us by clearifying it further.
Ankur
July 24, 2009 at 11:00 am
Thanks Gail,
I thought that it keeps rows with adjacent clustered index entries, subject to space being avaiable on the page, on the same leaf page. Am I missing something?
I understand that pages may not be physically adjacent because a page split would cause too much reorganistion.
Allen
July 24, 2009 at 11:14 am
allen davidson (7/24/2009)
I thought that it keeps rows with adjacent clustered index entries, subject to space being avaiable on the page, on the same leaf page.
It does.
Let's take an example of a clustered index on an int column (not an identity) and let's say there are 100 rows in the index at 10 rows per page and those int values are contiguous
The index will have rows 1..10 on page 1, 11..20 on page 2 and so on with 91..100 on page 10. That the clustered index guarantees. Within the physical page however, there's no guarantee that the rows will physically be written on the page in that order. Let's say, for some odd reason, first all the odd numbered rows were inserted and then all the even. If you went and read the page physically, you'd see this:
Page Header
Row 1
Row 3
Row 5
Row 7
Row 9
Row 2
Row 4
Row 6
Row 8
Slot index
Page footer
Because, when SQL goes to insert row 2, it doesn't want the cost of shifting all the rows down to make space, same as with page splits it doesn't want have to shift pages around to maintain order.
The slot index, at the bottom of the page, maintains pointers into the page that are in the order of the clustering key, so that the correct rows can be retrieved. So logically, the slot index maintains the 'order' of rows but physically the rows may not be stored in order.
You could argue that I'm been pedantic about it, but the prevalent myth that clustered index order = physical order on disk gives rise to lots of misunderstandings about indexes and ordering.
Also: http://www.sqlmag.com/Articles/ArticleID/92886/92886.html
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
July 27, 2009 at 5:36 am
Thanks Gail,
I hadn't heard about ther slot table before.
Your post prompted to to have a play with DBCC IND & DBCC PAGE and I can see, running your example, that the offsets do not run in order.
I think my reply should have been along the lines of...
clustered indexes affect placement on disk
Allen
July 27, 2009 at 5:44 am
allen davidson (7/27/2009)
I think my reply should have been along the lines of...clustered indexes affect placement on disk
Not necessarily true either. Think about fragmented indexes, fragmented file system, striping, etc. If you haven't read that article I linked, please do.
The clustered index may affect the placement of pages within the data file, but it's not the only thing that does. That's why I definitely prefer to talk about the logical ordering rather than physical because so many other things can and do affect the physical location of the pages on disk.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply