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 approach 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.
