sql server - Is there any way to keep the data from a view in sqlserver is always right?

Question

This problem happened at a SQL Server Express 2019, my SQL script is not much special which with another view in it and did a left outer join connection.

In my situation, the SQL script, there's one record was returned, and one of its field names "DeleteFlag" returns 0 when not in the view, but it changed when put into the view be the content of it, just returns 1!

It's so weird that I tried times to find out the discrepancy, but no result. And in the process, the situation is stably reproducing.

The problem disappeared after I operated the auto generated alter view script to refresh it. But when I restored the backup database, It reproduced then. every time I restore the DB backup, it reproduced!

Reproduce steps and related files:

1. Unpack and restore the *.bak file (test111.zip), you'll get a database named "ADS_STARTUP_DB_2401"

2. Do a selection on the view [medDispense].[PatientProfile], you'll get one row and the column names DeleteFlag, the value is 1:

    SELECT DeleteFlag 
    FROM [medDispense].[PatientProfile]
   

3. Then go to the definition of [medDispense].[PatientProfile]:

    CREATE VIEW [medDispense].[PatientProfile] 
    AS
        SELECT DISTINCT
            inventory.StationID, patientProfile.* 
        FROM 
            V_PATIENT_PROFILE patientProfile 
        LEFT OUTER JOIN 
            V_INVENTORY inventory ON patientProfile.Billnum = inventory.Billnum 
        WHERE 
            (patientProfile.Active = 1) 
            AND (patientProfile.DeleteFlag = 0) 
            AND (inventory.DeleteFlag IS NULL OR inventory.DeleteFlag = 0)
   

   Then, operating the bold part statement, you'll see one record and look at the column DeleteFlag, you'll see the value is 0. The issue is just here.

So, I'm thinking if this problem happened one day at the users' program which is connecting a SQL Server 2019 Express. The data result was definitely wrong, but there's no fault to any developer. It would be so weird.

So is there a way to keep the data from a view is always right?

question from:https://stackoverflow.com/questions/65894904/is-there-any-way-to-keep-the-data-from-a-view-in-sqlserver-is-always-right

Answer 1

Waitting for answers