Creating an index on a view with OpenQuery

Question

SQL Server doesn't allow creating an view with schema binding where the view query uses OpenQuery as shown below.

Is there a way or a work-around to create an index on such a view?

Answer 1

Here are the basics for Indexed views and thier requirements. Note what you are trying to do would probably fall in the category of a Derived Table, therefore it is not possible to create an indexed view using "OpenQuery"

This list is from http://www.sqlteam.com/article/indexed-views-in-sql-server-2000

1.View definition must always return the same results from the same underlying data.

2.Views cannot use non-deterministic functions.

3.The first index on a View must be a clustered, UNIQUE index.

4.If you use Group By, you must include the new COUNT_BIG(*) in the select list.

5.View definition cannot contain the following

a.TOP

b.Text, ntext or image columns

c.DISTINCT

d.MIN, MAX, COUNT, STDEV, VARIANCE, AVG

e.SUM on a nullable expression

f.A derived table

g.Rowset function

h.Another view

i.UNION

j.Subqueries, outer joins, self joins

k.Full-text predicates like CONTAIN or FREETEXT

l.COMPUTE or COMPUTE BY

m.Cannot include order by in view definition

Answer 2

The best you could do would be to schedule a periodic export of the AD data you are interested in to a table.

The table could of course then have all the indexes you like. If you ran the export every 10 minutes and the possibility of getting data that is 9 minutes and 59 seconds out of date is not a problem, then your queries will be lightning fast.

The only part of concern would be managing locking and concurrency during the export time. One strategy might be to export the data into a new table and then through renames swap it into place. Another might be to use SYNONYMs (SQL 2005 and up) to do something similar where you just point the SYNONYM to two alternating tables.

The data that supplies the query you're performing comes from a completely different system outside of SQL Server. There's no way that SQL Server can create an indexed view on data it does not own. For starters, how would it be notified when something had been changed so it could update its indexes? There would have to be some notification and update mechanism, which is implausible because SQL Server could not reasonably maintain ACID for such a distributed, slow, non-SQL server transaction to an outside system.

Thus my suggestion for mimicking such a thing through your own scheduled jobs that refresh the data every X minutes.

--Responding to your comment--

You can't tell whether a new user has been added without querying. If Active Directory supports some API that generates events, I've never heard of it.

But, each time you query, you could store the greatest creation time of all the users in a table, then through dynamic SQL, query only for new users with a creation date after that. This query should theoretically be very fast as it would pull very little data across the wire. You would just have to look into what the exact AD field would be for the creation date of the user and the syntax for conditions on that field.

If managing the dynamic SQL was too tough, a very simple vbscript, VB, or .Net application could also query active directory for you on a schedule and update the database.

Answer 3

In this case, there is no way for SQL Server to know of any changes (data, schema, whatever) in the remote data source. For a local table, it can use SCHEMABINDING etc to ensure the underlying tables(s) stay the same and it can track datachanges.

If you need to query the view often, then I'd use a local table that is refreshed periodically. In fact, I'd use a table anyway. AD queries are't the quickest at the best of times...