sql-server-2005 - SQL Server 2008 动态跨数据库 View

Question

我们在 SQL Server 2008 R2 SE 中有一个名为 AVL 的数据库。该数据库有很多表，但有一个特别称为 ASSETLOCATION 的表现在有 46 百万行，占数据库总大小的 99.9%。

这个表有2008年至今的信息，实际增长速度是每天12万条左右。现在，我们要解决两种情况:

• 性能开始缓慢下降，一切正常优化，所以没有什么可做的
• 备份时间不断增加并成为一个问题(我们做了 1 个完整的每天备份)。 BAK 文件是 11GB，在 winrar 完成他的事情之后最终大小为 2GB，然后脚本将文件发送到异地。我们有T1 并通过电线拉取 2GB 大约需要 5 个小时。

这一切都是正常的，但我想捕获一个问题:+90% 的 SQL 语句使用仅 3 个月或更短时间的信息，换句话说，2008 年、2009 年和 2010 年的数据不经常被访问。

我正在考虑为每一年创建一个新数据库。让我们说: - AVL2008 数据库，只有 ASSETLOCATION 表会有 2008 年的记录 - AVL2009 数据库，只有 ASSETLOCATION 表会有 2009 年的记录 - AVL2010数据库，只有2010年记录的ASSETLOCATION表

正如您已经猜到的，过去的数据不会发生变化，因此从备份的角度来看，这非常好，因为 AVL 数据库将仅包含当年的记录。这种方法也将对性能有很大帮助。

现在是问题。假设 ASSETLOCATION 表具有以下列: - IDASSETLOCATION(整数，PK 身份) - IDASSET(int，FK 到 ASSET 表) - 何时(日期时间) - LATLONG(varchar(22)，空间信息)

我需要在 AVL 数据库中创建一个名为“vASSETLOCATION”的 View ，这很简单，但我不希望该 View 访问所有数据库并通过 UNION 加入 ASSETLOCATION 表，而不是基于 WHEN 字段的唯一需要的。例如:

select * from vASSETLOCATION where [WHEN] between '2008-01-01' and '2008-01-02'

在这种情况下， View 应该只访问 AVL2008.ASSETLOCATION 表

select * from vASSETLOCATION where [WHEN] between '2008-12-29' and '2009-01-05'

在这种情况下， View 应该访问 AVL2008.ASSETLOCATION 和 AVL2009.ASSETLOCATION

select * from vASSETLOCATION where 
    ([WHEN] between '2008-01-01' and '2008-01-01')
or 
    ([WHEN] = getdate())

在这种情况下， View 应访问 AVL2008.ASSETLOCATION 和 AVL.ASSETLOCATION

我知道用表标量 UDF 代替 View 可以解决问题，但不止只有 4 个字段，而且 [WHEN] 不是我们可能希望包含在 where 部分中的唯一字段。在有人建议之前，表分区功能可能有助于提高性能，但不会解决备份问题。

是否有办法在 View 中执行此操作？谢谢。-

Answer 1

这听起来像是 table partitioning 的经典案例或 distributed partitioned views .

但是，您可以使用一些以不同方式看待问题的智能代码来解决这个问题，而无需为企业版支付高昂的价格(或完成支持这些功能所需的所有准备工作)。您不希望单个 View 访问不同数据库中的所有表，但如果您有多个 View 和一个存储过程来控制它们的访问方式怎么办？

为最常见的访问模式创建 View 。也许您有一个涵盖 2008-2010、2008-2009、2009-2010 等日期范围的 View 。它们可能如下所示:

CREATE VIEW dbo.vAL_2008_2009 
AS
    SELECT * FROM AVL2008.dbo.ASSETLOCATION
    UNION ALL
    SELECT * FROM AVL2009.dbo.ASSETLOCATION;
GO

CREATE VIEW dbo.vAL_2008_2010
AS
    SELECT * FROM AVL2008.dbo.ASSETLOCATION
    UNION ALL
    SELECT * FROM AVL2009.dbo.ASSETLOCATION
    UNION ALL
    SELECT * FROM AVL2010.dbo.ASSETLOCATION;
GO
-- etc. etc.

现在，您处理查询的代码可以采用输入日期参数​​并计算需要查询的 View 。例如:

CREATE PROCEDURE dbo.DetermineViews
    @StartDate     DATETIME,
    @EndDate       DATETIME,
    @optionalToday BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX) = N'';

    SET @sql = @sql + N'SELECT * FROM ' + CASE
      WHEN @StartDate >= '20080101' AND @EndDate < '20090101' THEN 'AVL2008.dbo.ASSETLOCATION'
      WHEN @StartDate >= '20080101' AND @EndDate < '20100101' THEN 'dbo.vAL_2008_2009'
      WHEN @StartDate >= '20080101' AND @EndDate < '20110101' THEN 'dbo.vAL_2008_2010'
      -- etc. etc.

      WHEN YEAR(@StartDate) = YEAR(CURRENT_TIMESTAMP) THEN 'AVL.dbo.ASSETLOCATION'
    ELSE '' END;

    IF @OptionalToday = 1 AND YEAR(@StartDate) <> YEAR(CURRENT_TIMESTAMP)
    BEGIN
        SET @sql = @sql + N'UNION ALL SELECT * FROM AVL.dbo.ASSETLOCATION'
    END

    SET @sql = @sql + ' WHERE [WHEN] BETWEEN ''' 
        + CONVERT(CHAR(8), @StartDate, 112) + ''' AND '''
        + CONVERT(CHAR(8), @EndDate,   112) + '''';

    IF @OptionalToday = 1
    BEGIN
        SET @sql = @sql + ' OR ([WHEN] >= DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
            AND [WHEN] < DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)';
    END

    PRINT @sql;
    -- EXEC sp_executeSQL @sql;
END
GO

我可能遗漏了您的一些业务逻辑，您肯定想在其中添加一些错误处理并测试其中的垃圾，但这是一个相对易于维护的解决方案，只需要更新与创建新数据库同时进行，以存档去年的数据，这听起来好像每年只发生一次。