Materialized view in MS SQL 2000?

Question

Materialized view in MS SQL 2000?

zdenek douda

SSC-Addicted

Points: 419
More actions
September 23, 2004 at 2:55 am

#60524

Hi,
exists in MS SQL 2000 something like materialized view in Oracle. View saves physicaly data and automaticaly is updated if source table is changed.

Thanks zdenek

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

MS SQL does not store the data physically for views currently. HOWEVER, ANY TIME the data source is updated the view is as well.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

zdenek douda SSC-Addicted Points: 419 More actions · Answer 2

My problem:

I have very big table and I need to store (physically) from table only small sample (select from one table) of date. Originaly I would to create view but this is slowly therefore I want to store date physically. It is possible resolve this with trigger. Or do you have any idea?

thanks

noeld SSC Guru Points: 96590 More actions · Answer 3

SQL SERVER DO HAVE Something like the materialized view in Oracle they are call INDEXED VIEWS instead. There are a lot of restrictions to make them work but for many many cases you can use them. Please refer to the topic on BOL.

And, Yes you can use triggers to accomplish what you want but if you are able to implement that with the indexed views, that's you best bet

HTH

* Noel

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 4

believe you need Enterprise edition to use indexed views btw...

msdn version might allow it as well.

noeld SSC Guru Points: 96590 More actions · Answer 5

"...believe you need Enterprise edition to use indexed views.. "

That is not correct either you can use standard edition too just remember to use the (NOEXPAND) hint on the query that uses the view

HTH

* Noel

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 6

wierd ... from BOL:

Note You can create indexed views only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

not used them though and have come across dodgy documentation b4

zdenek douda SSC-Addicted Points: 419 More actions · Answer 7

I try create indexed view but it is not possible on linked server table. I must use with schemabinding, this is problem/error. Can I create indexed view on linked server tables? Is any possibilities?

PRO is linked server (oracle), SAPR3 is owner

CREATE VIEW BKPF2

WITH SCHEMABINDING

AS

SELECT BELNR, GJAHR, BLART, USNAM, TCODE, CPUDT, PPNAM

FROM PRO..SAPR3.BKPF

result>

Server: Msg 4512, Level 16, State 3, Procedure BKPF2, Line 4

Cannot schema bind view 'BKPF2' because name 'PRO..SAPR3.BKPF'

is invalid for schema binding. Names must be in two-part format and

an object cannot reference itself.

--CREATE UNIQUE CLUSTERED INDEX bkpf_belnr ON BKPF (BELNR)

thanks zd

vhanda SSCrazy Points: 2240 More actions · Answer 8

You CAN create indexed views on Standard Edition, we have it going at a client site.

Zdenek, you cannot use a four part name in an indexed view, so "FROM PRO..SAPR3.BKPF" is invalid.

This also implies that you may not be able to do this for a linked server. Check SQL Books On Line for restrictions on creating & using indexed views. You may want to consider replication instead?

Vik

zdenek douda SSC-Addicted Points: 419 More actions · Answer 9

Hi,

I dont understand you, I can create view, but when I use 'with schemabinding' then problem occurs. I read conditions for creating indexed view and this schemabinding is obligatory. I thing I cannot create indexed view with using linked server tables.

zd

vhanda SSCrazy Points: 2240 More actions · Answer 10

WITH SCHEMABINDING attempts to create a view as an indexed view.

From Books On Line (BOL):

>>>>

Tables and user-defined functions must be referenced by two-part names. One-part, three-part, and four-part names are not allowed.

>>>>

This is on a page you can view if you go to the BOL index and type "creating indexed views". A four part name for a database object is comprised of [server].[database].[owner].[object]

So, according to the rule above, you can't use four part names in an indexed view, which then means you can't reference a linked server in an indexed view.

If you need access to the data on the linked server, you'll have to bring it over to your local server and database somehow, and retrieve it there.