February 15, 2006 at 8:17 am
in sql 7.0 I have a table with many indices that show up in the "sp_help ATable". When I run a query using "where columnname = x" where the columnname is indexed I get a table scan - when I display the execution plan, it shows it using a Primary Key clustered index (that's going to cause a table scan).
When I try to drop the index I get "Cannot drop the index 'atable._WA_Sys_COMPANY_6576FE24', because it does not exist in the system catalog." When I try to create the index, I get "There is already an index on table 'Atable' named '_WA_Sys_COMPANY_6576FE24'."
All was fine and then it just BROKE - don't know that anything was done to cause the problem.
Loaded a backup to SQL 2003 and get same behavior.
Any ideas?
Thanks
DIck RIder
dick rider
February 15, 2006 at 9:41 am
The "index" _WA_Sys_COMPANY_6576FE24 is actually distribution statistics automatically generated for that column. Autostatistics just happen to show up like indexes, and in fact are stored in the sysindexes system table.
This is why you can't drop it (you need to use the "DROP STATISTICS" command to do that).
Create an index with a simple name - something that reflects the columns in the index key is generally a good idea. I'd do something like IX_NCI_ColumnName, which tells me that it's an Index, it's a Non-Clustered Index, and the key is on ColumnName. So long as the query only matches with a small number of rows, it should use the index.
If it matches with more than a very small proportion of the total rows in the table (>1 or 2%) it will probably scan the clustered index instead, since NC indexes get very inefficient very quickly as more records match the query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply