how can esure data hold or persist in a database view till i havent refresh view
-
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
-
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/ -
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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.
-
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.
-
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.
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply