SQL Server - 使用聚集索引时如何存储数据页

Question

Lamak

Asked: 2018-07-05 08:38:21 +0800 CST2018-07-05 08:38:21 +0800 CST 2018-07-05 08:38:21 +0800 CST

在整个数据库中更改 GETDATE() 的使用

772

我需要将本地 SQL Server 2017 数据库迁移到 Azure SQL 数据库，我面临着一些挑战，因为有很多限制要通过。

特别是，由于 Azure SQL 数据库仅在 UTC 时间（无时区）中工作并且我们需要本地时间，因此我们必须更改数据库中GETDATE() 任何地方的使用，这已证明比我预期的要多。

我创建了一个用户定义的函数来获取适用于我的时区的本地时间：

CREATE FUNCTION [dbo].[getlocaldate]()
RETURNS datetime
AS
BEGIN
    DECLARE @D datetimeoffset;
    SET @D = CONVERT(datetimeoffset, SYSDATETIMEOFFSET()) AT TIME ZONE 'Pacific SA Standard Time';
    RETURN(CONVERT(datetime,@D));
END

我遇到的问题是GETDATE()在每个视图、存储过程、计算列、默认值、其他约束等中实际更改此函数。

实施此更改的最佳方式是什么？

我们处于托管实例的公共预览版中。它仍然有同样的问题GETDATE()，所以它对这个问题没有帮助。迁移到 Azure 是一项要求。这个数据库总是在这个时区使用（并将被使用）。

6 个回答

Voted

AMG · Answer 1 · 2018-07-05T11:17:48+08:00

Best Answer

AMG

2018-07-05T11:17:48+08:002018-07-05T11:17:48+08:00

使用 SQL Server 工具将数据库对象定义导出到 SQL 文件，其中应包括：表、视图、触发器、SP、函数等
使用任何允许您查找文本"GETDATE()"并将其替换为的文本编辑器编辑 SQL 文件（首先进行备份）"[dbo].[getlocaldate]()"
在 Azure SQL 中运行已编辑的 SQL 文件以创建数据库对象...
执行数据迁移。

在这里您可以参考 azure 文档：Generating Scripts for SQL Azure

17

Evan Carroll · Answer 2 · 2018-07-05T08:49:14+08:00

Evan Carroll

2018-07-05T08:49:14+08:002018-07-05T08:49:14+08:00

实施此更改的最佳方式是什么？

我会反过来工作。将数据库中的所有时间戳转换为 UTC，然后使用 UTC 并顺其自然。如果您需要不同 tz 中的时间戳，您可以使用AT TIME ZONE（如上面所做的那样）创建一个生成的列，该列在指定的 TZ 中呈现时间戳（对于应用程序）。但是，我会认真考虑让 UTC 返回应用程序，并在应用程序中编写该逻辑 - 显示逻辑。

15

David Spillett · Answer 3 · 2018-07-06T05:26:47+08:00

与其导出、手动编辑和重新运行，不如尝试直接在数据库中执行该工作，例如：

DECLARE C CURSOR FOR
        SELECT sm.definition, so.type
        FROM   sys.objects so
        JOIN   sys.all_sql_modules sm ON sm.object_id = so.object_id
        WHERE  so.type IN ('P', 'V')
        ORDER BY so.name
DECLARE @SQL NVARCHAR(MAX), @ojtype NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL, @ojtype
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') 
    IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW'     , 'ALTER VIEW'     ) 
    SET @SQL = REPLACE(@SQL, 'GETDATE()', '[dbo].[getlocaldate]()') 
    --PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL, @ojtype
END
CLOSE C
DEALLOCATE C

当然也可以扩展它来处理函数、触发器等。

有几个注意事项：

CREATE您可能需要更亮一点并处理和PROCEDURE/ VIEW/之间的不同/额外空白<other>。而不是REPLACEfor ，您可能更愿意将其留CREATE在原地并执行DROP第一个，但这可能会导致离开sys.depends和朋友不合时宜的风险ALTER，如果ALTER失败，您至少有现有的对象仍然存在DROP+CREATE您可能不是。
如果您的代码有任何“聪明”的味道，例如使用临时 TSQL 修改其自己的模式，那么您需要确保搜索和替换CREATE->ALTER不会干扰这一点。
您将希望在操作后对整个应用程序进行回归测试，无论您使用光标还是导出+编辑+运行方法。

我过去曾使用此方法进行类似的模式范围更新。这有点像 hack，感觉很丑，但有时它是最简单/最快的方法。

默认值和其他约束也可以类似地修改，尽管它们只能被删除和重新创建而不是改变。就像是：

DECLARE C CURSOR FOR
        SELECT AlterDefaultSQL = 'ALTER TABLE [' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
                               + CHAR(10)
                               + 'ALTER TABLE [' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', '[dbo].[getlocaldate]()')+' FOR '+sc.name+';'
        FROM   sys.tables st
        JOIN   sys.default_constraints si ON si.parent_object_id = st.object_id
        JOIN   sys.columns sc ON sc.default_object_id = si.object_id
DECLARE @SQL NVARCHAR(MAX)
OPEN C
FETCH NEXT FROM C INTO @SQL
WHILE @@FETCH_STATUS = 0 BEGIN
    --PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C

您可能需要应对一些更有趣的事情：如果您按时间分区，那么这些部分可能也需要更改。虽然按时间更精细地按天分区很少见，但您可能会遇到DATETIME分区函数将 s 解释为前一天或后一天的问题，具体取决于 timezine，从而使您的分区与通常的查询不一致。

Sting · Answer 4 · 2018-07-11T07:49:40+08:00

我真的很喜欢大卫的回答，并赞成以一种程序化的方式做事。

但是您今天可以尝试通过 SSMS 在 Azure 中进行测试：

右键单击您的数据库-> 任务-> 生成脚本..

[背景故事] 我们有一个初级 DBA，他将我们所有的测试环境升级到 SQL 2008 R2，而我们的生产环境是 SQL 2008。这一变化让我至今都畏缩不前。为了从测试迁移到生产环境，我们必须使用生成脚本在 SQL 中生成脚本，并且在高级选项中，我们使用“脚本的数据类型：模式和数据”选项来生成大量文本文件。我们成功地将我们的测试 R2 数据库移动到我们的旧版 SQL 2008 服务器——在这种情况下，将数据库还原到较低版本是行不通的。我们使用 sqlcmd 来输入大文件——因为这些文件对于 SSMS 文本缓冲区来说通常太大了。

我在这里要说的是，这个选项也可能对你有用。您只需要再执行一个步骤，然后在生成的文本文件中搜索 getdate() 并将其替换为 [dbo].getlocaldate。（不过，我会在迁移之前将您的函数放入数据库中）。

（我从不想精通这种数据库恢复的创可贴，但有一段时间它成为了一种事实上的做事方式。而且，它每次都有效。）

如果您选择这条路线，请务必选择“高级”按钮并选择您需要的所有选项（阅读每个选项）以从旧数据库移动到新数据库 - 就像您提到的默认值一样。但是在 Azure 中进行几次测试。我敢打赌，您会发现这是一种行之有效的解决方案——只需一点点努力。

KumarHarsh · Answer 5 · 2018-07-14T02:35:57+08:00

动态更改所有 proc 和 udf 以更改值

    DECLARE @Text   NVARCHAR(max), 
        @spname NVARCHAR(max), 
        @Type   CHAR(5), 
        @Sql    NVARCHAR(max) 
DECLARE @getobject CURSOR 

SET @getobject = CURSOR 
FOR SELECT sc.text, 
           so.NAME, 
           so.type 
    FROM   sys.syscomments sc 
           INNER JOIN sysobjects so 
                   ON sc.id = so.id 
    WHERE  sc.[text] LIKE '%getdate()%' 

--and type in('P','FN') 
OPEN @getobject 

FETCH next FROM @getobject INTO @Text, @spname, @Type 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF ( @Type = 'P' 
            OR @Type = 'FN' ) 
        SET @Text = Replace(@Text, 'getdate', 'dbo.getlocaldate') 

      SET @Text = Replace(@Text, 'create', 'alter') 

      EXECUTE Sp_executesql 
        @Text 

      PRINT @Text 

      --,@spname,@Type 
      FETCH next FROM @getobject INTO @Text, @spname, @Type 
  END 

CLOSE @getobject 

DEALLOCATE @getobject

    CREATE PROCEDURE [dbo].[Testproc1] 
AS 
    SET nocount ON; 

  BEGIN 
      DECLARE @CurDate DATETIME = Getdate() 
  END

注意注释的 sysobjects 类型列条件。我的脚本将只更改 proc 和 UDF。

这个脚本将改变所有Default Constraint包含GetDate()

    DECLARE @TableName      VARCHAR(300), 
        @constraintName VARCHAR(300), 
        @colName        VARCHAR(300), 
        @Sql            NVARCHAR(max) 
DECLARE @getobject CURSOR 

SET @getobject = CURSOR 
FOR SELECT ds.NAME, 
           sc.NAME AS colName, 
           so.NAME AS Tablename 
    --,ds.definition 
    FROM   sys.default_constraints ds 
           INNER JOIN sys.columns sc 
                   ON ds.object_id = sc.default_object_id 
           INNER JOIN sys.objects so 
                   ON so.object_id = ds.parent_object_id 
    WHERE  definition LIKE '%getdate()%' 

OPEN @getobject 

FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @Sql = 'ALTER TABLE ' + @TableName 
                 + ' DROP CONSTRAINT ' + @constraintName + '; ' 
                 + Char(13) + Char(10) + '           ' + Char(13) + Char(10) + '' 
      SET @Sql = @Sql + ' ALTER TABLE ' + @TableName 
                 + ' ADD CONSTRAINT ' + @constraintName 
                 + '          DEFAULT dbo.GetLocaledate() FOR ' 
                 + @colName + ';' + Char(13) + Char(10) + '          ' + Char(13) 
                 + Char(10) + '' 

      PRINT @Sql 

      EXECUTE sys.Sp_executesql 
        @Sql 

      --,@spname,@Type 
      FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 
  END 

CLOSE @getobject 

DEALLOCATE @getobject

Henrik Staun Poulsen · Answer 6 · 2019-01-24T23:42:04+08:00

我赞成 Evan Carrolls 的回答，因为我认为这是最好的解决方案。我无法说服我的同事他们应该更改大量 C# 代码，因此我不得不使用 David Spillett 编写的代码。我已经修复了 UDF、动态 SQL 和模式（并非所有代码都使用“dbo”）的几个问题，如下所示：

DECLARE C CURSOR LOCAL STATIC FOR
        SELECT sm.definition, so.type
        FROM   sys.objects so
        JOIN   sys.all_sql_modules sm ON sm.object_id = so.object_id
        WHERE  so.type IN ('P', 'V')
        AND CHARINDEX('getdate()', sm.definition) > 0
        ORDER BY so.name

DECLARE @SQL NVARCHAR(MAX), @objtype NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
    FETCH NEXT FROM C INTO @SQL, @objtype
    IF @@FETCH_STATUS <> 0 BREAK

    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') 
    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE   PROCEDURE', 'ALTER PROCEDURE') /* when you write "create or alter proc" */
    IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW'     , 'ALTER VIEW'     ) 
    IF CHARINDEX('getdate())''', @sql) > 0 BEGIN  /* when dynamic SQL is used */
        IF CHARINDEX('utl.getdate())''', @sql) = 0 SET @SQL = REPLACE(@SQL, 'GETDATE()', 'utl.getdate()') 
    end
    ELSE begin
        SET @SQL = REPLACE(@SQL, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset,  SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')') 
    end
    EXEC dbo.LongPrint @String = @sql    
    EXEC (@SQL)
END
CLOSE C
DEALLOCATE C

和这样的默认约束：

DECLARE C CURSOR LOCAL STATIC FOR
        SELECT AlterDefaultSQL = 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
                               + CHAR(10)
                               + 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset,  SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')+' FOR '+sc.name+';'
        FROM   sys.tables st
        JOIN   sys.default_constraints si ON si.parent_object_id = st.object_id
        JOIN   sys.columns sc ON sc.default_object_id = si.object_id
        INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
        WHERE CHARINDEX('getdate()', si.definition) > 0
        ORDER BY st.name, sc.name

DECLARE @SQL NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
    FETCH NEXT FROM C INTO @SQL
    IF @@FETCH_STATUS <> 0 BREAK

    EXEC dbo.LongPrint @String = @sql  
    EXEC (@SQL)
    FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C

UDF
使用返回今天日期和时间的 UDF 的建议看起来不错，但我认为 UDF 仍然存在足够多的性能问题，因此我选择使用非常长且丑陋的 AT TIME ZONE 解决方案。

在整个数据库中更改 GETDATE() 的使用

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

在整个数据库中更改 GETDATE() 的使用

6 个回答

相关问题