DB2迁移至SQL Server 2016后视图列值显示不稳定问题求助

Hey there, I’ve dealt with similar inconsistent query behavior when migrating from DB2 to SQL Server—let’s walk through the likely causes and fixes for your issue:

Key Observations from Your Problem #

• The view uses a CTE filtered with getdate() and a left outer join to the main table
• Direct queries against CONTACT.CONTACT_LIST and CONTACT.ADDRESS work consistently
• View results have intermittent NULLs in columns pulled from the CTE, even with static test data

Likely Causes & Fixes #

1. Outdated Statistics (Most Common Culprit) #

After migrating data, SQL Server’s query optimizer relies on outdated statistical information (or no stats at all) from the migration to generate execution plans. This can lead to it making bad decisions about which rows to retrieve.

Fix: Update statistics for the affected tables manually:

UPDATE STATISTICS CONTACT.ADDRESS;
UPDATE STATISTICS CONTACT.CONTACT_LIST;

You can also enable auto-update stats if it’s not already turned on, but running this manually first will rule out stale stats as the root cause.

2. Non-Deterministic getdate() Causing Plan Instability #

getdate() is a non-deterministic function—it returns a slightly different value each time it runs. When used in a CTE filter like getdate() <= coalesce(ADDRESS.EXPIRATION_DATE, getdate()), SQL Server’s optimizer might generate inconsistent execution plans because it can’t cache a plan that accounts for the changing date value.

Fix: Replace getdate() with CURRENT_TIMESTAMP (a deterministic equivalent) and simplify the filter logic to avoid repeating the date function:

-- Revised CTE filter
WHERE ADDRESS.ADDRESS_TYPE_ID = 1 
  AND (ADDRESS.EXPIRATION_DATE IS NULL OR ADDRESS.EXPIRATION_DATE >= CURRENT_TIMESTAMP)

This makes the filter logic clearer and more stable for the optimizer to work with.

3. CTE vs. Subquery Optimization Differences #

DB2 and SQL Server handle CTEs differently—SQL Server sometimes "expands" CTEs into the main query, which can lead to unexpected predicate pushing (where filters get applied earlier than intended in a left join scenario).

Fix: Rewrite the view to use a subquery instead of a CTE and see if the behavior stabilizes:

CREATE VIEW CONTACT.ANTOXCUST2 ( clinicid, clinicname, address1, address2, city, stateabrv, countryid, postalcode ) 
AS 
SELECT cl.CONTACT_ID, 
       COALESCE(cl.CONTACT_NAME, ' '), 
       a.ADDRESS_1, 
       a.ADDRESS_2, 
       a.CITY, 
       a.STATE_ABRV, 
       a.COUNTRY_ID, 
       a.POSTAL_CODE 
FROM CONTACT.CONTACT_LIST AS cl 
LEFT OUTER JOIN (
    SELECT ADDRESS.ADDRESS_ID, 
           ADDRESS.CONTACT_ID, 
           ADDRESS.ADDRESS_1, 
           ADDRESS.ADDRESS_2, 
           ADDRESS.CITY, 
           ADDRESS.STATE_ABRV, 
           ADDRESS.COUNTRY_ID, 
           ADDRESS.POSTAL_CODE 
    FROM CONTACT.ADDRESS 
    WHERE ADDRESS.ADDRESS_TYPE_ID = 1 
      AND (ADDRESS.EXPIRATION_DATE IS NULL OR ADDRESS.EXPIRATION_DATE >= CURRENT_TIMESTAMP)
) AS a ON cl.CONTACT_ID = a.CONTACT_ID;

4. Test with Execution Plan Recompilation #

To confirm if the issue is tied to cached execution plans, run your view query with the OPTION (RECOMPILE) hint:

SELECT * FROM contact.antoxcust2 ac WHERE ac.clinicid = 4678 OPTION (RECOMPILE);

If this returns consistent results every time, the problem is a bad cached plan. You can either:

• Add the hint to queries using the view (temporary fix)
• Force the view to recompile periodically
• Adjust the view logic to make it more "optimizer-friendly" (like the subquery rewrite above)

Final Check #

Since your test environment has static data, none of the intermittent behavior should be data-related—focus on SQL Server’s optimizer and statistics, which are the biggest differences between DB2 and SQL Server in this scenario.

内容的提问来源于stack exchange，提问作者DLuchin