SQL Server and 1 Trillion records for sample data set

Here is some sample SQL one can review for setting up your test database data.

 

From inception to the final function at the bottom, then I can create some other tables to work with the 1 trillion records against other meta data; GIS, GPS, Financial, Statistical, Medical, etc. Once you have 1 trillion rows you can shape and warp it all day long.

 

The SQL I used to create it is below. The parallelism trick is just launch the last function 16 times in separate execution threads, and voila. Change the target @n to 10m, 100m, etc. Having two research servers to run this 16 x 2 is pretty fast.

 

Enjoy!

 

 

-- create database trillion;

use trillion;
/*
create table trillion_rows (
row_id    int identity,
val1    int null,
val2    int null,
fact1    float null,
fact2    float null,
real1    real null,
real2    real null,
money1    money null,
money2    money null,
text1    varchar(255) null,
text2    varchar(255) null,
long1    bigint null,
long2    bigint null
);
*/

-- sp_columns trillion_rows;
-- select rand();
-- select newid();

-- 1 trillion 1,000,000,000,000
declare @x int, @y int;
-- select @x = 0, @y = 1000000000000
select @x = 0, @y = 100000
while @x < @y
BEGIN
    -- insert into trillion_rows (val1, val2, fact1, fact2, real1, real2, money1, money2, text1, text2, long1, long2)
    insert into trillion_rows (val1, val2, fact1, fact2)
    Select
    round(rand() * 100,0), -- val1
    round(rand() * 100000,0), --val2
    rand() * 100, --fact1
    rand() * 1000 --fact2

    select @x = @x + 1;
END

-- create index row_id_index on trillion_rows (row_id);

-- select * from trillion_rows
-- select top 1000 * from trillion_rows

declare @i int, @j int;
-- select @i = 0, @j = 1000000000000
select @i = 45124658, @j = 45124659
while @i < @j
BEGIN
    update trillion_rows set real1 = round(rand() * 100,0), real2 = round(rand() * 100000,0), money1 = rand() * 1000, money2 = rand() * 100000, text1 = newid(), text2 = newid(), long1 = round(rand() * 1000000000000000,0), long2 = round(rand() * 100000000000001,0)
    -- update trillion_rows set real1 = round(rand() * 100,0), real2 = round(rand() * 100000,0), money1 = rand() * 100, money2 = rand() * 1000
    -- update trillion_rows set text1 = newid(), text2 = newid()
    -- update trillion_rows set long1 = round(rand() * 10000000000000000000,0), long2 = round(rand() * 1000000000000000001,0)
    -- where row_id = @i
    where row_id = @i
    select @i = @i + 1;
END

-- alter the database to support expanded data format

-- select @i = 0, @j = 1000000000000
-- so the first 45 million minus the count to 1 trillion, check your disk space fella
-- how long will this run? oh Shit!
-- select @m = 0, @n = 1000000000000 - 45124659
-- how long will this run? oh Shit! let's do 1 billion first
-- select @m = 0, @n = 1000000000 - 45124659
-- select @m = 0, @n = 1000000000000 - 80913316 -- 1 trillion
-- select @m = 0, @n = 1000000000 - 80913316 -- 1 billion
-- select @m = 0, @n = 100000000 - 80913316 -- 1 billion

    -- update trillion_rows set real1 = round(rand() * 100,0), real2 = round(rand() * 100000,0), money1 = rand() * 1000, money2 = rand() * 100000, text1 = newid(), text2 = newid(), long1 = round(rand() * 1000000000000000,0), long2 = round(rand() * 100000000000001,0)
    -- update trillion_rows set real1 = round(rand() * 100,0), real2 = round(rand() * 100000,0), money1 = rand() * 100, money2 = rand() * 1000
    -- update trillion_rows set text1 = newid(), text2 = newid()
    -- update trillion_rows set long1 = round(rand() * 10000000000000000000,0), long2 = round(rand() * 1000000000000000001,0)
    -- where row_id = @i
    -- where row_id = @m

 

-- ACTUAL FUNCTION to populate random data into database.

declare @m int, @n int;
select @m = 0, @n = 100000000 -- 100 million

while @m < @n
BEGIN
insert into trillion_rows (val1, val2, fact1, fact2, real1, real2, money1, money2, text1, text2, long1, long2)
    Select
    round(rand() * 100,0), -- val1
    round(rand() * 100000,0), --val2
    rand() * 100, --fact1
    rand() * 1000, --fact2
    round(rand() * 100,0), --real1
    round(rand() * 100000,0), --real2
    rand() * 1000, --money1
    rand() * 100000, --money2
    newid(), --text1
    newid(), --text2
    round(rand() * 1000000000000000,0), --long1
    round(rand() * 100000000000001,0) --long2
    select @m = @m + 1;
END

REF: Continuation of testing  

 

more to come...