Results 1 to 9 of 9

Thread: Too Many

  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: Too Many

    I am using SQL Server 2000

    There is an install process that creates a new clean instance of our database, then populates some of the tables with default data. Following database creation, the data is copied from the old database to the new. One table in particular has no default data and is empty following the database creation phase. That table has 267 columns in it (combination of char, varchar, int, tinyint). I have 4 indices that I need to add to the table, but only one will add, and it has only 6 columns. When I attempt to add more indices, I get the following error...

    "Cannot create more than 249 nonclustered indices or column statistics on one table".

    Currently, the table has only one index and I cannot add more. In the prior version of the database, I was able to add all 4 indices. At most, there are only 4 or 5 new fields in this version. I am out of ideas on what to do. Can anyone help?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    maybe you can attache the ddl for that table..incluse all triggers pks, other constraints as well...

    when you sp_depends on that table what do you get?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Step 1: look at any table with over 200 columns as a candidate for normalization.

    Step 2: Turn off Auto-Create statistics on the database.

    Step 3: Add your indices.

    Step 4: Turn Auto-Create statistics back on.

    What likely happened was that SQL Server automatically created statistics for 248 of the 267 columns, because they showed up in a where clause of some query that was run. Turning off Auto-Create Statistics should remove the statistics, then you should be able to run the index scripts with no problems. Good luck.

  4. #4
    Join Date
    Feb 2004
    Posts
    24

    Thanks

    1. it is a simple columnar table with no triggers and only one stored procedure and UDF for reporting.
    2. sp_depends shows no dependancies other than the UDF mentioned above

    3. tried the AUTO_CREATE_STATISTICS database option... no dice... still giving me the same error. I went to Query Analyzer, copied the code I am using to create the indices there. Then added the Alter database ... AUTO_CREATE_STATISTICS on/off. Then removed all existing indices (only one there), then ran the script. It bombed with the same error
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Created no problem here....but then I don't mess around with any of the settings...

    Close cursor on commit?

    And Holy denormalized table batman
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    24
    You're right!! I took that script, modified it to create Play2 and add the indices to it, and it ran flawlessly. So, there must be something else weird in the way the install script is creating that table. I'll have to mention this to the guy who is managing the database layout and building the install CDs.

    It may actually work if I use that script to recreate the Play table. Simply drop the table after the install creates it, then use the script to recreate it. I'll bet that will work.

    Thanks.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No Miracles...

    Tell the guy to go step by step though the install procedures to find out what's up....

    If you can't find remove some of the steps until it works..that way you can terget the problem

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. I guess turning auto-create statistics does not delete existing statistics. Try this:

    Code:
    select name
    from sysindexes
    where id = object_id('your table goes here')
    This query will list out all of the indexes and statistics on your table. Statistics that are automatically generated are named with _WA_Sys_columnname_other_stuff

    Assuming the statistics are on one of the columns you want to index, you can drop the statistics with
    Code:
    drop statistics tblname._WA_Sys_fName_0DAF0CB0
    then immediately build the indexes. Hope this helps.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    After reviewing the script, you could probably change the [PlayMaint1] index to a clustered index. If nothing else, this will get you one index closer to your goal, since you are allowed one clustered in addition to 249 non-clustered indexes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •