Clever Geek Handbook

SQL Server VIEW results differ from SELECT

I 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 it SELECT * FROM cs.MyTestPO

returns 276376 rows

If I run the select statement myself

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 returned by the SELECT statement.

+3


source to share


3 answers


to see the differences between the sets try



SELECT d
d.t_orno    , d.t_item    , c.t_dsca
FROM ttdpur401100 d
   inner  JOIN ttcibd001100 c ON 
c.t_item = d.t_item left outer join
cs.MyTestPO t on d.t_item = t.t_item
where
t.t_item is null
+1


source


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

  • Run sp_refreshview
  • Run dbcc checkdb
  • Uncheck and re-create the view
  • Make sure the SQL is patched to the latest service pack .
  • ~~~~~ Probably many other steps that MVP could think of, here ~~~~~
  • Reboot your SQL Server instance (we're starting to get it, now)?
  • Reboot the server?
  • Contact support (this will be serious).


It would be interesting to see if there is a difference in execution plans, io stats, etc. before doing any of this.

+3


source


There was just a problem with the SQL2008 instance. When I right clicked and scripted my view as creation, I could clearly see the hard-coded value in the view definition as "A". However, when I right clicked and selected the top 1000 rows, the hardcode did not return. Apparently a completely different set of code was running. Bizarre! Means everything I ever knew about the database. There must be some odd error in the guts of SQL Server, or weird data cache fetching despite the view change ... No window functions, no transactions ... I am working against a static dataset for data migration, not a live system.

When I UNION defines a view with a TOP 1000 selection from the view, I get an error about the mismatch of column definitions, so obviously something is out of sync.

I recompiled the view and the problem went away.

0


source






More articles:


All Articles