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

我们在 SQL Server 2008 R2 SE 中有一个名为 AVL 的数据库。这个数据库有很多表，但有一个叫做 ASSETLOCATION 的表，现在有 4600 万行，占数据库总大小的 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 数据库，只有 ASSETLOCATION 表才会有 2010 年的记录

因为您已经猜到过去的数据不会被更改，所以从备份的角度来看这将是很好的，因为 AVL 数据库将只有当年的记录。这种方法也对性能有很大帮助。

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

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

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 中做到这一点？
谢谢。-

最佳答案

这听起来像是 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

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

关于sql-server-2005 - SQL Server 2008 动态跨数据库查看，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7184178/