CODEWITHSUNDEEP
sqlsql-serversqldatatypesvarbinary
fishmong3r
fishmong3r

Reputation: 1434

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?

Upvotes: 4

Views: 8594

Answers (2)

GarethD
GarethD

Reputation: 69769

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'.

Upvotes: 8

knkarthick24
knkarthick24

Reputation: 3216

There is no provision to specify varbinary(2097152), Once if you cross 8000 bytes,you should go for max keyword only.

It will consume only the memory you need (in your case it is 2MB) and it will store upto 2GB at max.

varbinary(max) = 2 gigabytes.

Upvotes: 0

Related Questions