Pros and cons of using image data type

Can anybody let me know the pros and cons of storing
images using SQL server image data type as opposed to just
storing the image file in system file directories. I'm
more concern on the efficiency and the space used if I
store images using SQL server 2000. Anything bad that
might occur?That's a sensible question to ask ... And many out there forget when they
use image datatypes ... BTW, Iam not a against image datatype but I always
prefer in storing them in the web server itself rather than passing them
around to and forth to the SQL Server. Moreover storing large files at the
sql server is also not advisable ... But if you are storing just a small
signature image for example .. It is fine to use SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?|||The Pros of storing the images in SQL Server are pretty simple: You gain
all the "ACID" properties of a database system. The images are backed up
with the data in a coordinated fashion. All "pointers" between your
structured data and the images are maintained. No "broken link" problems,
no out of sync problems, no multiple namespaces, etc. If you store the data
in the database and the images in the file system then you have multiple
backups and various ways for the database and image information to end up
out of sync. Plus, you have to manage two different security environments.
The big negative of storing images in SQL Server is performance. There are
three issues here. The first is that SQL Server breaks images up into
chunks that fit on database pages. This makes reassembling the images
slower than if they are stored, without additional internal structure, in a
file. It also makes it impossible to use the operating system's built-in
facilities to transmit a file directly from disk out over a communications
link in kernel mode. So, from the standpoint of serving the image out onto
the web it is definitely much slower. Second, images stored in SQL Server
are returned to the application via the TDS protocol and the data access
APIs. Again, these are not optimal for image processing and impose overhead
that doesn't exist with a file. Third, most applications that process
images read and write them from the file system. So, if the image is stored
in SQL Server then you have to read the image out of the server, write it to
a temporary file, then invoke the image processing software against the
temporary file. It is these performance issues that lead many people to
store the images outside the database itself.
The real suitability of storing images inside SQL Server versus in the file
system comes down to the analysis of the application itself. For example,
if you have an HR application and one of the pieces of information that you
store about an employee is the picture that is on their id card then I think
you should store that image inside the database. Why? Well, you don't
access it very often. You aren't serving it up to the web constantly. It's
always accessed in conjunction with other employee data. You need to
protect access to the images under your HR policies. The performance hit is
thus not a significant factor when compared with the application and
operational issues.
If I had a server whose primary function was to serve up images all day,
then I'd store them in the file system. Or, at least maybe I would. I
actually have a preferred architecture for this scenario that addresses both
the management and performance issues. But it cost disk space. I would
store the authoritative copies of the images in the database, and then
create copies (a cache) in the file system. I would serve up the images
from the file system, but my recovery procedures would blast the
authoritative copies from the database into the filesystem. So I get the
best of both worlds. And with 120GB disks going for less money than I
usually carry around in my wallet, the duplication hardly seems to be a
problem for most situations.
One system that runs counter to conventional wisdom is the TerraServer
(http://terraserver-usa.com/). You can get details of how it works from
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29.
Even though it serves up images all day long the images are stored in SQL
Server. This was done for a few reasons, chief among them to show that SQL
Server was capable of hosting such an application. Terraserver has been
operational since the summer of 1998, at times serving up several million
images per day.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?

Programatically viewing the data in a particular column

This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.

My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column... One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization. I need to be able to read this value so I can assign it to the Page.

'First Do a Database Connection

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsString = _

ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString

'Create a SqlConnection instance

Using myConnectionAsNew SqlConnection(connString)

'Specify the SQL query

Const sqlAsString ="SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "

'Create a SqlCommand instance

Dim myCommandAsNew SqlCommand(sql, myConnection)

'Get back a DataSet

Dim myDataSetAsNew DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet,"SEO")

'Create a DataView

Dim myDataViewAsNew DataView

myDataView.Table = myDataSet.Tables("SEO")

'Ok...this is where I'm lost. Now how do I look up the value for the column "Title" in this Dataset

'Close the connection

myConnection.Close()

'End database connections

EndUsing

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

To access other columns, simply replace the column name ("Title") with a respective column name

Alternatively you can use the following line to access the value inside the1st row of the coloumn
Dim strAsString =CStr(myDataSet.Tables("SEO").Rows(0).Item("Title"))

To access other rows, simply replace the row number (0) with a respective row number

Hope this helps you.

|||

I'm relatively new too.

Instead of writing the entire class as 'codeaholic' did (looks daunting), can I use the drag'n'drop methodology to place a SqlDataSource1 control onto an ASPX page and configure the datasource with an appropriate Select statement, (easy part) and then use less VB code to find the value of a column for a given record?

In other words, if I want to extract a single column of a record do I have to 'throw away' all those luxury features of design time

|||

i'm personally not familier with using data controls in my pages as im currently using either 2-tier or 3-tier application development, but it's possible to do what you want.
just create the data controls one-by-one till you get a datagrid or dataview object containing the values you wanted, then use code-behind to access the datagrid or dataview objects for your values.
or you could even directly bind your values to controls using the datasource property and other respective properties.
however it may get tedious when you need a large number of data objects to access different values in different tables.

i would also advise strongly against doing so as it would affect the performance of your pages due to unneeded controls needed to be complied and loaded.
and there's always the security issue.
there's always better ways to do it.
hope this helps you.

|||

AnjinG:

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

Hmm, just getting around to trying this and it doesn't work. Using this code above. myColumnValue.toString is simply the word "Title". Its not actually extracting the value of the column Title but simply setting this to the name of the column...

|||

Hi,

you can try doing this to directly access the particular column value
you willl need to traverse the rows in the table

Dim i as integer

For i = 0To myDataSet.Tables("SEO").Rows.Count - 1

Dim vAsString = myDataSet.Tables("SEO").Rows(i).Item(columnIndex)

Next

|||Yeh, I just used the row syntax you indicated as the "second" possibility. Since there is only one row, we don't have to loop it. Thanks.