February 27, 2012 at 7:09 am
Hi I have a table dbo.RATING in database A and a view stage.RATING in database B that is defined as
SELECT * from A.dbo.RATING
The view was created one week ago and a couple of rows have been inserted into the table since then. Unfortunately when querying the view I get the content from one week ago i. e. different resultsets from
SELECT * from A.dbo.RATING
compared to
SELECT * from B.stage.RATING
I know that changing the source table definition is automatically implemented in the view. But change of data should :crazy:
Anyone knowing this issue?
February 27, 2012 at 7:19 am
Have you tried running :
Execute sp_refreshview 'Your View Name Here'
Check if doing the above operation makes any difference.
February 27, 2012 at 7:32 am
That makes the results identical (as obviously dropping and re-creating the view does).
But I'd be happy if the view was correct without explicitly forcing it to be...
February 27, 2012 at 2:49 pm
I haven't experienced this issue, but have you considered creating the view with the SCHEMABINDING option? Here's the notes from BOL (http://msdn.microsoft.com/en-us/library/ms187821(v=sql.90).aspx)
If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
I'd like to know some examples of what Microsoft considers 'unexpected results'
February 27, 2012 at 2:58 pm
Brian Kukowski (2/27/2012)
I'd like to know some examples of what Microsoft considers 'unexpected results'
the unexpected results is just what Alexander is experiencing; but this is technically a known behavior.
his expectation is changes in the underlying table DDL would be propagated into the view; add a column to teh table, and he expected it in the view.
the reason it's not is the view is the * is converted to the actual column names at the moment of view creation, so SELECT * becomes SELECT col1, Col2 etc.
Brian had a great suggestion with the schema binding...that will force you to make sure the views are up to date.
you could also break the view, if for example you dropped a column that the view used....you wouldn't know about an issue until the view gets queries alter.
Lowell
February 28, 2012 at 12:39 am
Sorry, I had a typo in my first posting: I am aware that schema changes of the underlying table are not being propagated to the view. What drives me crazy is that obviously I cannot be sure that all data changes of the table are visibile in the view.
What can only be considered an error.
February 28, 2012 at 3:14 am
If view underlying table(s) DDL is not changed, data should not be missing in the view.
Can you please post your view DDL.
February 28, 2012 at 3:23 am
Simple as can be:
USE [ZDA_005000_AU_TEMP]
GO
/****** Object: View [stage].[RATING] Script Date: 02/28/2012 11:22:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [stage].[RATING] AS SELECT * FROM [ZDA_005000_AU_STAGE].[dbo].[RATING]
GO
February 28, 2012 at 4:11 am
Looks like nothing special.
I cannot believe that data is missing from the view in your case.
It could be:
a.) you are looking into different environments
b.) you are applying filters
c.) insert did happen within not commited transaction, so you can see records in the connection session where you did insert but not in the session which you use to get data from view.
February 28, 2012 at 7:32 am
Thank you for your ideas. But none of your problems is the case:
As I have describe I query the table and the view not only in one connection but in one batch and the queries used are as simple as "SELECT *" or "SELECT COUNT(*)" without any where clauses.
It seems to be a caching issue. You can probably imagine, that it is easy to deal with a problem once you can delimit it. But to loose faith in the basic concepts of a view is much worse...:sick:
February 28, 2012 at 8:58 am
Do you see the difference in returned resultset in SQLServer MS or in a client/server application?
Your application may cach the query results, SQLServer doesn't do it. It can cach the execution plan but not the results.
Can you recreate a problem?
Are you telling us that you insert records into table using SQLServer MS and without any transactions, then, from another query window you perform "select" from the view you gave us and cannot see these records, but after refreshing the view you can?
If you are right, no one on this planet would use SQLServer...
February 29, 2012 at 1:40 am
The behavior described by OP is strange. Either He or We are missing something here.
February 29, 2012 at 2:18 am
Eugene Elutin (2/28/2012)
Do you see the difference in returned resultset in SQLServer MS or in a client/server application?
I query both table and view from SSMS.
Your application may cach the query results, SQLServer doesn't do it. It can cach the execution plan but not the results.
Can you recreate a problem?
No, not yet. Since doing the sp_refreshview the view is up to date and showing the new rows that are inserted daily.
But I have 3 databases left where I haven't executed the sp and they are still outdated.
Are you telling us that you insert records into table using SQLServer MS and without any transactions, then, from another query window you perform "select" from the view you gave us and cannot see these records, but after refreshing the view you can?
If you are right, no one on this planet would use SQLServer...
The insert happens by daily execution of a sql script (plain delete, insert and update statements, no transactions) starting sqlcmd from an agent job.
I execute the batch
select COUNT(*) from ZDA_001302_PU_TEMP.stage.RATING
select COUNT(*) from ZDA_001302_PU_STAGE.dbo.RATING
from one SSMS window. Each day the second value (the table) increases while the first one (select * view) stays the same.
You can be sure that I could hardly believe my eyes...
February 29, 2012 at 2:50 am
Alexander G. (2/29/2012)
...The insert happens by daily execution of a sql script (plain delete, insert and update statements, no transactions) starting sqlcmd from an agent job.
...
Does your job have a step to recreate a table or make any DDL change?
sp_refreshview does not refresh data! It does only update the metadata for the specified non-schema-bound view.
Can you confirm that after executing sp_refreshview you can insert a new row into your table and that row will not be visible in a view until you execute sp_refreshview again?
February 29, 2012 at 6:03 am
Eugene Elutin (2/29/2012)
Does your job have a step to recreate a table or make any DDL change?
The job does not execute any ddls at all.
Can you confirm that after executing sp_refreshview you can insert a new row into your table and that row will not be visible in a view until you execute sp_refreshview again?
From the first execution of sp_refreshview the view seems to behave correctly. So you do not need to execute it again, all inserts are transparent. Of course this solves my problem for the moment but it does not remove my doubts...
Some more diagnosis:
correct values.
It seems to be some user/connection specific caching issue...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply