Anton Burtsev

If you want your database to be fast with huge amount of data, you should test it with large test database. To create test database you may need many guids for row ids. If you want to create a list of guids and put them into a SQL table you may write the following:

create table ids(id uniqueidentifier);

declare @guidCount int = 10000;
declare @n int = 0;
while @n < @guidCount
begin
   insert ids values(NEWID());
   set @n = @n + 1;
end

This code creates 10 000 guids.
If you need more guids it can be very slow. You then modify the code as follows

create table ids(id uniqueidentifier);

declare @ids table(id uniqueidentifier default NEWID());
declare @guidCount int = 100000;
declare @n int = 0;
while @n < @guidCount
begin
   insert @ids default values;
   set @n = @n + 1;
end

insert ids with(tablockx)
select * from @ids;

This code creates 100 000 guids with the same period of time.
If you need even more guids the second aproach also can be slow and memory consumptive. But what if we won’t generate guids in memory? Instead, we just generate counter and then use it to generate guids:

create table ids(id uniqueidentifier);

declare @ids table(id int identity)
declare @guidCount int = 1000000;
declare @n int = 0;
while @n < SQRT(@guidCount)
begin
   insert @ids default values;
   set @n = @n + 1;
end

insert ids with(tablockx)
select NEWID() from @ids i1, @ids i2;

This code creates 1 000 000 guids also with the same period of time as the first and second ones!
I use this pattern to generate huge test databases. If you need volume testing – this pattern is for you.

bye.
See you on codeproject