Storing files in SQL Server

by

There’s a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

  • if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008’s FILESTREAM attribute, they’re still under transactional control and part of the database)

  • in between those two, it’s a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures – do not store the employee photo in the employee table – keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don’t always need to select the employee photo, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let’s call it “LARGE_DATA”.

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!

More Related Contents:

  1. How to get the last row from a table in SQL Server?
  2. Alias name is same as column name [closed]
  3. LocalDB deployment on client PC
  4. Understanding PIVOT function in T-SQL
  5. The order of a SQL Select statement without Order By clause
  6. Using RegEx in SQL Server
  7. What is the best way to auto-generate INSERT statements for a SQL Server table?
  8. Storing a file in a database as opposed to the file system?
  9. SQL Server named instance with Visual Studio 2017 Installer project
  10. How do you count the number of occurrences of a certain substring in a SQL varchar?
  11. Why is it considered bad practice to use cursors in SQL Server?
  12. Import Excel spreadsheet columns into SQL Server database
  13. CAST and IsNumeric
  14. SQL Server – inner join when updating [duplicate]
  15. How do I use the CONCAT function in SQL Server 2008 R2?
  16. How do I exclude Weekend days in a SQL Server query?
  17. Best data store for billions of rows
  18. newid() inside sql server function
  19. How can I use a single mssql connection pool across several routes in an Express 4 web application?
  20. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  21. How do I list all tables in all databases in SQL Server in a single result set?
  22. Retrieving date in sql server, CURRENT_TIMESTAMP vs GetDate()
  23. SQL Server 2008 Vertical data to Horizontal
  24. Joining multiple tables returns NULL value
  25. Which join syntax is better?
  26. What are some ways of accessing Microsoft SQL Server from Linux?
  27. LPAD in SQL Server 2008
  28. How to maintain the order of insertion in SQL Server
  29. How does SQL Server decide format for implicit datetime conversion?
  30. The Best Way to shred XML data into SQL Server database columns

Leave a Comment