使用 OpenQuery 在视图上创建索引
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以做的最好的事情就是安排定期将您感兴趣的 AD 数据导出到表中。
该表当然可以包含您喜欢的所有索引。如果您每 10 分钟运行一次导出，并且获取 9 分 59 秒过期数据的可能性不是问题，那么您的查询将会快如闪电。
唯一需要关注的部分是导出期间的锁定和并发管理。一种策略可能是将数据导出到新表中，然后通过重命名将其交换到位。另一种可能是使用 SYNONYM（SQL 2005 及更高版本）执行类似的操作，只需将 SYNONYM 指向两个交替表即可。
提供您正在执行的查询的数据来自 SQL Server 之外的完全不同的系统。 SQL Server 无法为不属于它的数据创建索引视图。对于初学者来说，当某些内容发生更改时，如何通知它以便更新其索引？必须有某种通知和更新机制，这是难以置信的，因为 SQL Server 无法合理地为外部系统的这种分布式、缓慢的非 SQL Server 事务维护 ACID。
因此，我建议通过您自己的计划作业来模仿这样的事情，每 X 分钟刷新一次数据。
--回复您的评论--
如果不查询，您无法判断是否添加了新用户。如果 Active Directory 支持某些生成事件的 API，我从未听说过。
但是，每次查询时，您可以将所有用户的最大创建时间存储在一个表中，然后通过动态 SQL，仅查询创建日期在此之后的新用户。理论上，这个查询应该非常快，因为它只会通过网络提取很少的数据。您只需查看用户创建日期的确切 AD 字段以及该字段条件的语法即可。
如果管理动态 SQL 太困难，一个非常简单的 vbscript、VB 或 .Net 应用程序也可以按计划查询活动目录并更新数据库。
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.
以下是索引视图的基础知识及其要求。请注意，您尝试执行的操作可能属于派生表的类别，因此无法使用“OpenQuery”创建索引视图
此列表来自 http://www.sqlteam.com/article/indexed-views-in-sql-server-2000
1 .视图定义必须始终从相同的基础数据返回相同的结果。
2.视图不能使用非确定性函数。
3.视图上的第一个索引必须是聚集的唯一索引。
4.如果使用 Group By，则必须在选择列表中包含新的 COUNT_BIG(*)。
5.视图定义不能包含以下内容
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
在这种情况下，SQL Server 无法知道远程数据源中的任何更改（数据、架构等）。对于本地表，它可以使用 SCHEMABINDING 等来确保基础表保持不变并且可以跟踪数据更改。
如果您需要经常查询视图，那么我会使用定期刷新的本地表。事实上，无论如何我都会使用一张桌子。即使在最好的情况下，AD 查询也不是最快的......
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...