Storing images in SQL Server

0 votes

I've created a little demo site where I'm storing images in a SQL server image column. I have a few inquiries, like...

  • Is this a poor plan?
  • Will it have an impact on my site's performance as it expands?

The alternative would be to keep the image on a disc and merely keep a database reference to it. This must be a problem that plenty of people have encountered. I'd be pleased to hear some suggestions, and if I could avoid making a mistake altogether, all the better.

Sep 14 in Database by Kithuzzz
• 12,240 points
23 views

1 answer to this question.

0 votes

Following extensive performance testing and analysis, the following is the conclusion:

  • It is more effective to save your images or documents in a database's VARBINARY column if they are normally less than 256 KB in size.
  • Storage in the filesystem is more effective if your images or documents are frequently larger than 1 MB; also, thanks to SQL Server 2008's FILESTREAM characteristic, they remain subject to transactional control and a part of the database.
  • Depending on your use, it's kind of a toss-up between those two.

If you choose to store your photos in a SQL Server table, I would strongly advise utilising a distinct table for that purpose. Do not maintain the employee photo in the employee table. If you don't always need to choose the employee photo as part of your queries, the Employee table can be slim, mean, and incredibly efficient.

For an introduction to filegroups, see Files and Filegroup Architecture. In essence, you would either design your database from the start with a separate filegroup for huge data structures or add a second filegroup later. Give it the moniker "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

I hope this helps you.

answered Sep 16 by narikkadan
• 20,880 points

Related Questions In Database

0 votes
0 answers

What datatype should be used for storing phone numbers in SQL Server 2005?

I need to save contact information in ...READ MORE

Sep 10 in Database by Kithuzzz
• 12,240 points
16 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
121 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
45 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
149 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
330 views
0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,380 points
650 views
0 votes
1 answer

Time Series Chart in Power BI

Your Date format should be handled by ...READ MORE

answered Nov 26, 2018 in Power BI by Upasana
• 8,620 points
3,608 views
0 votes
1 answer

Power BI Using SQL Server stored procedures

NOTE: The following works for the "Import ...READ MORE

answered Dec 14, 2018 in Power BI by Upasana
• 8,620 points
5,290 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
39 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
63 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP