none
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?


    Thanks in advance,
    Shai.

    Sunday, June 03, 2007 5:22 PM

Answers

  • 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