VIEW results differ to underlying SELECT results

Question

I have created the following simple view:

CREATE VIEW cs.MyTestPO AS
SELECT ttdpur401100.t_orno
, ttdpur401100.t_pono
, ttdpur401100.t_sqnb
, ttdpur401100.t_item
, ttcibd001100.t_dsca
FROM ttdpur401100 
	JOIN ttcibd001100 ON ttcibd001100.t_item = ttdpur401100.t_item

Then SELECT * FROM cs.MyTestPO returns 276376 rows

If I run the select statement by itself 

SELECT ttdpur401100.t_orno
, ttdpur401100.t_pono
, ttdpur401100.t_sqnb
, ttdpur401100.t_item
, ttcibd001100.t_dsca
FROM ttdpur401100 
	JOIN ttcibd001100 ON ttcibd001100.t_item = ttdpur401100.t_item

it returns 277488 rows

Can anyone suggest why the view is missing 1112 rows that the SELECT statement returns.

Answer 1

Rebuild your indices and statistics on your table and - if they exists - on your view.

Answer 2

Does it return two rows?

 

select *
from sys.tables
where name in ('ttdpur401100', 'ttcibd001100')

 

If there are more then two rows then there are same table names in different schemas with different content.

Answer 3

I find it odd that you schema qualify the view name, but not the underlying tables (or are they views?).  Perhaps you should qualify those names as well (which is a best practice in any event).

Answer 4

Excellent - many thanks for that.