none
Statistics still exist after dropping it RRS feed

  • Question

  • I have dropped the statistics but after dropping it still exist.

    Does anyone know how can i delete that?

    Actually i need to alter the column that depend upon statistics. But somehow after deleting it still exists.

    Friday, April 5, 2013 4:34 AM

Answers

  • Thanks Lathessh for valuable response.

    I have got the solution. Actually there is auto create statistics and auto update statistics was set to True so i set to False and try again and it's work.

    • Marked as answer by Chickoo79 Friday, April 5, 2013 6:38 AM
    Friday, April 5, 2013 6:38 AM

All replies

  • You do not really worry about the statistics on the column to alter the column. I beleive, your auto create statistics are causing the automatic creation of the statistics.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 5, 2013 4:49 AM
  • I cant alter the column until statistics exist on that column. 

    When i alter the column will get the message, statistics depend upon the column.

    So i have to drop the statistics first before altering column (Computed column), but after dropping the statistics, it still exist. I really dont have clue how to drop that stats.

    Friday, April 5, 2013 4:56 AM
  • It looks like you have a user statistics created on the column. Do you have any trigger on the table? Or someway any proc or job creating the statistics?

    Use the below script:

    sp_helpstats '<tableNAme>', 'ALL'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by SQLZealots Friday, April 5, 2013 5:14 AM
    Friday, April 5, 2013 5:10 AM
  • Is there any way to drop user statistics. Yes there are Insert, Update and Delete trigger on this table.

    As we have lots of jobs and proc, it's is very difficult to tell if anyone of them creating statistics with the same column.

    Is there any way to check?

    Friday, April 5, 2013 5:14 AM
  • You can drop user statistics as below:

    DROP STATISTICS dbo.Titles.us_name;

    however, the stats are again creating by trigger/job/proc etc. First,we need to identify what creates it probabaly.Check your triggers for the same also.

    Select 

    * From sys.sql_modules Where definition like '%CREATE STATISTICS%'                                                                         

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 5, 2013 5:20 AM
  • I have dropped the stats using same command. It says command successfully completed but when i check again using

    sp_helpstats it's still there.

    I have also check existence of any proc or job using the script provided by you, but couldn't find any result.

    Friday, April 5, 2013 5:44 AM
  • We need to ideally find out the way its being created automatically and stop and change the column.

    However, you can try as below:(It will not be a good idea though depends on what you are changing etc etc)

    create Table T1(col1 varchar(10))
    Create statistics ST on T1(Col1)
    Begin Tran T1
    Drop statistics T1.St
    Alter table t1 Alter column Col1 varchar(10)
    Commit tran T1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 5, 2013 5:57 AM
  • Thanks Lathessh for valuable response.

    I have got the solution. Actually there is auto create statistics and auto update statistics was set to True so i set to False and try again and it's work.

    • Marked as answer by Chickoo79 Friday, April 5, 2013 6:38 AM
    Friday, April 5, 2013 6:38 AM
  • Hi DBA, I wont suggest you to keep false always. It should be ideally TRUE.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 5, 2013 7:00 AM
  • I have set that to false for just column altering. Set it back to true already.

    Thanks for information.

    Friday, April 5, 2013 7:02 AM