CODEWITHSUNDEEP
sql-serversql-server-2016
Dmitriy
Dmitriy

Reputation: 135

MS SQL query results vary depending on set of columns

Please help me find solution to the following issue. I have 2 queries:

SELECT tu
FROM testview1
WHERE tu = 1;
SELECT *
FROM testview1
WHERE tu = 1;

First query returns 21 rows, second - 36. testview1 is a view which has 3 colums, all of them are int.

CREATE VIEW testview1 AS
  SELECT
    rn,
    tu,
    id
  FROM t1
  UNION ALL
  SELECT
    rn,
    tu,
    id
  FROM t2

What could be a reason for such difference?

Upvotes: 1

Views: 119

Answers (1)

sepupic
sepupic

Reputation: 8697

Your view has top () without order by, so the result of this select depends on the order that server chooses to return you the rows.

Two queries produces 2 different plans because of different columns requested and server chooses ODRERED index scan in the "right" plan and NOT ORDERED in "wrong" plan.

As the result, different rows are filtered further and the final number of rows is different

Upvotes: 1

Related Questions