Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how can esure data hold or persist in a database view till i havent refresh view Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 3:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 494, Visits: 453
I have a table Employee (id,name,address)
having following row's
1,'AAAAAA','AAAAAAAAAA'
2,'BBBBBBBBBBBBB','BBBBBBBBBB'
3,'CCCCCC','CCCCCC''
4,'DDDDDDDDD','DDDDDDDDD'

i have created a view on top of this ..
Create view [dbo].[vEmployee] (select iD,name,address from employee) with schemabinding and clustered Index on it ;


select id,name,address from [dbo].[vEmployee] =

Output : 4 rows

if i perform delete on Employee where id=4

select id,name,address from [dbo].[vEmployee] =

Output : 3 rows


if i perform Insert on Employee where id=5

select id,name,address from [dbo].[vEmployee] =

Output : 5 rows (includes the row i have inserted.

can i have a mechanism to hold the original data to the view..

IF i refresh view then only data should refresh


Can i define any type or any other way i can achieve this.


??????????????????? please help







Post #1507024
Posted Tuesday, October 22, 2013 3:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
The short answer is – no you can't. A view is a SQL statement that selects records from another. If the record exists, it will show it. If it doesn't, it won't show it. If you'll specify more about what you need to do, some of us might come up with a way of doing it.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1507037
Posted Tuesday, October 22, 2013 4:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
You could look at materialized or indexed views. These are actually a separate data store that you have to update to see data changes. But, they don't work well with data that changes a lot. You can read about them here.

Other than that, the previous post is correct, a view is nothing but a query. It doesn't store data for you.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1507048
Posted Tuesday, October 22, 2013 7:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 494, Visits: 453
Thnx !

Some people argued this feature supported in Oracle ..SQL Server also have that.

I have gone through the link above for to create the indexed views and materialized index to it.
also appended query hint with (NO EXPAND) but no luck.




Post #1507110
Posted Tuesday, October 22, 2013 7:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 912, Visits: 2,875
Grant Fritchey (10/22/2013)
You could look at materialized or indexed views. These are actually a separate data store that you have to update to see data changes.
Indexed views do not need to be updated to see data changes. Data changes are automatically applied to the index as if the index was part of the base table.





The SQL Guy @ blogspot

@SeanPearceSQL

About Me


  Post Attachments 
IndexUpdate.png (57 views, 9.03 KB)
Post #1507148
Posted Tuesday, October 22, 2013 7:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 912, Visits: 2,875
vineet_dubey1975 (10/22/2013)

can i have a mechanism to hold the original data to the view..
IF i refresh view then only data should refresh
Can i define any type or any other way i can achieve this.

To do this you would need another user table instead of a view. You can then refresh this table when you need to.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1507150
Posted Tuesday, October 22, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
Sean Pearce (10/22/2013)
Grant Fritchey (10/22/2013)
You could look at materialized or indexed views. These are actually a separate data store that you have to update to see data changes.
Indexed views do not need to be updated to see data changes. Data changes are automatically applied to the index as if the index was part of the base table.


I need a new brain. Of course you're right. Sorry about that.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1507168
Posted Wednesday, October 23, 2013 3:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 2,880, Visits: 3,232
You can get something close to what you describe with Snapshot Isolation, but ultimately what you are asking for is not what a database is designed to do.

If you enable Snapshot Isolation, then once you have initiated your transaction, any changes to data that occurr to the objects you reference are not seen by your current SQL session. You can repeat your SELECT ... FROM View or SELECT ... FROM Table and still get the same results, regardless of if the underlying data has changed. Both SQL Server and Oracle implement a form of snapshot isolation, but the way it is implemented within the DBMS is different.

This is a very simplistic introduction to snapshot isolation. If you want to know more then a search for 'sql server snapshot isolation' gives a lot of advice.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1507523
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse