The instance was upgraded, yet a new function still fails in one database. Compatibility level 170 controls several SQL Server 2025 behaviors independently of the engine version.
Read the Engine and Database Separately
Installing SQL Server 2025 changes the engine running your databases. Each database also has its own compatibility setting. Restored databases retain a supported older level instead of automatically adopting every new query behavior. That separation gives you room to test application queries after upgrading the server.
I check both values before troubleshooting a missing feature. An engine version from SERVERPROPERTY answers what software is running. The database row answers which compatibility behavior applies here. A server upgrade announcement can be perfectly accurate while telling you only half the story.
SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion,
SERVERPROPERTY('ProductMajorVersion') AS EngineMajorVersion,
DB_NAME() AS CurrentDatabase;
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();For SQL Server 2025, the major engine version is 17. Older engine versions cannot accept level 170. Also check the query window's database selection. Testing in master while diagnosing an application database introduces a particularly avoidable distraction.
Know What the Higher Level Enables
Compatibility level 170 enables new optimizer behavior and selected language features. REGEXP_LIKE and REGEXP_SPLIT_TO_TABLE require it on SQL Server 2025. Optional parameter plan optimization also needs this level and its database scoped configuration enabled.
Do not assume every feature introduced with the engine has the same requirement. Some other regular expression scalar functions work without this particular compatibility gate. Backup compression options belong to backup commands and engine support. Check the requirement for the feature you actually intend to use.
The setting also enables the cardinality estimation changes associated with the new level. Estimates influence access paths, joins, and memory grants. A different plan is expected in some cases. Better overall workload behavior is the objective, rather than keeping every plan drawing identical.
Establish Query Store Before Changing Anything
Query Store gives you historical plans and execution statistics within the database. Enable it before the compatibility change and collect a representative workload. A baseline from a quiet lunch break says little about a busy month-end process. Include scheduled reports, background jobs, and important parameter variations.
These examples use an existing test database named CompatLab. Start with a restored application copy before scheduling a production change. Review storage limits and retention settings rather than relying on a default allocation. Query Store needs enough space to retain both sides of your comparison.
ALTER DATABASE [CompatLab] SET QUERY_STORE = ON;
ALTER DATABASE [CompatLab]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
USE [CompatLab];
GO
SELECT actual_state_desc, desired_state_desc,
current_storage_size_mb, max_storage_size_mb,
query_capture_mode_desc
FROM sys.database_query_store_options;An enabled setting alone does not prove useful capture. Check that actual_state_desc is READ_WRITE and that your important statements appear. Read-only state, restrictive capture, or exhausted storage leaves holes in the evidence. Resolve those gaps before you change query behavior.
Move to Compatibility Level 170 in a Controlled Window
Record the original database setting outside the query session. Also record the change time and relevant scoped configurations. Keep those facts with your test results. A rollback value remembered from another database is a poor substitute for the value you actually read.
Change only the database selected for this test. The command affects its query compilation behavior and clears its plan cache. Expect fresh compilation afterward. It does not install a new engine or alter every database on the instance. Avoid combining this change with unrelated schema or index deployments.
ALTER DATABASE [CompatLab]
SET COMPATIBILITY_LEVEL = 170;
GO
USE [CompatLab];
GO
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN
(N'OPTIONAL_PARAMETER_OPTIMIZATION',
N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION');
Confirm Compatibility Level 170 With a Small Test
Use a harmless statement to check the language gate. The following input values are chosen examples, rather than real application data. The predicate accepts an uppercase prefix followed by four digits. Inspect the returned codes and verify they match that stated rule.
SELECT CodeValue
FROM (VALUES (N'AB1234'), (N'ab1234'), (N'AB12')) AS v(CodeValue)
WHERE REGEXP_LIKE(CodeValue, N'^[A-Z]{2}[0-9]{4}$', 'c');
SELECT value
FROM REGEXP_SPLIT_TO_TABLE(N'red,green,blue', N',');The split output does not establish a contractual sort order by itself. Add an appropriate ordering method when downstream logic requires one. For application validation, also decide how NULL, empty strings, and unexpected length should behave. A syntax test only confirms that the feature is available.
Compare Comparable Query Store Windows
I compare the important queries individually before accepting an upgrade. Aggregate totals hide changes in execution count and parameter mix. A query run once after the change does not provide the same evidence as a heavily exercised baseline. Inspect both duration and workload volume.
Run this query in CompatLab after capturing the two periods. Replace the time values with your recorded UTC test windows. Query Store statistics summarize intervals, so use interval boundaries for a cleaner comparison. Partial overlaps include the whole matching interval in this example.
DECLARE @ChangeUTC datetimeoffset = '2026-01-15T12:00:00+00:00';
DECLARE @BeforeUTC datetimeoffset = DATEADD(HOUR, -2, @ChangeUTC);
DECLARE @AfterUTC datetimeoffset = DATEADD(HOUR, 2, @ChangeUTC);
SELECT q.query_id, p.plan_id,
CASE WHEN i.end_time <= @ChangeUTC
THEN 'Before' ELSE 'After' END AS TestPeriod,
SUM(s.count_executions) AS ExecutionCount,
SUM(s.avg_duration * s.count_executions) /
NULLIF(SUM(s.count_executions), 0) / 1000.0 AS AverageDurationMs,
SUM(s.avg_cpu_time * s.count_executions) /
NULLIF(SUM(s.count_executions), 0) / 1000.0 AS AverageCpuMs
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS s ON s.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = s.runtime_stats_interval_id
WHERE s.execution_type = 0
AND i.end_time > @BeforeUTC
AND i.start_time < @AfterUTC
AND (i.end_time <= @ChangeUTC OR i.start_time >= @ChangeUTC)
GROUP BY q.query_id, p.plan_id,
CASE WHEN i.end_time <= @ChangeUTC
THEN 'Before' ELSE 'After' END
ORDER BY q.query_id, TestPeriod, p.plan_id;Investigate Regressions Before Blaming the Number
The query excludes intervals crossing the change time. That prevents mixing the two settings inside one summary. Weighting averages by execution count handles multiple statistics rows correctly. The underlying duration and CPU columns use microseconds, so the expression converts their averages to milliseconds.
When a query regresses, open both plans in SSMS. Compare estimates, actual execution evidence, grants, spills, and parameter values. Check statistics freshness and workload changes too. Multiple plans can reflect useful parameter variants. Do not label every extra plan a failure.
Query Store plan forcing provides a targeted response when a suitable prior plan remains usable. Test it and verify forcing succeeds. Keep a removal plan for that temporary intervention. A single problematic statement should prompt focused analysis before you abandon improvements across the workload.
Keep a Tested Route Back From Compatibility Level 170
What will happen to new application code if you lower the setting? REGEXP_LIKE calls introduced during the deployment stop working below their required level. Coordinate code rollback with database rollback. Changing a number cannot undo a dependency already added to an application.
If your recorded original level was 160, this command restores that value. Use the actual recorded value for another database. Run the representative workload again and review new compilations. Lowering the setting leaves the SQL Server 2025 engine in place. It does not restore an older engine's security fixes or storage format.
ALTER DATABASE [CompatLab]
SET COMPATIBILITY_LEVEL = 160;Approve compatibility level 170 after feature tests and workload comparisons support it. Keep the baseline, change time, and exception decisions available. That record makes later plan changes much easier to explain than a vague recollection that the upgrade seemed fine.
Related reading on this blog: Impact of Changing Database Compatibility Level on Cache and SQL SERVER 2022: Oldest Compatibility Level Supported.
A compatibility level is not the engine version, it is a database behavior setting.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
