We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Can a Primary Key be multiple columns in SQL Server 2005 / 2008 ?
Posted on 2009-07-05
Hi,
Can a Primary Key be multiple columns ? Because Primary Key will have Clustered Index by default, it will be physically stored in an order. But if a Primary Key be multiple columns, then how it will be stored and ordered - using leaf nodes / pointers to leaf nodes ?
Thanks
Can a Primary Key be multiple columns ? Because Primary Key will have Clustered Index by default, it will be physically stored in an order. But if a Primary Key be multiple columns, then how it will be stored and ordered - using leaf nodes / pointers to leaf nodes ?
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4
3
3- +2
14 Comments
Active 4 days ago
>how it will be stored and ordered - using leaf nodes / pointers to leaf nodes ?
it is not an one word answer, i would recommend you read " Inside SQL "
it is not an one word answer, i would recommend you read " Inside SQL "
Active today
Surely, it can be multiple columns key. The key value is composed from column values and behaves like single column key. The index and table data are at two different places and index leafs contain pointers to original data.
Active today
The clustered vs. nonclustered difference is in necessity of "duplicate sorting" when you create clustered index - index keys are organized in the tree as obvious (the first sorting) and original data are physically sorted by index key values (the second sorting). Thus the time necessary for updates of columns included in clustered index is obviously much longer than time necessary for other columns.
Active 2 days ago
>> Can a Primary Key be multiple columns ?
Yes..
>> Because Primary Key will have Clustered Index by default, it will be physically stored in an order.
Even if it is single or multiple columns, Clustered Index will physically sort records present in the table.
>> But if a Primary Key be multiple columns, then how it will be stored and ordered - using leaf nodes / pointers to leaf nodes ?
As mentioned earlier, It would physically sort based upon the order you specify. For eg:
If you create a Primary key / clustered index on ( a, b , c) then it would be first sorted by a then by b and then by c
Hope this helps
Yes..
>> Because Primary Key will have Clustered Index by default, it will be physically stored in an order.
Even if it is single or multiple columns, Clustered Index will physically sort records present in the table.
>> But if a Primary Key be multiple columns, then how it will be stored and ordered - using leaf nodes / pointers to leaf nodes ?
As mentioned earlier, It would physically sort based upon the order you specify. For eg:
If you create a Primary key / clustered index on ( a, b , c) then it would be first sorted by a then by b and then by c
Hope this helps
Active today
" first sorted by a then by b and then by c" is probably not accurate becaus we could minimize it to sorting by c only :-).
The correct sentence is:
It would be first sorted by a, if a values are equal the b value is added, and if a+b are equal then c value is added. Or, simply said, it is sorted by a+b+c (the ASC/DESC possibility is omitted here).
The correct sentence is:
It would be first sorted by a, if a values are equal the b value is added, and if a+b are equal then c value is added. Or, simply said, it is sorted by a+b+c (the ASC/DESC possibility is omitted here).
Active 2 days ago
Answer is simply YES to the first part, there are a few restrictions as to what kind of data (such as not NULL, LOB or BLOB), and ideally is small non-changing data elements.
When creating the Primary Key, if a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.
You may also want to consider a UNIQUE Constraint and the use of a surrogate key as the Primary Key.
If you do create a clustered index, then by definition, the leaf level of a clustered index and the data pages are the same.
The "KEY VALUE" will result in a sequence of the composite key. However, if spanning different (partitions) filespaces then it is not gauranteed that it will return everything in exact sequence. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition. You must really use the ORDER BY to ensure correct sequence.
However, assuming a single partition,
table My_abc (a int, b int, c int, d varchar(100), CONSTRAINT PK_My_abc PRIMARY KEY CLUSTERED (a,b,c))
with values (1,1,1,'first c'), (1,2,1,'second b'), (2,1,1,'second a'), (1,1,2,'Second c'), (1,2,2,'Second b and c')
will return thr rows in the sequence of the key
(1,1,1,'first c')
(1,1,2,'Second c')
(1,2,1,'second b')
(1,2,2,'Second b and c')
(2,1,1,'second a')
When creating the Primary Key, if a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.
You may also want to consider a UNIQUE Constraint and the use of a surrogate key as the Primary Key.
If you do create a clustered index, then by definition, the leaf level of a clustered index and the data pages are the same.
The "KEY VALUE" will result in a sequence of the composite key. However, if spanning different (partitions) filespaces then it is not gauranteed that it will return everything in exact sequence. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition. You must really use the ORDER BY to ensure correct sequence.
However, assuming a single partition,
table My_abc (a int, b int, c int, d varchar(100), CONSTRAINT PK_My_abc PRIMARY KEY CLUSTERED (a,b,c))
with values (1,1,1,'first c'), (1,2,1,'second b'), (2,1,1,'second a'), (1,1,2,'Second c'), (1,2,2,'Second b and c')
will return thr rows in the sequence of the key
(1,1,1,'first c')
(1,1,2,'Second c')
(1,2,1,'second b')
(1,2,2,'Second b and c')
(2,1,1,'second a')
>> will return thr rows in the sequence of the key
Without ORDER BY the order of rows returned by a query is undefined. It's quite possible that they will not be returned in the order of the clustered index. In some cases it's very likely that they won't match the clustered index order. The only way to be sure is to specify ORDER BY.
Without ORDER BY the order of rows returned by a query is undefined. It's quite possible that they will not be returned in the order of the clustered index. In some cases it's very likely that they won't match the clustered index order. The only way to be sure is to specify ORDER BY.
Active 2 days ago
Yep, agree, that is why I also said : " You must really use the ORDER BY to ensure correct sequence."
However, in a single partition, then the default return sequence will be data page sequence and with a clustered index, with data held on leaf nodes, well...
However (and again), one should really use the ORDER BY if there is a specific sequence required.
Using the My_abc as an example, and inserting rows out of sequence, then the select will return the rows as expected (again, it must not be relied upon)...
create table My_abc (a int, b int, c int, d varchar(100), CONSTRAINT PK_My_abc PRIMARY KEY CLUSTERED (a,b,c))
insert My_abc values (1,1,1,'first c')
insert My_abc values (1,2,1,'second b')
insert My_abc values (2,1,1,'second a')
insert My_abc values (1,1,2,'Second c')
insert My_abc values (1,2,2,'Second b and c')
select * from my_abc
However, in a single partition, then the default return sequence will be data page sequence and with a clustered index, with data held on leaf nodes, well...
However (and again), one should really use the ORDER BY if there is a specific sequence required.
Using the My_abc as an example, and inserting rows out of sequence, then the select will return the rows as expected (again, it must not be relied upon)...
create table My_abc (a int, b int, c int, d varchar(100), CONSTRAINT PK_My_abc PRIMARY KEY CLUSTERED (a,b,c))
insert My_abc values (1,1,1,'first c')
insert My_abc values (1,2,1,'second b')
insert My_abc values (2,1,1,'second a')
insert My_abc values (1,1,2,'Second c')
insert My_abc values (1,2,2,'Second b and c')
select * from my_abc
>> in a single partition, then the default return sequence will be data page sequence and with a clustered index, with data held on leaf nodes
Unless the optimiser chooses to use a covering nonclustered index scan instead. Or it performs an unordered (allocation order) scan of the clustered index. Or an "advanced" scan. Or...
Unless the optimiser chooses to use a covering nonclustered index scan instead. Or it performs an unordered (allocation order) scan of the clustered index. Or an "advanced" scan. Or...
Active 2 days ago
*laughing* OK... Enough said... Should have known better (but the optimizer would need some additional predicates to choose alternatives :) ) :)
>> the optimizer would need some additional predicates to choose alternatives
Not necessarily...
CREATE TABLE tbl (x INT PRIMARY KEY CLUSTERED, z CHAR(1) UNIQUE);
INSERT INTO tbl VALUES (1,'Z');
INSERT INTO tbl VALUES (2,'Y');
INSERT INTO tbl VALUES (3,'X');
INSERT INTO tbl VALUES (4,'W');
SELECT * FROM tbl;
Result:
x z
----------- ----
4 W
3 X
2 Y
1 Z
Not necessarily...
CREATE TABLE tbl (x INT PRIMARY KEY CLUSTERED, z CHAR(1) UNIQUE);
INSERT INTO tbl VALUES (1,'Z');
INSERT INTO tbl VALUES (2,'Y');
INSERT INTO tbl VALUES (3,'X');
INSERT INTO tbl VALUES (4,'W');
SELECT * FROM tbl;
Result:
x z
----------- ----
4 W
3 X
2 Y
1 Z
Featured Post
Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
by Mark Wills
Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted? Many believe this is the case.
Let us try to examine for ourselves with an example.
To get started, use the following script, wh…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 14 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.