June 10, 2006 at 3:14 am
Dear all,
What is the diffrence between creating a primary key with clustered index and primary key with no clustered index?
Hope u understand the question.
from
Killer
June 10, 2006 at 8:30 pm
Well, a key is simply a means of identifying a row in a table. An index is a method for finding data quickly in that table. When you create a clustered index on the primary key (the default in SQL Server), you designate the database engine to physically store the data sorted based on that key value. If you create a primary key and do not create a clustered index, that key is still the primary unique identifier for a given row, however when data is added or moved it is placed into order on the clustered index (assuming that you have another column or set of columns designated as the clustered index) or in the order the data is inserted (if you do not have a clustered index on the table).
Does that answer your question?
June 11, 2006 at 10:49 pm
Hi,
It is confusing.Can u pls explain more clear.
from
killer
June 12, 2006 at 5:40 am
The clustered or non-clustered part tells SQL Server what type of index you want to create for that primary key. A clustered index means the data is physically sorted based on that index. A non-clustered index means the data isn't physically sorted based on that index. Since data can only be physically sorted one way, there can be only one clustered index, but many non-clustered indexes.
An example where you might want want a non-clustered primary key is if you're using an IDENTITY column as a surrogate primary key. In that case, you may want to physically sort your data based on some other data field(s) because of your queries and to avoid having a "hot spot" and page splits.
K. Brian Kelley
@kbriankelley
June 12, 2006 at 5:42 am
Basically (by default in SQL Server), a primary key with a clustered index will ALWAYS keep the data for that column in ascending order. If a record is inserted, the entire table will be reordered to maintain that ascending order...so keep this in mind when setting these up because with a very large amount of inserts and deletes, you add a little bit of overhead to your server...deciding on whether to cluster an index or not is a balance between searching and finding a record quickly vs. the overhead of maintaining the index.
A primary key with NO clustered index will only make sure the entries are unique, (without repeated values), but will not order the table based on that column and an inserted record will go in and stay in the same order it was inserted in (unless, of course, there was some other clustered index on the table on another column), keep in mind here that it can take longer for a record to be found if using only the nonclustered key column to search because, of course, the records are not in that order.
Look more into the differences between indexes and clustered indexes, that may help to clarify the point. Keys are one thing...indexes are another.
Hope that helps,
Mary M.
June 13, 2006 at 5:04 pm
Not quite true. Inserting a row into a clustered index never causes the entire table to be reordered, it simply maintains the page pointers on split pages so that the page chain remains in sorted order. What it does not do is attemtpt to maintain the pages in contiguous physical order on disk. So, suppose there are two pages, AA and BB, and the page chains on each point to the other, forward and back (AA BB). Now we insert a row that falls onto page AA, but AA does not have room for it. SQL will do a page split by grabbing a fresh page (lets call it AB), copying half the date from AA onto AB, and updating the page chain so that the data is in order as the chain is walked (AA AB BB). These three pages do NOT exist contiguously on disk. In doing the page split, pages before AA (00 - 99) and after BB (CC - ZZ) are un affected.
One big advantage of clustered indexes that seems to be under-emphasized in these posts is that at the leaf level of a clustered index you find the entire data row. Therefore, SQL's optimizer will favor a clustered index, knowing it will find your data there. With a non-clustered index, the leaf level holds an address where data can be found, and SQL then has to go to that address to get the data. Metaphorically, a clustered index is like an encyclopedia, and a nonclustered index is like a set of textbooks. If you want to look up "Gettysburg", grab the G volume of the encyclopedia, and rapidly flip the pages to find the entry. Further, not only did you find "Gettysburg Address", but "Gettysburg, Battle of" is there, too. In the textbook, you can rapidly scan the index for Gettysburg, find out it is on page 429 and 814, then turn to those pages to find your info. The non-clustered index beats the heck out of reading the whole book, but is not as efficient as the clustered index.
As a rule of thumb (and if that's not strong enough, throw in a few fingers, too), EVERY table should have a clustered index. The trick is in identifying which index it should be. In all the years I've been a DBA (12, gulp!), I can only think of one time when I made an argument to uncluster a table, but that's a story for another day.
July 19, 2007 at 1:07 am
I have question where there is a table with two columns :
1. ID(PK, with non clustered index)
2. Address(clustered index)
When we insert data in the table it is not reordering the data based on Address(field as clustered index) but if columns are more than 3 then it is able to reorder the data in the table.
July 19, 2007 at 6:24 am
Keep in mind that if you are dealing with an RDBMS, you are not guaranteed sorted data unless you specify an ORDER BY clause.
K. Brian Kelley
@kbriankelley
July 19, 2007 at 7:03 am
But When we say that column with clustered index always orders the table then why its not showing the correct results.
But if I use 3 columns then it orders the table according to the clustered index column.
wht is the difference between using 2 columns with incorrect data output and 3 columns with correct data output for the clustered index column
July 19, 2007 at 8:03 am
You are using one arbitrary example and the database engine will use whatever techniques it can in order to get the data back as quickly as possible. Somewhere along the way, based on the way your data is structured and what you are asking to be returned, it pulls the data back in different ways. And that's to be expected. Even with a clustered index, you are not guaranteed a sort order UNLESS you use ORDER BY. To rely on the clustered index for sort means eventually you are going to get an inconsistent result, like you have now.
K. Brian Kelley
@kbriankelley
July 20, 2007 at 1:18 am
Thanks for ur response...........
But my question is that then why does people say that the order of the data is in accordance to the clustered indexed column if it exist. But in my case its showing up for 3 or more columns and not for 2 columns, when I do select * from <tablename>
Can u give a material in respect to your response so that it can be elborated, coz this is a new thing that i am hearing from you and not many people might be knowing these details
June 25, 2010 at 9:03 am
Hi guys
I understand the basics of the keys and indexes. My question is, in SQL Server 2000, BOL specifically says:
"Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint."
Does the same thing happen on SQL Server 2008?
I'm trying to debug some code converted from 2000 - 2008 and not all of the keys made it over on the tables.
Thanks
June 25, 2010 at 9:04 am
Chris Schmidt (6/25/2010)
"Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint."Does the same thing happen on SQL Server 2008?
Yes.
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
August 23, 2010 at 4:08 am
If I create a primary key on a column then it automatically creates a Clustered Index on it.
How can I create a PK without Clustered index?
August 23, 2010 at 4:13 am
Specify NONCLUSTERED in the definition of the pk. Then the PK will be enforced by a unique nonclustered index.
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 19 total)
You must be logged in to reply to this topic. Login to reply