我在SQL Server中创建视图时遇到一些问题. [英] i have some problem when i am creating a view in SQL Server..

问题描述

我在SQL Server 2005中创建视图时遇到一些问题.
问题是我有两个数据库.在一个数据库中，两个表和另一个花药在表上.这两个数据库都在服务器中，并且我正在本地服务器中创建视图..

i have some problem when i am creating a view in SQL Server 2005.
problem is that i have Two Database. in one database two table and anther have on table. both database is in server and i am creating view in my local server..

is it possible to create view on local server database.?

推荐答案

是的，您可以创建一个查看另一个数据库中数据的视图.例如

您有database1和database2. database1包含table1，database2包含table2

通过使用3部分命名语法来限定您的数据库对象，我们可以在database1中创建一个如下所示的视图

这里的语法是[database].[owner].[object]

Yes, you can create a view that looks at data in another database. e.g.

You have database1 and database2. database1 contains table1 and database2 contains tables2

By using 3 part naming syntax to qualify your database objects, we can create a view in database1 that looks like the following

The syntax here is [database].[owner].[object]

SELECT 
    Field1, Field2, Field3
FROM
    database1.dbo.table1 T1
INNER JOIN
    database2.dbo.table2 T2
WHERE
    T1.SomeField = T2.SomeField

使用此视图的任何人都可能遇到安全问题.他们将需要同时拥有数据库和对象的权限.

编辑:对不起，您在我写我的文章时更新了答案！

您可以使用 4部分命名查询 [

You might have security issues with anyone consuming this view. They would need permissions to both the databases and objects.

EDIT: sorry, you updated you answer while I wrote mine!

You can use a 4 part naming query[^] for this.

SELECT 
    Field1, Field2, Field3
FROM
    localhost.database1.dbo.table1 T1
INNER JOIN
    REMOTESERVER.database2.dbo.table2 T2
WHERE
    T1.SomeField = T2.SomeField

但是，您肯定会遇到与此相关的安全问题，甚至可能会遇到一些性能问题

也许使用 OPENQUERY [

However, you''ll definitely run into security issues with this and more than likely some performance issues

Maybe investigate using OPENQUERY[^] to grab the data you need from your REMOTESERVER and perform aggregation locally.

Totally depends on how much data you''re working with

这篇关于我在SQL Server中创建视图时遇到一些问题.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！