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

Reputation: 23

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.

Upvotes: 0

Views: 79

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8402

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.

  • Turning off auto-updates (STATISTICS_NORECOMPUTE = ON) could cause performance degradation if the statistics become stale over time.

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.

Upvotes: 0

Related Questions