Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2006-03-31 : 15:18:56
|
jemma writes "Hi All,Does records appended on to the basetable reflect on the View Index? Do we have to re-build the index?" |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-31 : 16:07:42
|
No you don't need to rebuild it. Are you not seeing new data?Tara Kizeraka tduggan |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2006-04-04 : 10:24:13
|
quote: Do we have to re-build the index?
Oracle has this feature..------------------------I think, therefore I am - Rene Descartes |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-04 : 10:31:27
|
so does sql server... point is you don't have to rebuild it.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2006-04-04 : 10:40:26
|
What I mean to say is Oracle has both type of materialsed views (indexed views)1. Views which needs to be refreshed on a periodic basis.2. views which don't need to be refreshed periodically.Where as in SQL2K only option 2 is available. Hope I am clear this time..------------------------I think, therefore I am - Rene Descartes |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-04 : 10:43:05
|
cool.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-04 : 11:07:19
|
"cool"You sure? So you do REFRESH (lets assume it takes a while, you've got lots of data) and then you run your (say, monthly) report. But it misses records added in the interim (I suppose you could use a TRANSACTION block to prevent that). Then you spot an error in the report, so you "fix" the data ... and then REFRESH the index .... painful!Then there's the SQL Server way:CREATE INDEXRun reportFix dataRe-run reportDROP INDEX<vbg>Kristen |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2006-04-04 : 11:41:39
|
quote: (lets assume it takes a while, you've got lots of data)
Oracle has both DELTA refresh and FULL refresh. Don't forget that this is just an additional feature. It also provides the DEFAULT option which SQL2K has (automatic REFRESH).As far as I know the Indexed views in SQL2K are Read only. But ora materialsed views can be updated !!------------------------I think, therefore I am - Rene Descartes |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-04 : 11:48:28
|
"DELTA refresh"But the refresh is only as good as the time at which you asked for it, so can't the "current data" be inconsistent with the index? (i.e. you select a record by the index which has been changed such that it is no longer valid for your query - plus the "just-been-added" scenario too)Kristen |
|
|
|
|
|