Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Indx newbie: please help

    Consider the following

    -----------------------------------------------------------------
    ASSIGNMENT
    - Index : Integer, Unique, NotNull, Primary Key
    - Start : DateTime, , NotNull,
    - End : DateTime, , ,
    - Other : ...

    TASK
    - Assignment : Integer, Unique, NotNull, Primary Foreign Key
    - SubIndex : Integer, , NotNull, Primary Key
    - Type : Char(3), , NotNull, Foreign Key
    - Start : DateTime, , NotNull,
    - End : DateTime, , ,
    - Other : ...

    TASK_TYPE
    - Code : Char(3), Unique, NotNull, Primary Key
    - Description : VarChar(20), , NotNull,
    -----------------------------------------------------------------

    As u can c the idea is simple...
    ...an assignment has an INDEX as PK, a start date, an end date and other fields;
    ...an assignment can have one or more tasks; the relationship is 1:N and is identifying (see next point)
    ...a task has a SUB INDEX inside the assignment; that is the PK is the assigment it belongs to (also a FK) and an index for that assignment
    ...a task has also a TYPE, which is a FK to the TASK_TYPE table

    Consider also that...
    ...ASSIGNMENT contains > 1 millions rows
    ...TASK contains < 10 rows for each assignment (so an average of 5 millions rows)
    ...TASK_TYPE contains < 10 rows



    As far as I know SQL-server creates a CLUSTERED INDEX for any PK, that is
    ASSIGNMENT (Index)
    TASK(Assigment, SubIndex)
    TASK_TYPE(Code)

    Do I have to add any other NON CLUSTERED INDEX? I would say I should add the following:
    TASK(Assignment)
    TASK(Type)

    But of course TASK(Assignment) is already part of the CLUSTERED INDEX ASSIGNMENT(Assignment, SubIndex), so I shouldn't add it, right?
    What about TASK(Type)?

    Or maybe there's a completely different solution?

    My main problem is due to the fact that TASK has a composite PK wher one field is also a FK.

    Any advice is welcome.

    thanks a lot :-)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what, exactly, is your main problem? you didn't say what it was

    i don't think you need any additional indexes, since TASK_TYPE should always be handled in memory (but then, i am not a DBA, eh )

    by the way, it's not a good idea to name a column "index" as this is a reserved word in a lot of languages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    5
    I'd simply like to know how where to put indexes

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, put an index on task.type

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would put the index in the database. Someone might need it, and it would be very embarrassing, if you left it at home.

    Joking aside, is there a particular query that is giving problems? Remember that excessive indexing can hurt data modification processes.

Posting Permissions

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