CODEWITHSUNDEEP
sql-serverazure-sql-database
Erechtheus
Erechtheus

Reputation: 765

SQL Server views are empty even though data exists in underlying tables

I'm running Azure SQL Server and there's a problem I have that relates to views. I have a view (which I will refer to as viewC) that is built on a UNION ALL of two other views, i.e.

SELECT ColA
      ,ColB
FROM dbo.viewA

UNION ALL

SELECT ColA
      ,ColB
FROM dbo.viewB

If I truncate all the tables in the underlying viewA and viewB views, and re-import the data in these truncated tables, sometimes viewC is still empty if SELECT from it. This behavior is very odd and I was wondering if it could be related to caching or something like this.

I first noticed this might be related to the UNION ALL operation somehow. I remember having issues when I used * instead of specifying the individual columns, even though viewA and viewB had the exact same schema. I.e. viewC was empty when I ran this:

SELECT *
FROM dbo.viewA

UNION ALL

SELECT *
FROM dbo.viewB

The view seems to work again if I manually SELECT individual tables, then viewA and viewB and finally ViewC respectively. Very odd!

Are there any table hints or view hints I can try? Does anyone know what this 'bug' might be related to?

Upvotes: 0

Views: 847

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6063

This issues may be due to a change in the table structure.

You can use sp_refreshview system stored procedure to refresh the view.

exec sp_refreshview 'schema.view_name'

Upvotes: 2

Related Questions