Forum Discussion

Dixus's avatar
Dixus
Copper Contributor
Nov 14, 2023

SQL Server not returning (all) rows (Schrödingers Database)

Hi, i haven't seen this in 20 years of my experience. We tried nearly everything to figure out what's happening here. I hope someone can explain this.. The facts: There is a view that returns a ...
Dixus's avatar
Dixus
Copper Contributor
Nov 15, 2023

LainRobertson it did not. Look at the screenshot on it's not there..

bake13's avatar
bake13
Icon for Microsoft rankMicrosoft
Nov 15, 2023

Hi Dixus -- This is interesting, but I am with LainRobertson in that without seeing the view logic there are simply too many variables.  My only other idea is to check your ANSI_NULLS setting on the connection/table/view etc. to ensure the value is not being eliminated, though without additional testing I'm not sure that would address all of your scenarios.  Take care.

  • bake13's avatar
    bake13
    Icon for Microsoft rankMicrosoft
    Nov 21, 2023

    Hi Dixus -- Glad you solved it.  Nice work.  Take care.

  • Dixus's avatar
    Dixus
    Copper Contributor
    Nov 21, 2023
    The problem was the error in that subquery (sort by was wrong). I think that different execution plans ran into different outputs. Depending on the conditions.
  • Dixus's avatar
    Dixus
    Copper Contributor
    Nov 15, 2023
    Thanks for the try and the help.

    At least I could figure out, that the issue is one property that is caclulated using CASE (SELECT TOP 1 ... ) 1 else 0 END

    The column is 1 or 0 depending on the condition you give (WHERE orgid >= 9952 or without that). This is scary.

Resources