SQL Server VIEW results different to the SELECT

Question

I have created the following simple view:

CREATE VIEW cs.MyTestPO AS
SELECT ttdpur401100.t_orno
, 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_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

If there is truly a difference between selecting from a view and running its definition ad-hoc, I would:

• Run sp_refreshview
• Run dbcc checkdb
• Drop and recreate the view
• Make sure SQL is patched up to the most recent service pack.
• ~~~~~Probably a lot of other steps that an MVP would think of go here~~~~~
• Restart the SQL Server instance (we're starting to reach, now)?
• Reboot the server?
• Contact support services (this would be getting serious).

It would be interesting to see if there's a difference in execution plans, statistics io, etc. before doing any of this, though.