我可以从另一个数据库服务器在我的数据库服务器中创建视图吗

Is it possible to create view in my database server of another servers database table?

是否可以在我的数据库服务器中创建另一个服务器数据库表的视图?

Let's say you have a database called Testing on server1 and you have another database Testing2 on server2. Is it possible to create view of Testing2's table Table2 in server1's database Testing?

假设在server1上有一个名为Testing的数据库，在server2上有另一个数据库Testing2。是否可以在server1的数据库测试中创建Testing2的表表2的视图?

Also, I am using SQL Server 2008.

此外，我正在使用SQL Server 2008。

Please let me know if you have any questions.

如果你有任何问题，请告诉我。

Thanks,

谢谢,

2 个解决方案

#1

Yes, you can. First, you need to link to the other server, using something like sp_addlinkedserver.

是的,你可以。首先，需要使用sp_addlinkedserver之类的链接到另一个服务器。

Then you can access the data using 4-part naming. Here is an example:

然后您可以使用四部分命名来访问数据。这是一个例子:

create view v_server1_master_tables as
    select *
    from server1.master.information_schema.tables;

#2

It is possible through linked servers. However, I wouldn't encourage you to create views based on tables from another server, as it's likely that entire table will be selected from linked server every time you use this view - optimizer may not know about this table structure to issue any filters. I've seen it at work, where nobody knew where select * from queries on large table come from that were slowing down the database, and it appeared that it was being used somwhere in another server, in a simple query. At least you should check if your solution won't cause the above problem. Maybe someone else could elaborate on how optimizer behave when dealing with linked servers?

这可以通过链接服务器实现。但是，我不鼓励您基于来自另一个服务器的表创建视图，因为每次使用这个视图时，很可能会从链接服务器中选择整个表——优化器可能不知道这个表结构来发出任何过滤器。我在工作中见过它，没有人知道从大型表的查询中选择*来自哪里，这会减慢数据库的运行速度，而且它似乎正在另一个服务器的somwhere中使用，在一个简单的查询中。至少您应该检查您的解决方案是否不会导致上述问题。也许其他人可以详细说明在处理链接服务器时优化器的行为方式?

#1