Solved

Views, How to create Index on View and also do update through view

Posted on 2007-11-26
20
783 Views
Last Modified: 2008-02-01
Hi,
I would like to know how can I create views on index ( clustered or nonclustered?) and how do I perfom update through the views? this views will be use for Linked server query
0
Comment
Question by:motioneye
  • 10
  • 8
  • 2
20 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 100 total points
ID: 20355265
It is in the same way you create for the Tables

create index IndexName on ViewName( columns )


http://www.sqlteam.com/article/indexed-views-in-sql-server-2000
0
 

Author Comment

by:motioneye
ID: 20355727
ok,
I'm using linked server, so far no indexed view been created, if I create indexed view will I gain any performance? especially update,delete and select?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20355856
Yes, it  will perform better
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 400 total points
ID: 20355966
I don't believe you can create indexed views on linked servers. indexed views are a FULL copy of the table data, CLUSTERED a different way... it requires WITH SCHEMABINDING to be specified when creating the view - which would be difficult across servers
0
 

Author Comment

by:motioneye
ID: 20356312
Hi imitchie
Are u sure we can't deploy this especially for linked server?, what u mean it would be difficult across servers? what I believe and thought is when we have indexed views definitely linked server query will perform faster, this due to fact the query will access the actual storage ( indexed views ) instead accessing virtual table ( views ) which will cost more compare to indexed views
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20356469
0
 

Author Comment

by:motioneye
ID: 20356523
You may take a read at this article
http://msdn2.microsoft.com/en-us/library/Aa933126(SQL.80).aspx

it sounds its possible to have indexes on views on Linked server, btw let me know also your understanding on the article
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20356738
I'm sorry but the word "Linked" does not appear in that MSDN article. An Indexed view is a direct copy of data, CLUSTERED a different way to the the original data, for convenience.  It's also called a materialised view. The difference between that and a normal index, is that an Index key contains : indexed columns, and the RID (rowid reference to actual data).

Each entry in Index on (Date, Type) will contain Date,Type,RID

Whereas an Indexed View is a full copy of data. The original table may be Clustered (laid sequentially in SQL-filesystem) on ID, but we create an Indexed view via Customer,Date for convenience since it is a very common to search by Customer&Date. SQL Server copies the entire record on Indexed view creation and from then on will continue to keep it completely synchronized with the underlying base table.  This is why WITH SCHEMABINDING is absolutely required.  No changes may be made to the base table without removing the indexed view first.

Now having said all that, to keep a linked server query up to date, it will need to
be notified of all inserts, updates, (by another server)
be involved in transactions on another server
be part of the transaction log of the linked server
etc

You can see how that just doesn't make sense
0
 

Author Comment

by:motioneye
ID: 20357522
Hi,
I'm trying with linked server and manage to select data, no any error being return even though my views has schemabinding on it, what do u think???



StmtText                                                                                           StmtId      NodeId      Parent      PhysicalOp                     LogicalOp  
-------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------
set showplan_all on                                                                                1           1           0           NULL                           NULL        

SELECT * FROM OPENQUERY(kiptox,'SELECT top 20000 * FROM northwind..Index_views_Custom')           2           2           0           NULL                           NULL        
  |--Remote Scan(SOURCE:(Kiptox), OBJECT:(SELECT top 20000 * FROM northwind..Index_views_Custom))  2           3           2           Remote Scan                    Remote Scan

(3 row(s) affected)

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20360684
How did you create the SCHEMA bound remote view? Are you
a) trying to select from an Indexed view sitting in another database? Or
b) trying to create a schema bound view in this database to a table in another server?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:motioneye
ID: 20362615
Hi,
I'm select from an Indexed view sitting in another database yes this is what I'm doing right now.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 400 total points
ID: 20362642
ok, in that case treat it just like a table.

if a view vTbl is an indexed view for table Tbl, then

update vTbl set f1 = X where Y = Z
is the same as
update Tbl set f1 = X where Y = Z

treat it like a normal table and you will be fine
0
 

Author Comment

by:motioneye
ID: 20363222
so any advatanges of doing this way?? query a views which sitting in difference db? or creating a views in the db where should I call for query?? which one is better?
0
 

Author Comment

by:motioneye
ID: 20363633
Hi,
I found that what u mean previously, when I trying to create a views it return me an errro below

Msg 2014, Level 16, State 1, Procedure ind_viewCust2, Line 4
Remote access is not allowed from within a schema-bound object.


so in ur opinion, which one will perform better?

Create an indexed views in remote database and create the Linked server and let the query (openquery) selecting the indexed view it self?

Or create a normal views in local database with providing creating a proper index on remote table?
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 400 total points
ID: 20363726
what is the purpose of creating the indexed view?
if you want to update a remote table, do it directly via

update remoteserver.db.dbo.table set ....

there is no performance gain in creating local views except maybe as a shorthand, so that you can use
select .. from r_d_d_t  (assuming you created the view r_d_d_t to remoteserver.db.dbo.table)

i'm not sure if you can update from a local view to remote table, but you can certainly give it a try. even if you could, there is no performance gain.  an indexed view is just another index to the data, except this one carries data within the index itself.  openquery will get optimized and will use available table indexes (including indexed view if any) just like any normal query
0
 

Author Comment

by:motioneye
ID: 20363769
<what is the purpose of creating the indexed view?> well as u know this will provide me an actual storage as compare to virtual table ( view) and technically,  indexed view ( clustered indexes ) will perform better since I can directly accessing the storage rather than rely on normal views which again need to access the table and pulling the data, with using indexed on view it will give me performance gain since the data are ready as storage so query will perform much faster
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 400 total points
ID: 20363902
indexed view-> query read, yes faster, when using EXACTLY the index in the view
indexed view-> slower write, 2x data size, larger backups etc

whether or not you created indexed views on a table is after measuring the good vs bad above. this is within a single database, based on data access patterns. now, you are asking about remote queries, which does not allow indexed views, period.
0
 

Author Comment

by:motioneye
ID: 20364136
<based on data access patterns. now, you are asking about remote queries, which does not allow indexed views, period.>  are u sure?? any article on this?? or  I extremely don't realize a  remote queries won't allow any indexed view to be involve, so means that here I have to consider clustered indexes on the base table it self..
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 400 total points
ID: 20364716
you can remote query from server A to a indexed view on server B.
you can remote query from server A to table on server B.
you can remote query from server A to some data on table on server B that will involve an index.
you cannot create an indexed view on server A referencing a table on server B
you can create a view on server A referencing a table/indexed view on server B
you CANNOT update an indexed view/table on server B via a view on server A
you can use remote query to update a table/indexed view on table B
0
 

Author Comment

by:motioneye
ID: 20365406
hmm just wonder how u so so on this :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now