How to declare a blob in SQL Server

Question

I want to do some tests to my database (like turning off the machine while it's still writing something). To do this, I'm planning to insert a movie file into the database with 700mb, so that I can have time to insert it and turn it off (instead of being something done instantaneously).

I'm using SQL Server 2008, and the closest I can find in the data types is Binary(50). Is this enough for what I want?

I want to know which data type must the column that will store this large file be.

Answer 1

Binary(50) will hold 50 bytes - this is not going to be enough to hold 700mb.

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

You should use VARBINARY(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.

You could also use Image, though it is deprecated.