How to disable all table indices in sql server compact edition via SqlCeCommand object?

Question

I am trying to make a huge insert into the SqlCe database (version 3.5, oh and using C # 3). I tried various ways to do this (table adapter tab, prepared parameterized query, sqlceresultset insert, etc.). The fastest time I read on many sites used the SqlCeResultSet object in table mode.

I want to speed it up even further by disabling all the indexes in the required tables before doing the insert (then I will rebuild them later). I have a problem with the ALTER INDEX statement working (I've never used it before, so I'm sure I'm missing something simple).

Basically, I created the SqlCeCommandObject object and configured the alter statement as follows (the Connection object is already configured and open, etc.):

. . . SqlCeCommand disableIndexCommand = Connection.CreateCommand(); disableIndexCommand.CommandText = "ALTER INDEX ALL ON ItemMaster DISABLE"; disableIndexCommand.ExecuteNonQuery(); . . . 

When I try to run this code, I get the following error:

An error occurred while parsing the request. [Token line number = 1, token line offset = 7, Token in error = INDEX]

It seems to me that this is not an understanding of the INDEX keyword in the alter statement. Is this not supported in SqlCe?

thanks

[UPDATE] - I tried other DDL statements (like DROP INDEX, CREATE INDEX), and I can't get them to work? I searched everywhere, trying to find a list of what is and what is not supported in SSCE, regarding DDL statements. Bad luck. Does anyone know any good resources for this. The MS documentation on compact version 3.5 sqlce was completely useless, I have to say ...

Answer 1

DROP INDEX syntax on SqlServerCe:

 DROP INDEX [TableName].IndexName 

There is no ALTER INDEX syntax in SqlServerCe, so when he says “parsing errors” in “INDEX”, because he is looking for “TABLE”, the only thing ALTER is true for SqlServerCe.

EDIT: Regarding the documentation, the best way I've found is to download Books Online . If you are not using 3.5 SP1, look here for a more complete list of available downloads.