Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Indexes newbie question

    Hello,

    I have a table like this:

    Id - int
    date - date
    User - nvarchar
    Name - nvarchar
    Tel - .....

    The most used sql's lookes like this:
    Select * from table where date = (n) and User = (n)
    Select * from table where (date Between (n) and (n)) and User = (n)
    Select * from table where Id = (n)

    What is the best way to create a index for this table?
    1/ Do I make one index and add the three coloms like this: Id, date, User.
    Do I have to use clustered or non-clustered?
    Do I have to check Unique?

    Do I create three different indexes for each colom?
    Do I have to use clustered or non-clustered?
    Do I have to check Unique?

    Thanks for the help..

  2. #2
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    I will prefer the following

    Id - Clustered Index with unique check (i.e., PRIMARY KEY)
    Date - Non Clustered Index without unique check
    User - Non Clustered index without unique check

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ringfoon, Assuming that your ID column is what uniquely identifies you rows, I would make 2 indexes.

    As kandrusatish suggested, the first index would be on just ID (Unique/Primary and Clustered).

    The second would be on USER then DATE. USER is always used with an equality (=). DATE can be used with an equality or a range (Between). You want to put columns that use a range after columns that use an equality because no columns are used after the a range parameter (or at least not very much).

    You could create 3 indexes but I would only do that if you used DATE by itself (without USER) in queries.

    PS whether the USER/DATE index is Unique or not depends on your data and requirements. More than likely, it would be non-unique, but you will have to determine that.

  4. #4
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Yes, I agree with Stealth DBA. If you are not using the DATE alone always in ur queries then you can use one non clustered index formed with DATE & USER.

Posting Permissions

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