July 10, 2003 at 10:04 am
Hi All,
I am facing a problem when trying to create a nonclustered-index on a table.
This table has 515 columns (All data in table is flushed).
and has a composite index on 4 columns.
Now I alter the table and add another column and want to index that column.
The index creation is failing with following error-
Server: Msg 1910, Level 16, State 1
Cannot create more than 249 nonclustered indices or column
statistics on one table.
The confusing part is that this behaviour is not uniform across all
databases. In another instance the index creation went through successfully
under identical conditions. I need an explanation for this and a workaround.
Please help..
TIA
Sumedh.
July 10, 2003 at 12:30 pm
My guess is you already have 250 indexes on that table. Check your sysindexes table. Execute the following query and see how many rows it returns. If it returns 250 you cannot create any more indexes, if it less than 250, since your table is already empty, script out the table, drop and re create with the new index you want to add
Select count(*) from sysindexes where id = ( Select OBJECT_ID('Table_Name') )
Shas3
July 10, 2003 at 2:56 pm
Hi shas3,
Thanks for ur prompt reply.It was spot on.
The db giving problems had 251 entries in sysindexes while other one had 7 for
that table !!
In my case i dropped and recreated the table. But in case that isnt possible
i was wondering what could be the easiest way to put the index ?
I noticed many entries in sysindexes corresponding to columns that arent indexed
(_WA_Sys_[column names]). guess the optimizer must be making these. When I recreate
the table with 2 indexes initially just 4 entries are there.
Is there any stored proc which would delete these seemingly non mandatory entries in
sysindexes.
Thanks
Sumedh
July 10, 2003 at 3:42 pm
Your assumption is right, they are created by query optimizer. You can drop them by using DROP STATISTICS command. Be aware dropping statistics may affect the plan chosen by the query optimizer.
July 11, 2003 at 8:09 am
Yes you are right, SQL Server creates these to keep track of non-index statistics on columns, to capture the statistics even though there is no physical index on that. Since these are not real indexes you cannot use DROP INDEX, you must use DROP STATISTICS. But again it is not a good idea to drop these stats as the system created for the maintenance plan or whatever reason. Btw why don’t you normalize your table, do you really need 500+ columns in a table?
Shas3
July 11, 2003 at 9:12 am
SQL Server will only create these pseudo indexes if you have the 'Auto Create Statistics' option set on.
You probably have the option set on for the database with 250 indexes, and set off for the database with only 7 indexes.
July 11, 2003 at 10:39 am
Thanks shas3, allen, ian for your replies. Every pointer was useful
shas3, the table is used for interfacing diff applications, so it contains many columns which are just nulls presently but which may be mapped and used in future. I felt that too many columns in a table (70% of which are nulls in my case) can cause problems one of which i felt was inserts and updates being slow, single record spread over many pages.
There is now another important argument, that the optimizer creates statistics for all the non indexed cols for any table. Any index changes will be a problem. Also for my table the server couldnt create stats for approx. 50% of the columns due to insuff. space in sysindexes.
ian, the db with 7 index entries was freshly created and still no data was put in it. Auto Create Statistics is on by default and hadnt turned it off explicitly. But i will try this option out as a test.
sumedh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply