WITH (STATISTICS_NORECOMPUTE=ON)

ebobs

I'm trying to compare a source SQL SERVER 2005 database with SQL destination SERVER 2000 databases but it keeps including the statement WITH (STATISTICS_NORECOMPUTE=ON) on all alter tables on the SQL SERVER 2005 side. How can I get the compare tool to ignore this?

Michelle T

Unfortunately there isn't a way to specifically ignore STATISTICS_NORECOMPUTE - the closest you can get is to ignore all indexes.

In order to file an appropriate feature request and maybe give you a workaround, I could do with the answer to a couple of questions:

By 'all alter tables', do you mean 'all indexes', or something else?

When you say 'it keeps including the statement WITH (STATISTICS_NORECOMPUTE=ON)', is this because everything in your 2005 database has STATISTICS_NORECOMPUTE=ON or is it something that SQL Compare appears to be adding?

ebobs

Hi,

I can confirm that the STATISTICS_NORECOMPUTE=ON isn't set on the SQL SERVER 2005 table, SQL Compare seems to add it on.
Below is an example of where it added it to the source table "Constraints and Indexes" window.

ALTER TABLE [dbo].[CodesArea] ADD CONSTRAINT [IX_CodesArea] UNIQUE NONCLUSTERED ([AreaCode]) WITH (STATISTICS_NORECOMPUTE=ON)

The ignore indexes would be no good for us.

Michelle T

SQL Compare isn't meant to be adding STATISTICS_NORECOMPUTE=ON to your unique constraints at this point, as far as I can see. Let's see if we can track down the problem.

1) When you script out a table with the SQL Server Management Studio 'Script as -> Create' menu option, does it have the STATISTICS_NORECOMPUTE?

2) If you compare the 2005 database to another 2005 database, does STATISTICS_NORECOMPUTE still appear?

3) Is it appearing when you compare to a blank 2000 database, or only when you compare to a 2000 database with a table of the same name / a UNIQUE constraint of the same name on that table?

ebobs

1) When I script the table out from Sql Server 2005 it doesn't have STATISTICS_NORECOMPUTE

2) I don't have two SQL 2005 database to compare, it's a locked down environment so I can't easily do this.

3) I'm comparing the same database schema between dev & uat. Dev is SQL 2005 and UAT is 2000. Both are populated with the sames tables and data.

Michelle T

You can compare the database to itself - you don't need a second SQL 2005 database.

ebobs

Yes it puts the WITH (STATISTICS_NORECOMPUTE=ON)
on the source and destination Constraints and Indexes.

Michelle T

It appears that STATISTICS_NORECOMPUTE doesn't show up in Management Studio's 'script out a table' output - could you possibly double-check that, in the properties for these indexes, on the 'options' page, the box 'Automatically recompute statistics' is checked?

I'm not making much progress on any other theories, unfortunately - I can't get the behaviour you describe to occur on any of my test databases.

1) I don't suppose you can send a backup of the 2005 database to michelle.taylor@red-gate.com so I can have a look at the actual database you're having trouble with? Failing that, a snapshot might help, but I suspect the problem is somewhere in SQL Compare's initial reading of the database, which would make that less useful than usual.

2) Failing that, if you can send the creation SQL for one of the tables that's exhibiting that behaviour, that might help (although without the rest of the database as context, the issue might not show up).