Can not change index INCLUDE fields order....

Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch