July 14, 2008 at 11:58 pm
😎
Hi,
when we create a primary key on a table clustered index is created automatically,
can u diff between primary key and clustered index
July 15, 2008 at 1:16 am
Hi,
A primary key is a constraint, while a clustered index is an index.
A primary key makes sure that duplicate records are not inserted into the table. It provides entity integrity to make sure each record is unique.
A clustered index creates a separate structure within the database, which will be used for optimization of your queries. A clustered index also physically sorts the records of the table based on the column(s) it is defined upon.
When a primary key is created on a table along with a clustered index; all the records would be physically sorted and uniqueness guaranteed.
It is also possible to create a table with a non-clustered index on the primary key instead of a clustered index.
Hope this was clearly put out
Regards
July 15, 2008 at 1:24 am
They are unrelated terms.
A primary key is the identifier of the row. It must be unique, it must have no nulls and it's used to enforce the entity integrity of the table. It's also used in foreign key relationships.
A clustered index is an index that has the data pages as the leaf level of the index. It enforces the storage order of the rows and pages in the table
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 15, 2008 at 6:03 am
In fact, there are certain conditions where your Primary Key most certainly should not be considered for your clustered index. If, for example, you are designing a backend to a web (or other) app which is using a GUID. As a GUID is (generally - there are various types) non-monotonic (or random, if you prefer) this means that new records being inserted into the table - if this is your clustered index, rather than being inserted at the end of the table can end up being inserted pretty much anywhere. This could lead, potentially, to a lot of physical IO due to physically re-ordering your table, page splits, locking. Basically - certainly for anything other than pretty moderate volume - performance suicide
July 15, 2008 at 9:59 am
By default, the primary key is the clustered index. When you create the table, you can specify "nonclustered" on the PK row, which will override that default. If you do that, you can separately create your own clustered index on the table.
Example:
create table dbo.Table1 (
ID int primary key NONCLUSTERED, -- keyword
Col1 ... etc., add more columns
go
create clustered index CID_Table1_IndexName on dbo.Table1 (Col1)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 5:57 pm
Another very good reason for not setting the PK as a clustered index: some other column may be a better candidate for the clustered index, based on the type of queries performed on the table. If, for example, a column is used frequently in range queries or in the GROUP BY/ORDER BY clause, it is a perfect candidate for the clustered index.
Examine your queries and do not commit too early to making something (especially the PK) the clustered idx. It may be difficult to change this later on.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 15, 2008 at 11:20 pm
It true that when we define the primary key by default it create the clustered index, but its not true you can define it as non clustred index also.
Primary Key uniquely defines each row in the table.
Clustered index store the index ( like we see in book INDEX ) for this unqiue data on index pages.
Regards,
Abhijit More
July 16, 2008 at 12:24 am
Abhijit (7/15/2008)
It true that when we define the primary key by default it create the clustered index, but its not true you can define it as non clustred index also.
Really?
CREATE TABLE IndexTesting (
ID int IDENTITY,
SomeChars Varchar(50),
InsertDate DATETIME DEFAULT GETDATE()
);
GO
ALTER TABLE IndexTesting
ADD CONSTRAINT pk_Testing PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE CLUSTERED INDEX idx_Testing_Date ON IndexTesting (InsertDate)
The primary key is enforced by an index. It does not have to be a clustered index though.
Primary Key uniquely defines each row in the table.
Clustered index store the index ( like we see in book INDEX ) for this unqiue data on index pages.
The point of the clustered index is that it is the table and stored the full data in the leaf pages of the clustered index, with a b-tree structure. It does not store the leaf pages separate for the table (like a book index). A better analogy for the clustered index is the pages of the book itself, with the page number as the clustering key.
The clustered index does have to be unique, but if it is not specified as unique (which is allowed), SQL will make it unique behind the scenes
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply