locked
Need advice on index RRS feed

  • Question

  • Hi,

    If I need to add an index for a varchar column in a table/view, what type of index do I need to use i.e. clustered or non-clustered?


    Thanks, Sam
    Monday, September 13, 2010 5:57 PM

Answers

  • U can't decide type of index based on datatype.

     

    You have any other columns which are unique? Its always better to choose the clustering key having less size, if there is chance...  If you have any integer column or datetime columns or some fixed length columns wbich are unique, its better to keep that column as Clustered... Even if not such column, keeping an identity column as clustered key, is also an gud idea...

     

    if you don't have such kind of columns and your varchar column is of less size and unique, then make this column as clustered. Otherwise, I would suggest you to make varchar column as non-clustered.

    • Marked as answer by imsam67 Monday, September 13, 2010 6:24 PM
    • Unmarked as answer by imsam67 Monday, September 13, 2010 6:24 PM
    • Marked as answer by imsam67 Monday, September 13, 2010 6:26 PM
    Monday, September 13, 2010 6:09 PM
  • If this is the PK for your view, then you can add this as a clustered index.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by imsam67 Monday, September 13, 2010 6:06 PM
    Monday, September 13, 2010 5:59 PM
  • Then try to make it Non-Clustered.
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by imsam67 Monday, September 13, 2010 6:06 PM
    Monday, September 13, 2010 6:06 PM

All replies

  • If this is the PK for your view, then you can add this as a clustered index.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by imsam67 Monday, September 13, 2010 6:06 PM
    Monday, September 13, 2010 5:59 PM
  • It actually isn't the PK field but I'll be running some select statements that will use the data in this varchar field in the WHERE clause.
    Thanks, Sam
    Monday, September 13, 2010 6:05 PM
  • Then try to make it Non-Clustered.
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by imsam67 Monday, September 13, 2010 6:06 PM
    Monday, September 13, 2010 6:06 PM
  • Thank you very much
    Thanks, Sam
    Monday, September 13, 2010 6:07 PM
  • Unique or not.

    I'm reading up on this as we speak but want to make sure I get advice too. Thx


    Thanks, Sam
    Monday, September 13, 2010 6:08 PM
  • U can't decide type of index based on datatype.

     

    You have any other columns which are unique? Its always better to choose the clustering key having less size, if there is chance...  If you have any integer column or datetime columns or some fixed length columns wbich are unique, its better to keep that column as Clustered... Even if not such column, keeping an identity column as clustered key, is also an gud idea...

     

    if you don't have such kind of columns and your varchar column is of less size and unique, then make this column as clustered. Otherwise, I would suggest you to make varchar column as non-clustered.

    • Marked as answer by imsam67 Monday, September 13, 2010 6:24 PM
    • Unmarked as answer by imsam67 Monday, September 13, 2010 6:24 PM
    • Marked as answer by imsam67 Monday, September 13, 2010 6:26 PM
    Monday, September 13, 2010 6:09 PM
  • The PK is an integer column but as I mentioned above, I'll have to look up records based on the data in the varchar field. Therefore, I'm adding a second index to the view that will be the base data source in my queries.

     


    Thanks, Sam
    Monday, September 13, 2010 6:26 PM
  • Then you can add clustered index on PK column and non-clustered index on the varchar column.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 13, 2010 6:27 PM