Insert media file in SQL Server

Question

dear all How to insert media file such as *.mepeg or *.mp3 ,..... files in SQL Server

Answer 1

USE pubs
CREATE TABLE mycustomertable
(
user_login DEFAULT SUSER_SID, 
data_value
)

INSERT mycustomertable (data_value)
  VALUES (0x4F)

from http://en.allexperts.com/q/MS-SQL-1450/Binary-Data.htm

Answer 2

I tend to agree with @JoshD, but if you must store binary files such as digital video/audio in the database, SQL Server provides the varbinary datatype.

Note that the default maximum length for this type is 8000 bytes - likely not enough storage for the average audio/video file. Note the use of varbinary(max) for data larger than the 8K limit:

max indicates that the maximum storage size is 2^31-1 bytes.

Answer 3

• Should one store files in the database? To BLOB or Not to BLOB:

The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how write intensive the workload is, and the storage age of a typical replica in the system.

• How about filestream? FILESTREAM Storage in SQL Server 2008.

There are many more questions on this simple topic, when one starts to consider High Availability and Disaster Recoverability requirements, the need for a consistent backup-restore strategy, the question whether the media is accessed over an HTTP interface and the processing overhead of a URL that returns content from the database, and so on and so forth.

But to answer the naive question: you store media files in the database as VARBINARY(MAX) columns. You manipulate them in the client as SqlBinary types. One must use streaming semantics when manipulating large BLOBs, like using CommandBehavior.SequentialAccess as described in Retrieving Large Data (ADO.NET). If Win32 API access is needed, then FILESTREAM type must be used. More reading material on the topic, with samples, at SQL Server Binary and Large-Value Data (ADO.NET).

Oh, and before I forget: you also need columns to store the file metadata, like its name and type. These are needed to add the content-disposition and content-type headers if the media is streamed over HTTP responses.