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?
Thanks in advance,
Shai.
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
|||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
If you really want to fill the random numbers between the given range the following query is more enough..
Code Snippet
CreateTable #randomBinaryValue
(
valuevarbinary(32)constraint PKrandomBinaryValueprimary key nonclustered --Just for verfication/avoid duplicate
);
Go
SETNOCOUNTON;
Declare @.Iasint;
Set @.I= 0;
WHILE @.I< 10000
Begin
InsertINto #randomBinaryValuevalues(convert(varbinary,newid()));
Set @.I= @.I+ 1;
End
Select*From #randomBinaryValue
Go
DropTable #randomBinaryValue
|||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.
|||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
*/
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
No comments:
Post a Comment