Results 1 to 5 of 5

Thread: VARBINARY(max)

  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Question Unanswered: VARBINARY(max)

    I have this table:

    CREATE TABLE NormalTest2 (
    IDsample INT NOT NULL PRIMARY KEY REFERENCES Identification1(IDsample) IDENTITY(1,1),
    NT VARBINARY(max) NOT NULL
    )

    and I use this stored procedure:

    CREATE PROCEDURE uspInsertNTvb
    @NT VARBINARY(max)
    AS
    INSERT INTO NormalTest2(NT) VALUES (@NT);

    Using VARBINARY(max), the problem is that I insert only 128 bytes (128 hexadecimal numers) of 1600 bytes (1600 hexadecimal numers) that I try to put into. Instead the MSDN library says that I can use till 2^31-1 bytes. Here are the 128 hexadecimal numbers that I can insert only, which are represent from 256 characters:

    0x07A7078908EA07E608F708A0071F ... EE0807070E07E5

    So I try to change VARBINARY(max) to VARBINARY(1600) and to
    VARBINARY(150), but the result is the same. If I put VARBINARY(100) I have only 100 hexadecimal numers.

    MSDN:
    binary [ ( n ) ]
    Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

    varbinary [ ( n | max) ]
    Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The SQL-2003 synonym for varbinary is binary varying.

    Remarks When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    Use binary when the sizes of the column data entries are consistent.
    Use varbinary when the sizes of the column data entries vary considerably.
    Use varbinary(max) when the column data entries exceed 8,000 bytes.

    Thank you!
    Gabriele

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Works fine for me. How do you know only 128 bytes? Have you tested with DATALENGTH()?

  3. #3
    Join Date
    Jun 2008
    Posts
    6

    Question

    Quote Originally Posted by pootle flump
    Works fine for me. How do you know only 128 bytes? Have you tested with DATALENGTH()?
    No, I only count the number of couple of hexadecimal numbers that MS SSMS Express displays into the the text format. Now I have just tried to run this statement:

    USE TestMemoriaNT;
    GO
    SELECT numberBytes = DATALENGTH(NT), numberCharacters = LEN(NT), NT
    FROM NormalTest2
    GO

    The result is this:

    numberBytes numberCharacters NT
    256 256 0x07A98097B0906....
    1600 1600 0x07A98097B0906....
    1600 1600 0x07A98097B0906....
    128 128 0x07A98097B0906....
    ... ... ...

    So the NT records appear exatly the same into the sheet result,
    but the numbers of bytes and numbers of characters chage exacly like
    I change the insertions.

    May be is it a problem with displaying the records??

    Now I try to read the data from a VB.NET application.

    Gabriele

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not a problem - an optimisation - SSMS does not display all characters by default. Check the settings if you want to play around with things.

  5. #5
    Join Date
    Jun 2008
    Posts
    6

    Thumbs up

    Quote Originally Posted by pootle flump
    Not a problem - an optimisation - SSMS does not display all characters by default. Check the settings if you want to play around with things.
    I change the flag: 8192 is the maximum number of chatacters for my SSMS Express, but it is enought because I need to view anly 3202 characters (0x plus 1600 hexadecimal numbers).

    For a trial flag I spent a lot of time...

    Thank very much!!!

    Gabriele

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •