Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how can esure data hold or persist in a database view till i havent refresh view


how can esure data hold or persist in a database view till i havent refresh view

Author
Message
vineet_dubey1975
vineet_dubey1975
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 650
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
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2220 Visits: 6076
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/
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17525 Visits: 32252
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
vineet_dubey1975
vineet_dubey1975
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 650
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.
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 3432
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
Attachments
IndexUpdate.png (59 views, 9.00 KB)
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 3432
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17525 Visits: 32252
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3140 Visits: 3815
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search