Base de datos MS SQL con 10m filas, convierta varchar a int en la columna

Hemos henetworkingado una database, tiene 10 millones de filas y una consulta como esta, que se ejecuta con bastante frecuencia:

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

numSeconds es un número integer, numbers desde 0 hasta 2000, pero el tipo de columna es nvarchar; necesitamos convertir a INT sin perder ningún dato, y la tabla es enorme.

¿Alguna idea o sugerencia sobre cómo podemos hacer esto? Gracias a todos.

Esto funciona y es atómico (todo o nada)

 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 

———- EDITAR

El siguiente funciona, pero no atómico. Y si falla la actualización, estás en el arroyo.

 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