working with primary key

Posted on 2007-10-08
Medium Priority
Last Modified: 2008-01-09
Hello guys

doubt about primary key

is it true if I have a primary key compost with 3 or more field I can have a slow table or search on it?

If so, how should I work with it to avoid duplicated row?

Question by:hidrau
LVL 16

Assisted Solution

SQL_SERVER_DBA earned 200 total points
ID: 20037010
an index is a physical construct, and is used for performance. Primary and foreign keys, on the other hand, are logical constructs, and are defined with only one thing in mind -- to ensure the relational integrity of the data.
LVL 16

Expert Comment

ID: 20037013
so your okay

Assisted Solution

DiscoNova earned 600 total points
ID: 20037022
a) Yes, it is true. However, it is also true that you can have a slow table or search even with primary key composed of only a single value. You can (and most likely will) also have a slow table or search if you use no primary key at all.
b) You should avoid duplicated rows by using primary keys.

Ok, that was a bit harsh. But the fact is - no matter what you do, use primary keys. You're better off with them than you are without them. And use as many fields you need to, don't limit yourself because of FUD (Fear/Uncertainty/Doubt) ... it usually is not warranted.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 22

Assisted Solution

dportas earned 400 total points
ID: 20037506
>> is it true if I have a primary key compost with 3 or more field I can have a slow table or search on it?

Rest assured, that is nonsense. Performance is a product only of the physical model and of the particular queries you are executing.
LVL 23

Accepted Solution

Racim BOUDJAKDJI earned 800 total points
ID: 20039270
<<is it true if I have a primary key compost with 3 or more field I can have a slow table or search on it?>>
As the previous answerers mentionned there is *no* direct relationship between the logical construct of primary key and performance.  But because SQL does not implement primary keys constraint of unicity otherwise than by creating indexes, it does create confusion.  I guess the question the questionner meant to ask is: Can the implementation of a composite unique index over 3 columns is a performance killer.  the answer is : it depends on the length of the columns and the datatype.  As much as possible try to select short and/or integer unique indexes.  But as other said, you must accept that slight overhead or face the consequences of a denormalized schema (which will kill performance anyway)...

Hope this helps...

Author Comment

ID: 20049829
thanks very much

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

807 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