SAP Basis Community :: SAP Basis, Netweaver, ABAP, Portal, BW, Oracle, System Copy

If you want to create non-clustered indices on a MSSQL Database, normally you may get error as follow:

Request: Create Index LIPS-Z01 (R3DEV/03.01.06/06:32)                                         

Process: msisrv03_0                                                                            

sql:                                                                                           

CREATE                                                                                         

INDEX [LIPS~Z01] ON [LIPS]                                                                     

( [VGBEL] ,                                                                                    

[VGPOS] )                                                                                      

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

DDL time(___1): ........56 milliseconds                                                        

The SQL statement was not executed                                                             

Index   could not be created completely in the database                                        

Index LIPS-Z01 could not be created                                                            

Request for LIPS could not be executed        

Here is the solution:

- Connect to SQL Server and execute the following command to delete statistics

 

SELECT 'DROP STATISTICS [<systemname>]' + QUOTENAME(OBJECT_NAME(id)) + '.' + QUOTENAME(name) FROM

sysindexes WHERE OBJECT_NAME(id) = '<tabloadi>' AND INDEXPROPERTY(id, name,'ISStatistics') = 1

GO

 

- Then re-try to create index via SE14.