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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 indexed views

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 Kizer
aka tduggan
Go to Top of Page

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
Go to Top of Page

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"]
Go to Top of Page

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
Go to Top of Page

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"]
Go to Top of Page

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 INDEX
Run report
Fix data
Re-run report
DROP INDEX
<vbg>

Kristen
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -