在SQL Server 2014 Developer中高效地生成20亿行

CREATE TABLE Data (
    ID INT PRIMARY KEY,
    Column1 VARCHAR(100),
    Column2 INT,
    Column3 DATETIME
);

DECLARE @RowCount INT = 0;
DECLARE @BatchSize INT = 100000; -- 每批插入的行数
DECLARE @TotalRows INT = 2000000000; -- 总共需要生成的行数

WHILE @RowCount < @TotalRows
BEGIN
    INSERT INTO Data (ID, Column1, Column2, Column3)
    SELECT @RowCount + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
           'Value ' + CAST(@RowCount + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(100)),
           @RowCount + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
           GETDATE();

    SET @RowCount = @RowCount + @BatchSize;
END

ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;

ALTER TABLE Data NOCHECK CONSTRAINT ALL;
ALTER INDEX ALL ON Data DISABLE;

COMMIT;

ALTER INDEX ALL ON Data REBUILD;
ALTER TABLE Data CHECK CONSTRAINT ALL;
ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;