Solved

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

Posted on 2009-07-05
14
1,276 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
0
Comment
Question by:milani_lucie
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
>Can a Primary Key be multiple columns ?
Yes,  
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
>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 "
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 150 total points
Comment Utility
>> 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
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 100 total points
Comment Utility
" 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).
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
pcelba,
    I typed that meaning this one only. Thanks for explaining to the asker.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
Comment Utility
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')
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
>> 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.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
>>  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...
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
*laughing* OK... Enough said... Should have known better  (but the optimizer would need some additional predicates to choose alternatives :) ) :)
0
 
LVL 22

Expert Comment

by:dportas
Comment Utility
>> 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
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Determine if date is recurrance date 3 36
encyps queries mssql 15 24
SQL Help - 12 34
Creating Alerts in sql sever 2 12
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now