Search code examples
sqlsql-serversqldatatypesvarbinary

Can I set 2 MB for maximum size of varbinary?

As far as I know the maximum value you can define "manually" is 8000 -> varbinary(8000) which, as far as I know, means 8000 bytes -> 7,8125 KByte.

Is it possible to set max to 2 MB? Something ike varbinary(2097152), or shall I set it to varbinary(max) and check the file size through my upload/sql insert script?

Solution

  • You could use a CHECK CONSTRAINT to ensure the size is below 2MB:

    CREATE TABLE dbo.T
(
    ID INT IDENTITY,
    VarB VARBINARY(MAX)
);

ALTER TABLE dbo.T ADD CONSTRAINT CHK_T_VarB__2MB CHECK (DATALENGTH(VarB) <= 2097152);

    Then when trying to insert something larger than 2 MB:

    DECLARE @B VARCHAR(MAX) = '|';
INSERT dbo.T (VarB)
SELECT CONVERT(VARBINARY(MAX), REPLICATE(@B, 2097153));

    You get an error:

    The INSERT statement conflicted with the CHECK constraint "CHK_T_Column__2MB". The conflict occurred in database "TestDB", table "dbo.T", column 'VarB'.