MS SQL数据库10米行，VARCHAR转换为int的列上 [英] MS SQL Database with 10m rows, convert varchar to int on column

问题描述

我们已经继承了一个数据库，它有10米行，这样的查询时，经常运行：

We've inherited a database, it's got 10m rows and a query like this, that runs quite often:

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

numSeconds是一个整数，通过对从2000年的0号，但该列类型是nvarchar的 - 我们需要转换为INT不损失任何数据，该表是巨大的。

numSeconds is an integer, numbers from 0 through to 2000, but the column type is nvarchar - we need to convert to INT without loosing any data, and the table is huge.

我们如何能做到这一点的任何意见或建议？感谢所有。

Any ideas or suggestions on how we can do this? Thanks all.

推荐答案

这工作，是原子（全或无）

This works and is 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

下面的作品，但非原子。如果更新失败，​​你了小溪。

The below works, but Non-Atomic. And if the Update Fails, you're up the creek.

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

这篇关于MS SQL数据库10米行，VARCHAR转换为int的列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！