Home » Microsoft » MB6-886 » Which strategy should you use?
You work in an International company named TAKEEEN. And you’re in charge of the database of your company. You intend to use SQL Server 2008 to create a database solution. Large image files will be stored in a table. The files are about 20-50 MB in size. The requirements below have to be met:
• The image files are part of the database backup;
• The image files are accessible by applications that use Win32 APIs.
You have to store the images files by using a suitable strategy. So which strategy should you use?
A. An image data type should be used
B. The varbinary(max) data type should be used
C. The varbinary(max) data type should be used along with the FILESTREAM attribute
D. The image file should be stored in a file system. Then you store the file location in the database by using a varchar data type
Correct Answer: C
Explanation/Reference:
Microsoft recommends not to use image data type because it will be removed in a future version of Microsoft SQL Server. So avoid using these data type in new development work, and plan to modify applications that currently use it. Use varbinary(max) instead.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:
• Objects that are being stored are, on average, larger than 1 MB.
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.
For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.
You should not use the last option (D) because in this case the image files are NOT part of the database backup.
