# How to create and fill a random varbinary table?

### Question

• Hi all,

For a test I'm performing I need to create a table with varbinary(120000) column.
I need to fill this table with 1000000 rows of random varbinary values. (The values should be from 0x1 till 0xfff....f)
Does anyone have a "nice" way to create this table?

Shai.

Sunday, June 03, 2007 5:22 PM

• Shai:

I used this query as the basis to generate the script that follows:

Code Snippet
select '                  convert(binary(16), newid()) + '
from small_iterator (nolock)
where iter <= 500

What I found when I ran my mockup is that on my desktop server it takes about 67 ms to create each varbinary(max) record with a length of 120000.  Therefore, just to generate 1000000 rows of such on my desktop unit might take on the order of 18 hours!  The problem is that creating very long VARBINARY(MAX) data takes a fair amount of time.  I used a query like this:

Code Snippet

declare @begDt datetime    set @begDt = getdate()

declare @loopLimit integer     set @loopLimit = 10

declare @loopCt integer
declare @hugeVarbinary varbinary (max)

set @loopCt = 1
while @loopCt <= @loopLimit
begin

select @hugeVarbinary =
( select convert(binary(16), newid()) +
convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)

select @hugeVarbinary = @hugeVarbinary +
( select convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)
from small_iterator (nolock)
where iter <= 14

set @loopCt = @loopCt + 1

end

print ' '
select @loopLimit as [@loopLimit],
datediff (ms, @begDt, getdate()) as [Elapsed Time]

/*  Times in seconds
10 Records:    0.690   0.686   0.673    Average:   0.686
100 Records:    6.966   6.766   6.890    Average:   6.874
1000 Records:   68.030  67.156  67.436    Average:  67.541
10000 Records:  681.720

*/

Monday, June 04, 2007 1:17 PM
• Honestly, if you really only just need a random varbinary value, why not just build each new one on the fly?   Somthing like this:

create view randomVarbinary as

select

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) as crazyBigVarbinary

go

select *

from randomVarbinary

Wednesday, June 06, 2007 1:08 AM

### All replies

• You might be able to leverage (1) a table of numbers and (2) a scalar UDF that allows each row to have a different random results.  Give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330539&SiteID=1

Sunday, June 03, 2007 8:16 PM
• You can do something like this:

set nocount on
go
alter function getHexCharacter
(
@i int
)

--used to return a character 0-9 and A-F for inputs from 0-15
returns char(1)
as
begin
return char(case when @i <= 9 then @i + 48
when @i > 9 and @i <= 15 then @i + 55
else null end)
end
go

create table #randomBinaryValue
(
value  varbinary(2) constraint PKrandomBinaryValue primary key
)
go
declare @i int, @newValue char(4) set @i = 0
while (1=1)
begin
--build up the text of the
select @newValue =
'0x' + dbo.getHexCharacter(cast(rand() * 15 as int))
+ dbo.getHexCharacter(cast(rand() * 15 as int))

--add more getHexCharacters to get a longer result
-- + dbo.getHexCharacter(cast(rand() * 15 as int))

begin try
exec (' insert into #randomBinaryValue select ' + @newValue)
set @i = @i + 1
end try
begin catch
select 'don''t update the counter',error_message()
end catch

if @i > 100 break
end

select *
from #randomBinaryValue
go
drop table #randomBinaryValue

Sunday, June 03, 2007 11:15 PM
• If you really want to fill the random numbers between the given range the following query is more enough..

Code Snippet

Create Table #randomBinaryValue

(

value  varbinary(32) constraint PKrandomBinaryValue primary key nonclustered --Just for verfication/avoid duplicate

);

Go

SET NOCOUNT ON;

Declare @I as int;

Set @I = 0;

WHILE @I < 10000

Begin

Insert INto #randomBinaryValue values(convert(varbinary,newid()));

Set @I = @I  + 1;

End

Select * From #randomBinaryValue

Go

Drop Table #randomBinaryValue

Monday, June 04, 2007 6:06 AM
•

All the ideas are nice but...

casting newid will only generate me a 16 byte varbinary and I need 120000 byte varbinary.

create a 120000 varbinary with a loop and then inserting it into a table will take a long time for 1000000 rows...

Does any one have a quicker way?

Thanks,

Shai.

Monday, June 04, 2007 6:43 AM
• Shai:

I used this query as the basis to generate the script that follows:

Code Snippet
select '                  convert(binary(16), newid()) + '
from small_iterator (nolock)
where iter <= 500

What I found when I ran my mockup is that on my desktop server it takes about 67 ms to create each varbinary(max) record with a length of 120000.  Therefore, just to generate 1000000 rows of such on my desktop unit might take on the order of 18 hours!  The problem is that creating very long VARBINARY(MAX) data takes a fair amount of time.  I used a query like this:

Code Snippet

declare @begDt datetime    set @begDt = getdate()

declare @loopLimit integer     set @loopLimit = 10

declare @loopCt integer
declare @hugeVarbinary varbinary (max)

set @loopCt = 1
while @loopCt <= @loopLimit
begin

select @hugeVarbinary =
( select convert(binary(16), newid()) +
convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)

select @hugeVarbinary = @hugeVarbinary +
( select convert(binary(16), newid()) +
...

convert(binary(16), newid()) +
convert(binary(16), newid())
)
from small_iterator (nolock)
where iter <= 14

set @loopCt = @loopCt + 1

end

print ' '
select @loopLimit as [@loopLimit],
datediff (ms, @begDt, getdate()) as [Elapsed Time]

/*  Times in seconds
10 Records:    0.690   0.686   0.673    Average:   0.686
100 Records:    6.966   6.766   6.890    Average:   6.874
1000 Records:   68.030  67.156  67.436    Average:  67.541
10000 Records:  681.720

*/

Monday, June 04, 2007 1:17 PM
• Honestly, if you really only just need a random varbinary value, why not just build each new one on the fly?   Somthing like this:

create view randomVarbinary as

select

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) +

cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) + cast(newId() as varbinary(16)) as crazyBigVarbinary

go

select *

from randomVarbinary

Wednesday, June 06, 2007 1:08 AM