SQL Server代理表——解锁数据的钥匙

什么是SQL Server代理表

SQL Server代理表，顾名思义即为代理另一张表，使其在不同的数据库上看起来彼此相同，实际上它们是完全不同的表。SQL Server代理表可以作为解决数据分散、合并、跨数据库使用的利器。如果您想让一个表在不同的实例、或不同的数据库中出现，并且让变化反映到这些数据库之一，那么SQL Server代理表将会很有用。

如何创建代理表

步骤1：创建本地表和远程表

在创建代理表之前，必须先创建本地表和远程表。下面是一个示例：

-- 创建本地表
CREATE TABLE [dbo].[employees_local](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [position] [varchar](50) NOT NULL,
    [department] [varchar](50) NOT NULL,
    CONSTRAINT [PK_employees_local] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
-- 创建远程表
CREATE TABLE [dbo].[employees_remote](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [position] [varchar](50) NOT NULL,
    [department] [varchar](50) NOT NULL,
    CONSTRAINT [PK_employees_remote] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

步骤2：创建代理表

在创建代理表之前，需要启用SQL Server数据访问（ADO），并且必须在本地服务器和远程服务器之间建立数据连接。下面是一个示例：

-- 启用SQL Server数据访问
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
-- 建立数据连接
EXEC sp_addlinkedserver 'SERVERNAME\INSTANCE', 'false', 'SQLOLEDB', 'SERVERNAME\INSTANCE'
-- 创建代理表
EXEC sp_addlinkedserver 'employees_proxy',
  @srvproduct = ' ', 
  @provider = 'SQLNCLI', 
  @datasrc = 'SERVERNAME\INSTANCE',
  @provstr = 'Integrated Security=SSPI',
  @catalog = 'DATABASENAME';
EXEC sp_addlinkedsrvlogin 'employees_proxy', 'false', NULL, 'sa', 'PASSWORD';
GO
CREATE VIEW employees_proxy_view AS
SELECT * FROM OPENQUERY(employees_proxy, 'SELECT * FROM dbname.dbo.employees_remote')
GO
EXEC sp_addalias 'employees_local', 'employees_proxy_view', 'DATABASENAME'

如何使用代理表

现在，代理表已经创建完毕。在使用代理表时需要指定其全名（例如：[DATABASENAME].[dbo].[employees_local]）。

查询代理表

查询代理表是与查询本地表或远程表类似的。下面是一个示例：

SELECT * FROM [DATABASENAME].[dbo].[employees_local]

插入数据到代理表

在插入数据到代理表时需要指定其全名。下面是一个示例：

INSERT INTO [DATABASENAME].[dbo].[employees_local] ([name],[position],[department])
VALUES ('张三', '经理', '销售部')

更新代理表

更新代理表时需要指定其全名。下面是一个示例：

UPDATE [DATABASENAME].[dbo].[employees_local]
SET position = '总经理'
WHERE id = 1

删除代理表

删除代理表时，必须要先删除代理表的视图。下面是一个示例：

DROP VIEW [DATABASENAME].[dbo].[employees_proxy_view]
GO
EXEC sp_dropserver 'employees_proxy', 'droplogins'

总结

SQL Server代理表可以作为解决数据分散、合并、跨数据库使用的利器。在使用代理表时需要指定其全名，并且使用其他表的时候，需要将它们作为跨数据库查询来进行访问。代理表具有在不同的实例、或不同的数据库中出现，并且让变化反映到这些数据库之一的能力，是解锁数据的钥匙。