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, 2022 in Database by Kithuzzz
• 38,020 points
1,012 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, 2022 by narikkadan
• 63,720 points

Related Questions In Database

0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
777 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, 2022 in Database by Vaani
• 7,070 points
479 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, 2022 in Database by Neha
• 9,060 points
1,943 views
0 votes
1 answer

SQL Server replaces LEFT JOIN for LEFT OUTER JOIN in view query

You are getting the joins confused and ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
1,430 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
756 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,370 points
1,164 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
4,370 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
6,218 views
0 votes
1 answer

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

Does this include: International numbers? Extensions? Other information besides the ...READ MORE

answered Sep 11, 2022 in Database by narikkadan
• 63,720 points
1,625 views
0 votes
1 answer

Use of contains() in sql server

The straightforward method is shown here. You ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,720 points
885 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