Putting image files in sql server 2005

Question

Putting image files in sql server 2005

priest Ivan Delic

SSC Eights!

Points: 942
More actions
October 6, 2007 at 2:28 am

#175789

Hi all,
I posted to this forum recently, and I have one more question.
I worked for 3 years in a church library, and used MS ACCESS to maintine my data of books, members, loans etc. In one db I made a field called MemberImages (datatype was OLE) and inserted some images of members).
Now I am working in a simple publishing house, but with more complexed data, so I turned to sql server 2005 express ed. I nwant to put some photos of our employess in SQL database. I heard that I can use image data type for this, but I don't know how.
Please help me out

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

It's probably a good thing you don't know how because my recommendation is not to store images in SQL... store them in files and store the file names in SQL. Makes life real easy when you want to change the content of a file...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 2

At first thanks for reply.

Let's suppose that I have file named Tex1.doc with all images that I need in it. How to put that file in SQL? Through Visual Studio, or?

Sincerelly,

Ivan Delic

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

You'd need to split out the images into separate files...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

You'll usually find people split on this issue. I tend to agree with Jeff, but lots of people like having the images/BLOBs in SQL Server. Everything is then in once nice backup package, easy to manage.

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 5

Thanks for the quick replies.

In MSAccess that I used, I imported images files from local hard. Images files are called, for example:

Image01.jpg, Image02.jpg, Image03.jpg.

As you can see, these are three separate files.

I found some VS Project for importing images in Access database, and when I deployed a project I added five images in db. Then I used upsize wizard to export that db to my SQL Server, and on server I got db with table dbo.images, and five records on it. Data type was image, but in records it stands what got me confused. I saw something like that in Adventure Works Samples. My questions are:

Is it possible to view those images after deploying sql db into VS? And is it possible to view images in SQL Server?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Dunno about your first question but I'm pretty sure that the answer to your second question of can you "view images in SQL Server" is an emphatic "NO".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Using a visual tool (allowing for rendering) like VS would allow you to view data (including images) stored in SQL. That being said - in a lot of cases - it's really NOT a great idea to store images in SQL. It tends to be a LOT more efficient to store link info in SQL, and organize them on the OS (preferrably on a different server or drive from SQL anyway).

Your SQL server in most cases would be busy enough not to be the best tool to store and serve up little bobby's MP3 collection. I'm being a little facetious here - but you could really slow your other processing down with that nonsense. Have SQL server store data it can do something with - have the OS hold the big files/images/etc....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 8

priest Ivan Delic

SSC Eights!

Points: 942

October 10, 2007 at 2:42 am

#740666

Thanks a lot, you gave me a solution for my problem.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 9

One other consideration is that Ivan said one critical word: Express.

I'd be concerend about the amount of space taken up by the blobs. Ivan you said photos of the employee's and so you might not have that many. Then someone will want you to store photos of the products and then scanned images of sales receipts. It's a slippery slope.

ATBCharles Kincaid

EdVassie SSC Guru Points: 60476 More actions · Answer 10

Download the linked research paper, as it covers your situation. In a few words, it is saying any file under 256 KB is handeled more efficiently in SQL than in the file system, while anything over 1MB is handled more efficiently in the file system, and how to work out what is best for intermediate-sized files.

The paper makes clear it is not covering issues such as synchronising the file system with SQL meta data, or backup and restore issues (such as the jagged edge on recovery) that might make SQL storage more manageble.

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

(Thanks go to Simon Sabin for making me aware of this paper.)

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 11

priest Ivan Delic

SSC Eights!

Points: 942

October 13, 2007 at 1:31 am

#741779

Thanks and really thanks a lot