databaseazure-sql-databasevisual-studio-2022sql-server-2019schema-compare

Is there a way to ignore the "STATISTICS_NORECOMPUTE" clause when doing a schema compare in Visual Studio?

I have a database project in my Visual Studio (2022) solution that I just synced to the live database, which is in SQL Azure. My dev database is in a local SQL Server instance (2019, I think). When I run the schema compare in Visual Studio with the project as the source and the local DB as the target, it shows a ton of tables are out-of-sync, but they only differ because the Azure db has the "WITH (STATISTICS_NORECOMPUTE = ON)" clause on the CONSTRAINT line. For example:

enter image description here

My questions are:

  1. Is there any way to prevent "schema compare" from adding this? Turning the "Statistics" option off didn't do anything.

  2. Will it hurt anything if I just update the SQL Server 2019 instance with the Azure version of the table definitions? I'm not a dba so that STATISTICS_NORECOMPUTE doesn't mean anything to me.

Solution

  • Is there any way to prevent "schema compare" from adding this? Turning the "Statistics" option off didn't do anything.

    AFAIK Unfortunately, in Visual Studio's schema compare tool, there is no built-in way to ignore specific differences like the WITH (STATISTICS_NORECOMPUTE = ON) clause. The Statistics option you're referring to only affects actual statistics objects, not table constraints or index options like STATISTICS_NORECOMPUTE.

    To avoid these differences in schema comparisons you can choose not to apply those specific changes when applying the schema update by manually reviewing the generated SQL and excluding the changes related to STATISTICS_NORECOMPUTE.

    1. Will it hurt anything if I just update the SQL Server 2019 instance with the Azure version of the table definitions? I'm not a dba so that STATISTICS_NORECOMPUTE doesn't mean anything to me.

    STATISTICS_NORECOMPUTE is an index option that prevents SQL Server from automatically updating statistics for a table or index. Statistics help the SQL Server query optimizer to determine the most efficient way to execute a query.

    If you apply WITH (STATISTICS_NORECOMPUTE = ON) to your local SQL Server 2019 instance, it won't immediately harm anything, but you should be mindful of potential performance issues if the table's data changes frequently data changes frequently.