Post reply

IF EXISTS error.....

  • April 10, 2003 at 3:58 pm

    #58618

    Hi All,

    I am trying to fix one broken application, found this. In one of the procedures I found the following script in IF EXISTS:

    quote:

    select * from sysindexes where id = (select id from sysobjects where name like 'Names_Data') and indid != 0

    When I ran the same in Query Analyser, It gave me the following:

    quote:

    _WA_Sys_Last_Name_5EBF139D

    _WA_Sys_First_Name_5EBF139D

    _WA_Sys_Middle_Initial_5EBF139D

    I don't understand how these Indexes got created. We have not created them. It seems to me (by name) that these are system indexes.

    Since these are appearing even if the object we are looking is not EXISTS, the statement is failing. So I have changed the above statement as follows:

    quote:

    SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.Names_Data') AND name='SS_CIDX'

    Anyone have any thoughts how these getting created. I am running SQL Server 7.0.

    Thanks & Regards,

    Murali Damera.

    .

  • April 10, 2003 at 5:47 pm

    #453813

    Oh gurus,

    I'd also love to know what's up with these, apparently, dynamically created indexes. We've got several floating around in our production databases. Is there a methodology, or even need, to manage these objects?

    Thanks,

    SJTerrill

  • April 11, 2003 at 1:05 am

    #453832

    Those indexes are created by SQL Server when you have the 'Auto Create Statistics' on.

    They get created when the optimiser needs to create distribution statistics on a non-indexed column, in order to work out the best execution plan for a query.

  • April 11, 2003 at 7:07 am

    #453872

    So, if I consistently see these... consider an index on the columns effected?

  • April 11, 2003 at 8:30 am

    #453893

    Consider it, but analyse your queries to see whether they really require an index on those columns.

    It may just be that somebody ran a 'one-off' query, and SQL Server wanted stats on those columns to help in forming an execution plan for that query.

    I believe you can get rid of those 'indexes' by doing a 'drop statistics'

  • April 11, 2003 at 10:17 am

    #453918

    Ah... Thanks!

    Just took a look at another database we've got here... It's got over 600 of those objects. Not a one-off!

    Off to the races.

    Thanks Again,

    SJTerrill

  • April 13, 2003 at 6:27 pm

    #453997

    Thanks to All. my problem solved.

    .

  • April 14, 2003 at 1:40 am

    #454010

    suggestion: don't get rid of those objects, the optimizier needs them (at least partly). they won't disturb you when dropping objects.

    if you don't want to see them when selecting indexes from dicitionary, use:

    INDEXPROPERTY(table.id, index.name, 'isstatistics') = 0

    INDEXPROPERTY(table.id, index.name, 'ishypothetical') = 0

    regards,

    chris.

  • April 14, 2003 at 9:29 am

    #454085

    Thanks, cneuhold. I've got some more questions regarding these objects... I'll start another thread.

    SJTerrill

  • April 17, 2003 at 7:38 am

    #454523

    This is my input to index status. If you set to not automatically recompute distribution statistics, then you should manually run update statement periodically (when a large amount of data in an indexed column has been added, changed, or deleted). Also if you truncate a table, stats will be deleted also, so you will need to run update statistics again.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply