locked
Table design considerations RRS feed

  • Question

  • User1856468055 posted

    I have a table with following columns

    Id: Identity Column
    FileName: varchar
    Time: varchar
    Company: varchar
    Mod: varchar
    YearMonth: varchar

    This data I am getting from source and inserting in SQL server table

    I then have a search page where the users can search on the above table and the results would be shown in a grid.

    The search parameters can be company, mod and range of yearmonth.

    Now my questions is designing the index on the above table.

    Shall I include all the columns of my search param and make them index ie company, mod, yearmonth or just one of them.

    Also note that each of would not have unique values.

    Would appreciate inputs

    Friday, September 29, 2017 6:01 PM

All replies

  • User475983607 posted

    You should always use proper types otherwise you'll constantly need to cast from a string and validate.

    Change Time to a Time type.  Change the YearMonth to an INT that must be greater than, at least, 99999.  Perhaps greater than 190000.

    Shall I include all the columns of my search param and make them index ie company, mod, yearmonth or just one of them.

    Create indexes on the fields or combination of fields that will be queried most.

    Friday, September 29, 2017 6:23 PM
  • User-271186128 posted

    Hi John,

    Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

    You should avoid adding unnecessary columns. Adding too many index columns can adversely affect disk space and index maintenance performance.

    Consider the order of the columns if the index will contain multiple columns. The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first.

    Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. For more information, see Database Engine Tuning Advisor. https://technet.microsoft.com/en-us/library/hh231122(v=sql.110).aspx

    SQL Server Index Design Guide https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

    Best regards,
    Dillion

    Friday, October 13, 2017 9:02 AM