working with primary key

Posted on 2007-10-08
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

    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

    so your okay
    LVL 7

    Assisted Solution

    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.
    LVL 22

    Assisted Solution

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

    <<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...
    LVL 1

    Author Comment

    thanks very much

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now