Creating an index on a view using OpenQuery
The best thing you could do is schedule periodic exports of the AD data that interests you in the table.
The table can of course have all the indexes you like. If you run the export every 10 minutes, and the ability to fetch data, which is 9 minutes and 59 seconds, is not a problem, then your queries will be lightning fast.
The only issue is blocking and concurrency management during export times. One strategy might be to export the data to a new table and then rename it by swap. Another might be using SYNONYM (SQL 2005 and up) to do something like this, where you just point SYNONYM on two table variables.
The data providing the query being executed comes from a completely different system outside of SQL Server. There is no way for SQL Server to create an indexed view of data that it doesn't have. For starters, how would it be notified when something was changed so that it could update its indices? There must be some kind of notification and update mechanism, which is implausible because SQL Server cannot reasonably support ACID for such a distributed, slow, non-SQL server transaction to an external system.
So my suggestion is to emulate things like this through your own scheduling jobs that update the data every X minutes.
- response to your comment -
You cannot determine if a new user was added without prompting. If Active Directory supports some API that generates events, I've never heard of it.
But each time you ask for a query, you can store the longest creation time of all users in a table and then query only new users with a creation date after that through dynamic SQL. This query should theoretically be very fast as it will output very little data over the wire. You just need to see what the exact AD field for the user creation date and the syntax for the conditions in that field will be.
If dynamic SQL management was too tight, a very simple vbscript, VB or .Net application could also query the active directory for you on a schedule and update the database.
a source to share
Here are the basics for indexed views and their requirements. Please note that what you are trying to do will probably fall into the category of a derived table so 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 should always return the same results from the same underlying data.
2.Views cannot use non-deterministic functions.
3. The first index in the view must be a clustered UNIQUE index.
4.If you are using Group By, you must include the new COUNT_BIG (*) list 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
a source to share
In this case, SQL Server is unaware of any changes (data, schema, etc.) to the remote data source. For a local table, it can use SCHEMABINDING etc. to ensure that the underlying tables remain unchanged and can keep track of the data.
If you need to query the view frequently, I would use a local table that is updated periodically. Anyway, I would use a table. AD queries are the fastest at the best of times ...
a source to share