Datatype for storing video in SQL Server 2000

Question

What datatype would be used for storing video files in sql server2000?

Answer 1

Use BLOBs (binary large objects). Documentation here. More specifially:

In SQL Server, BLOBs can be text, ntext, or image data type

image

Variable-length binary data from 0 through 231 - 1 (2,147,483,647) bytes.

Answer 2

Save yourself a headache and store them on the file system. Store the path in the DB.

Currently I can't think of a really good reason to store videos in a database, when there is a file system available. What are your's?

Answer 3

In most cases, a database isn't the ideal place for video. You can force it (varbinary(max), or image on older versions), but if you do this, you'll need to be very careful to use steraming access rather than "get all" access (if you see what I mean). And you'd probably want the video on a different file-group (and probably drive) than regular data.

In SQL Server 2008 there is the filestream type, which is a hybrid between db and file system. Maybe this will do what you need?

If not, just use the file system.

One other reason not to use video in the DB; if you have a small system, SQL Server Express might be ideal - but has a db size cap that video will quickly swamp.

Answer 4

In SQL Server 2000 BLOB support is not good enough and you should definitively store files directly on disk. In SQL Server 2008 support for storing files in the database is supposed to be a lot better. I have never tried it as we had already built our application to store files on disk. But I think I would still recommend to store files on disk as it provides more flexibility in terms of storage; you can easily back it up and move files around. Also it reduces the size of the database which likely will make it faster.

So store the path in your database and the actual files in the file system.

Answer 5

I agree with Tomalak, even pictures and other smaller assets are much better served outside of the database. Our products use only reference paths to the assets in the DB, with th DB serving as a dictionary to location.

Of course the one down side is the disconnect that can develop between the filesystem and the DB entries, so you need to pay special attention to how you set this up and maintain it in an ongoing fashion.