Here is another "Zero downtime" option that uses partition switching.
See the inline comments for the details...
USE tempdb;
GO
--=============================================================================
-- Create the "original" table and populate it
IF OBJECT_ID('dbo.OriginalTable', 'U') IS NOT NULL
DROP TABLE dbo.OriginalTable;
CREATE TABLE dbo.OriginalTable (
SomeNumber INT NOT NULL
CONSTRAINT pk_OriginalTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]
);
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (SomeNumber) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))--(1M sequential numbers)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
INSERT dbo.OriginalTable (SomeNumber)
SELECT t.SomeNumberFROM cte_Tally t;
-- Verify that 1,000,000 rows were inserted...
--SELECT row_count = COUNT(*) FROM dbo.OriginalTable ot;
--=============================================================================
-- Create a "New" table that contains the newly refreshed values.
-- Note: The NewTable must have the same structure as the OriginalTable... Including indexes!!!
IF OBJECT_ID('dbo.NewTable', 'U') IS NOT NULL
DROP TABLE dbo.NewTable;
CREATE TABLE dbo.NewTable (
SomeNumber INT NOT NULL
CONSTRAINT pk_NewTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]
);
-- add some "refreshed" data...
WITH
n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Tally (SomeNumber) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 2 --(1M even numbers)
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
INSERT dbo.NewTable (SomeNumber)
SELECT t.SomeNumberFROM cte_Tally t;
-- Again... Verify that 1,000,000 rows were inserted...
--SELECT row_count = COUNT(*) FROM dbo.NewTable ot;
--=============================================================================
--=============================================================================
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
-------------------------
DECLARE @SwitchStart DATETIME2(7) = SYSDATETIME();
-- 1) Truncate the original...
TRUNCATE TABLE dbo.OriginalTable;
-- 2) Make the switch...
ALTER TABLE dbo.NewTable SWITCH TO dbo.OriginalTable;
-- See how long it took to actually make the switch...
SELECT DownTimeInMS = DATEDIFF(ms, @SwitchStart, SYSDATETIME())
-- 3) Trash NewTable (it's now empty)
DROP TABLE dbo.NewTable;
-------------------------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION;
DECLARE @msg nvarchar(2048) = error_message();
RAISERROR (@msg, 16, 1);
END CATCH;
-- 4) verify that Original table has the refreshed values.
SELECT TOP 1000 * FROM dbo.OriginalTable ot;