MS SQL数据库有10万行，在列上将varchar转换为int

Question

我们继承了一个数据库，它有10万行，像这样的查询，经常运行:

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

这个可以工作并且是Atomic (all or nothing)

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

---------- EDIT

下面的工作，但非原子。如果更新失败，你就麻烦了。

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