Monday, December 5, 2011

Concept of Open Query & Covering Index & Index View

Open Query

Open Query is mechanism in Sql Server. Using the Open Query you can write any sql query on the Link Server Database object.

Link Server : It is a technique by which you can add any database server in your Sql Sever instance.

Suppose you are working on a Sql Server(S1) which is in IST and you have a Sql Server(S2) which is in PST. And you need to write a Sql query in S1 server based on S2 server date, then how it can be possible?

It is possible by Link Server & Open Query.

First you need to add S2 server as a link server(LS_S2) in S1 and now you can get the date of S2 server as follow.

select S2Date from OpenQuery('LS_S2','select getdate() as S2Date')

Covering Index

Covering Index is not seperate kind of Index but it is a technique that is used to improve the performance. And you can say it is a non-clustered index which covers all the field given in your Select statement.

The use of Covering Index is best (and only should be used) in that case when you have a simple query and you need to run this query again & again because of Covering Index store the data at both level : heap & index page so Index Covering give the result fast.

Indexed View

If your view has the clustered index that means your view is a Indexed view.

Index view is a performance optomization tips.

Suppose you are not satisfied with your query response then you can create a view for that query and also you can create the index on that view as like regular table.
Indexed views consume disk space and involve some maintenance overhead (every time underlying tables change, the indexed view also has to change), but they usually provide a good boost in performance.

No comments:

Popular Posts