We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Can a Primary Key be multiple columns in SQL Server 2005 / 2008 ?

Medium Priority
1,346 Views
Last Modified: 2012-05-07
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
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>Can a Primary Key be multiple columns ?
Yes,  
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>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 "
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
pcelba,
    I typed that meaning this one only. Thanks for explaining to the asker.
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
>> 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.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Commented:
>>  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...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
*laughing* OK... Enough said... Should have known better  (but the optimizer would need some additional predicates to choose alternatives :) ) :)

Commented:
>> 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
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I knew I shouldnt have said anything else :)

But (gosh, cannot believe I am doing this) adding the unique constraint is adding another index and will do an index scan...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.