Filtered index syntax error: WHERE and WITH clauses swapped

shawnC

SQL Data Compare 10.2.3.5 (and earlier) generates incorrect syntax for restoring filtered indexes that have a FILLFACTOR clause

The WHERE and WITH clauses are the wrong way around

What is generated:

CREATE UNIQUE NONCLUSTERED INDEX [IXU_Foo] ON [Data].[Bar] ([BarID], [BarName]) WITH (FILLFACTOR=90)  WHERE ([BarName] IS NOT NULL) ON [PRIMARY]

This means that I have to use SQL Compare to fix the schema after synchronizing data and can't trust SQL Data Compare

Can this be fixed please?

Brian Donahue

I'm sorry you have encountered this issue. I will be responding to you via email with the information relevant to troubleshooting this problem.

Thanks!

shawnC

Note: this *only* happens when you use a filtered index.

Using a WITH clause alone works OK. But not when you have a WITH and WHERE clause.

So there's no confusion...

Brian Donahue

Thank you. I have logged a bug with our development team ( SDC-1502 ).

shawnC

One year later... any update please?