Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Yet another UNIQUE constraint question

    Say I have a table: parts( part_id, part_name, <other stuff> )

    Most of my sprocs do searches on this parts table based on the part_name, NOT the part_id. So...
    Code:
    SELECT ... FROM parts WHERE part_name = 'some name'
    I have a UNIQUE NONCLUSTERED constraint on the part_name

    My question: Since virtually any sproc that uses the parts table does a look-up based on the same, should I instead make it a CLUSTERED index? (and as a result, the part_id -- the primary key -- cannot be a clustered index)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There would need to be a lot more information to answer that properly but overall I would probably say no.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    There would need to be a lot more information to answer that properly but overall I would probably say no.
    Hmmm.... So how about if I say this:

    In the beginning, there will be lots of inserts into these tables, mainly just to fill them with the appropriate data. Once it's filled, inserts will be sparse, and almost all of the actions taking place on the table will be a SELECT query based on the part_name

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That tilts things towards changing it.

    Is the primary key (part_id) referenced as a foreign key and is it the leading column in any composite clustered indexes in those related tables?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    It IS referenced as a foreign key in 1 other table, however it is not a part of a composite clustered index in it

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the other table a many-to-many table?

    Are their any other non-clustered indexes on parts? I imagine part_name does not change?

    What does this return:
    Code:
    SELECT AVG(DATALENGTH(part_name))
    FROM parts
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    I was a little unsure in what context you meant many-to-many, but essentially the other table is a transaction table, where each trans_id (the primary key) can be associated with at most 1 part_id

    No other non-clustered indexes exist on the parts table (unless you count the part_id, which is a primary key, so indexed by default)

    part_name will not change -- if it does it will be rare

    Your AVG() query returns 11 (the part-name will almost always be 11 characters)

  8. #8
    Join Date
    Feb 2007
    Posts
    62
    In this case I would suggest the answer would be 'why bother'.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, he'd no longer have to traverse the NCI and perform a lookup.If he is not experiencing any perceivable performance hit then this theoretical gain is probably not worth the effort though, as you say.

    The obvious (to me anyway) point here would be to use the id for all where predicates but this would be mean retrofitting a lot of code. One to bear in mind for the future, dbguy.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    Well, he'd no longer have to traverse the NCI and perform a lookup.If he is not experiencing any perceivable performance hit then this theoretical gain is probably not worth the effort though, as you say.

    The obvious (to me anyway) point here would be to use the id for all where predicates but this would be mean retrofitting a lot of code. One to bear in mind for the future, dbguy.
    Thanks for the responses guys. I've decided to leave it as unclustered then.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •