Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Why create statistics on every column of every table in a database. RE: Why create statistics on every column of every table in a database.

  • March 24, 2010 at 12:19 pm

    #1139153

    Maybe this argument will help, too:

    Within one query, SQL server will only use one index per table. So, if you have the following querySELECT col1, col2, col3, col4

    FROM table

    WHERE col1='something' AND col2='something different'

    and you have an index on each and every single column then the query optimizer may not consider any of those indexes, since a bookmark lookup would be required to get all values used in the SELECT clause.

    It might be appropriate to use a single index on col1 and col2 with col3 an col4 as included columns.

    SQL server will not combine various indexes for one table within one query.

    Furthermore, it might even drop performance if the table in question is heavily used for updates/inserts/deletes, since each and every index would need to be changed.

    Edit: Incorrect answer.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]