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?
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.
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
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.
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp http://www.sql-server-performance.com/rd_table_hints.asp http://www.devx.com/vb2themax/Tip/18617 .. to get you on. Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
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