SQL Server Performance

"Ignore" indexes

Discussion in 'General DBA Questions' started by eramgarden, Jul 20, 2006.

  1. eramgarden New Member

    Is there such thing as "ignore indexes"..a way to do it. I know I can drop an index and recreate it but i dont want to do that...

    This is what I want to do:

    Ignore indexes on table1
    run an update command


    I know I can do this:
    drop indexes
    run an update command
    recreate them

    yes, no?
    eramgarden, Jul 20, 2006
    #2

  2. Luis Martin Moderator

    There is no ingnore indexes.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Jul 20, 2006
    #2

  3. joechang New Member

    there is a defect in the SQL Server 2000 optimizer in handling high row count updates where it uses a nonclustered index, when a table scan would be faster.
    if that is your issue, force the scan in the update, and let sql update the indexes
    joechang, Jul 20, 2006
    #2

  4. eramgarden New Member



    can I use "index hint" in update statements?
    eramgarden, Jul 20, 2006
    #2

  5. Luis Martin Moderator

    UPDATE
    {
    table_name WITH ( < table_hint_limited > [ ...n ] )
    | view_name
    | rowset_function_limited
    }
    SET
    { column_name = { expression | DEFAULT | NULL }
    | @variable = expression
    | @variable = column = expression } [ ,...n ]

    { { [ FROM { < table_source > } [ ,...n ] ]

    [ WHERE
    < search_condition > ] }
    |
    [ WHERE CURRENT OF
    { { [ GLOBAL ] cursor_name } | cursor_variable_name }
    ] }
    [ OPTION ( < query_hint > [ ,...n ] ) ]

    < table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ]
    | rowset_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

    < joined_table > ::=
    < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | < joined_table >

    < join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
    [ < join_hint > ]
    JOIN

    < table_hint_limited > ::=
    { FASTFIRSTROW
    | HOLDLOCK
    | PAGLOCK
    | READCOMMITTED
    | REPEATABLEREAD
    | ROWLOCK
    | SERIALIZABLE
    | TABLOCK
    | TABLOCKX
    | UPDLOCK
    }

    < table_hint > ::=
    { INDEX ( index_val [ ,...n ] )
    | FASTFIRSTROW
    | HOLDLOCK
    | NOLOCK
    | PAGLOCK
    | READCOMMITTED
    | READPAST
    | READUNCOMMITTED
    | REPEATABLEREAD
    | ROWLOCK
    | SERIALIZABLE
    | TABLOCK
    | TABLOCKX
    | UPDLOCK
    }

    < query_hint > ::=
    { { HASH | ORDER } GROUP
    | { CONCAT | HASH | MERGE } UNION
    | {LOOP | MERGE | HASH } JOIN
    | FAST number_rows
    | FORCE ORDER
    | MAXDOP
    | ROBUST PLAN
    | KEEP PLAN
    }



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Jul 20, 2006
    #2

  6. satya Moderator

    satya, Jul 21, 2006
    #2

  7. FrankKalis Moderator

    Data modifications happening to the base table will be immediately promoted to any other indexes on that table. I think that's by design, although other systems (like MySQL, IIRC) do have such a feature. You cannot ignore or delay that process. The only way here is to drop and recreate.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    FrankKalis, Jul 21, 2006
    #2

Share This Page