January 31, 2008 at 9:23 pm
Hi All,
What is the difference between a primary key and unique key if a column is defined as a primary key and a column defined as unique not null.
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 31, 2008 at 9:27 pm
There's not really much of a practical difference between the two except that many tools you work with will consider the primary key to be "king". Certainly linking your foreign keys to primary keys only helps with understanding and self-documenting of the database. You should at least have a primary key on the table always.
January 31, 2008 at 9:56 pm
So, a unique key with not null defined on it can't be used to link to foreign keys.
In many portals I read that the difference is that primary key by default has clustered index and Unique key has non clustered index defined on it.
Primary key dint allows null values where as unique key can has one null value.
But, as per me these are not the valid difference as we can have a primary key with a non clustered index and a unique key with clustered index.
And if i define a column with unique not null then it will not take a null value.
Kindly tell me that am I right?
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 31, 2008 at 10:41 pm
You can only have 1 primary key per table, but you can have many unique keys
Primay keys cannot have nulls. A unique key defined on a nullable field can have 1 null.
And if i define a column with unique not null then it will not take a null value.
Any column defined not null will not accept nulls. Doesn't matter if there's a unique key or not.
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
January 31, 2008 at 11:46 pm
Thanks .. a lot.. one more thing ....
do clustered index has an index table?
As per me it dint?
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 31, 2008 at 11:49 pm
I'm sorry, I'm not sure I understand your question.
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
February 1, 2008 at 12:53 am
As non clustered index is maintained as a separate B tree structure that has pointer in form of file id, page no,and row no. where as in clustered index the leaf level pages are the data pages.
Hence in case of non clustered index table is stored at different memory location than the index where as in case of clustered index table is the leaf level pages of the index.
I want to know that is this correct?
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 1, 2008 at 1:26 am
As Clustered Index has the leaf level stored with data pages, when you need to read other columns by Clustered Index, you don't have to do a separate operation. You are already in the data page and in one shot you can read all the information.
If you have a non clustered index, the leaf level entries are stored separately in an index page and will point to the corresponding clustered index key ( or RID if the table is not clustered) So to read the values of other columns you may have to ready the non clustered index and they go to the data page as well.
On your question 1, By default Primary keys are clustered and uNique keys are not.
Keep a clustered index on all tables. Primary key is a good candidate (as they have the unique and not null properties) but not always.
Remember the data is organized in Clustered key. So choose the key wisely, so that you wont create hot spots and too many page splits. Creating the clustered key in the natural order is good suggestion.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
February 1, 2008 at 2:48 am
Does it means that in clustered index, index pages are directly linked to data pages , which are sorted on the basis of index key.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 1, 2008 at 2:54 am
With a clustered index, the leaf pages of the index are the data pages. Yes, they are in the order of the index keys.
This means that the clustering key defines the storage order of the table (assuming no 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
February 5, 2008 at 5:00 am
Thank you All
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 6, 2008 at 10:04 pm
Primary Key
1)A table can have only one PRIMARY KEY constraint.
2)PRIMARY KEY constraint cannot accept null values.
3)A table can have only one primary key constraints.
4)It automaticaly define unique index for primary key.
Unique
1) It accept one null value.
2)UNIQUE constraints enforce the uniqueness of the values in a set of columns.
February 8, 2008 at 9:44 am
Clustering is about physical organization. When a relation is "clustered on a key", that means that the tuples will be ordered on that key in a B-tree structure. It is not possible to physically order a collection on more than one key. Therefore, it is not possible to have more than one clustered index on a relation.
Rob
February 8, 2008 at 9:54 am
They also serve different purposes. Put simply - the Primary Key is the "external address", the clustered key is the "internal address". Meaning - the Clustered key tells you physically where in the database the row will be found (as previously mentioned - the Clustered key dictates the physical order). The Primary key tells OTHER tables how to access this row.
Which is why the Primary Key MUST be addressable (i.e. must be unique AND not nullable). It's also why primary keys are associated with creating and maintaining relationships between tables (Primary Key to Foreign Key constraints).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 8, 2008 at 3:15 pm
And just to make things more interesting no you should not ALWAYS have a clustered index on a table.
If a table does not have a clustered index it is considered a "heap". For small tables .. 1 or 2 extents for example .. you are better off with a heap rather than having a clustered index. SQL will use the first_aim_page to reference the first extent directly. If all of the data is either in this extent or the next extent there are actually less reads (no index pages) and no need for the system to keep up an index.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply