MS SQL数据库，10m行，将varchar转换为int on column

Question

我们继承了一个数据库，它有10m行和一个类似这样的查询，它经常运行：

SELECT SUM(CONVERT(INT,numSeconds)) AS total_dwell from [dbo].[Orders] 
where category='Shoes' AND CONVERT(INT,numSeconds)<300

numSeconds是一个整数，数字从0到2000，但列类型是nvarchar -我们需要在不丢失任何数据的情况下转换为INT，并且表很大。

关于我们如何做到这一点，有什么想法或建议吗？谢谢大家。

Answer 1

这是有效的，并且是原子的(全部或全无)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ooopsie]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[Ooopsie]
    END
GO

CREATE TABLE [dbo].[Ooopsie] (
      [OoopsieKey] [smallint] not null
    , OoopsieColumn varchar(64) not null
) 

GO



IF EXISTS ( SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Ooopsie' and TABLE_SCHEMA = 'dbo'    )
    BEGIN

        IF EXISTS 
        (
            SELECT * 
                FROM [INFORMATION_SCHEMA].[COLUMNS] 
            WHERE   
                TABLE_NAME = 'Ooopsie' 
                AND TABLE_SCHEMA = 'dbo'
                AND COLUMN_NAME = 'OoopsieColumn'
                        and DATA_TYPE != 'int'
        )
            BEGIN

                ALTER TABLE [dbo].[Ooopsie]
                ALTER COLUMN [OoopsieColumn] int NOT NULL;
            END


    END

GO

--编辑

下面的代码是有效的，但不是原子的。如果更新失败了，你就有麻烦了。

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ooopsie]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[Ooopsie]
    END
GO

CREATE TABLE [dbo].[Ooopsie] (
      [OoopsieKey] [smallint] not null
    , OoopsieColumn varchar(64) not null
) 

GO


IF EXISTS ( SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Ooopsie' and TABLE_SCHEMA = 'dbo'    )
    BEGIN

        IF NOT EXISTS 
        (
            SELECT * 
                FROM [INFORMATION_SCHEMA].[COLUMNS] 
            WHERE   
                TABLE_NAME = 'Ooopsie' 
                AND TABLE_SCHEMA = 'dbo'
                AND COLUMN_NAME = 'OoopsieInt'
        )
            BEGIN

                ALTER TABLE [dbo].[Ooopsie]
                ADD [OoopsieInt] int NOT NULL;
            END


    END

GO


IF EXISTS 
(
    SELECT * 
        FROM [INFORMATION_SCHEMA].[COLUMNS] 
    WHERE   
        TABLE_NAME = 'Ooopsie' 
        AND TABLE_SCHEMA = 'dbo'
        AND COLUMN_NAME = 'OoopsieInt'
)
    BEGIN
        Update dbo.Ooopsie Set OoopsieInt = convert (int , OoopsieColumn) 
    END

GO



IF EXISTS 
(
    SELECT * 
        FROM [INFORMATION_SCHEMA].[COLUMNS] 
    WHERE   
        TABLE_NAME = 'Ooopsie' 
        AND TABLE_SCHEMA = 'dbo'
        AND COLUMN_NAME = 'OoopsieColumn'
)
    BEGIN
                ALTER TABLE [dbo].[Ooopsie]
                DROP COLUMN [OoopsieColumn];
    END

GO


IF EXISTS 
(
    SELECT * 
        FROM [INFORMATION_SCHEMA].[COLUMNS] 
    WHERE   
        TABLE_NAME = 'Ooopsie' 
        AND TABLE_SCHEMA = 'dbo'
        AND COLUMN_NAME = 'OoopsieInt'
)
    BEGIN

        EXEC sp_rename
            @objname = 'Ooopsie.OoopsieInt',
            @newname = 'OoopsieColumn',
            @objtype = 'COLUMN'
            END

GO