How do you disable all table indexes in sql server compact edition via an SqlCeCommand Object?

Question

I'm trying to do a huge bulk insert into an SqlCe database (version 3.5, oh and using C# 3). I've tried various ways of doing this (table adapter insert, a prepared parameterized query, sqlceresultset insert etc.). The fastest time, as I've read on many sites, was using the SqlCeResultSet object, in table direct mode.

I want to speed this up even further by disabling all of the indexes on the necessary tables before doing the insert (then I'll rebuild them afterwards). I'm having an issue getting the ALTER INDEX statement to work (never used it before, so I'm sure I'm overlooking something simple).

Basically I've created an SqlCeCommandObject and set up an alter statement in the following way (Connection object is already set up and is 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:

There was an error parsing the query. [ Token line number = 1, Token line offset = 7, Token in error = INDEX ]

It sounds to me like it is not understanding the INDEX keyword in the alter statement. Is this not supported in SqlCe?

Thanks

[UPDATE] - I've tried other DDL statements (like DROP INDEX, CREATE INDEX) and I can't seem to get any of them to work? I've been searching all over the place trying to find a list of what is and what isn't supported in SSCE, as far as DDL statements go. No luck. Does anybody know any good resources for this. MS documentation on the sqlce compact version 3.5 has been quite useless I must say...

Answer 1

The DROP INDEX syntax on SqlServerCe is:

DROP INDEX [TableName].IndexName

There is no ALTER INDEX syntax in SqlServerCe, so when it says "error parsing" at "INDEX", its because its looking for "TABLE", the only thing ALTER is valid for in SqlServerCe.

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