Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>
|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
sql
Showing posts with label save. Show all posts
Showing posts with label save. Show all posts
Monday, March 26, 2012
Images and PDF's in the same DB or in another DB?
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertSame DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertSame DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Images and PDF's in the same DB or in another DB?
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB.<br>
<br>
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).<br>
<br>
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB.<br>
<br>
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).<br>
<br>
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
Friday, March 23, 2012
Image save to SQL server
When I am trying to save byte[] of an image to SQL server which is having an image column, I am getting an error like this " A severe error occured on the current command. The results, if any, should be discarded.". I am trying to save through a stored procedure.
In the sp also, the datatype is image.
In the sp also, the datatype is image.
Can you give a reply to this?Try this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240194
Image save into folder
Dear Professional,
Image saved in the table and I wanna copy that image into any folder let's
say... D:\Image\MineImage.gif
Is it possible ?
Thanks
Hi
You will have to write some VB or c# code to do that. SQL Server has no
native support to write out a BLOB filed to a file.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy" <roy@.hotmail.com> wrote in message
news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Dear Professional,
> Image saved in the table and I wanna copy that image into any folder let's
> say... D:\Image\MineImage.gif
> Is it possible ?
> Thanks
>
>
>
>
|||Okie I am ready to write code in VB... any website is available where I can
find that sort of help.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> You will have to write some VB or c# code to do that. SQL Server has no
> native support to write out a BLOB filed to a file.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Roy" <roy@.hotmail.com> wrote in message
> news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
let's
>
|||http://support.microsoft.com/default...b;en-us;194975
http://support.microsoft.com/kb/308042/EN-US/
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy" <roy@.hotmail.com> wrote in message
news:uj$FjV4cFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Okie I am ready to write code in VB... any website is available where I
> can
> find that sort of help.
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...
> let's
>
|||Roy wrote:
> Okie I am ready to write code in VB... any website is available where
> I can find that sort of help.
>
I prefer a book for this. For Vb.Net 2003, I'd recommend "Programming
Microsoft Visual Basic.Net 2003," by Francesco Balena (Microsoft Press).
You're about to get into some very large topics: The .Net 1.1 framework,
Windows Forms, and ADO.Net.
Also, check out http://www.devx.com/
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||here is the simple example for saving images into disk
'=================================
Dim rst As New ADODB.Recordset
Dim Image1() As Byte
Dim ms As New ADODB.Stream
Dim cmd As New ADODB.Command
Dim l As Long
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_getImage" '==== poc_code_look_below
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters("@.ID") = ID
Set rst = cmd.Execute
ms.Type = adTypeBinary
ms.Open
l = rst(0).ActualSize
Image1() = rst(0).GetChunk(l + 1)
rst.Close
ms.Write (Image1())
deletefile ("c:\img.jpg")
ms.SaveToFile "c:\img.jpg", adSaveCreateOverWrite
ms.Close
Set cmd = Nothing
Set rst = Nothing
Set ms = Nothing
'================================
create PROCEDURE proc_GetImage
(@.ID int)
AS
BEGIN
SELECT photo
FROM tbl_images
WHERE ([ID] = @.ID)
END
'========================
it is just an example, so it is up to you
Image saved in the table and I wanna copy that image into any folder let's
say... D:\Image\MineImage.gif
Is it possible ?
Thanks
Hi
You will have to write some VB or c# code to do that. SQL Server has no
native support to write out a BLOB filed to a file.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy" <roy@.hotmail.com> wrote in message
news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Dear Professional,
> Image saved in the table and I wanna copy that image into any folder let's
> say... D:\Image\MineImage.gif
> Is it possible ?
> Thanks
>
>
>
>
|||Okie I am ready to write code in VB... any website is available where I can
find that sort of help.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> You will have to write some VB or c# code to do that. SQL Server has no
> native support to write out a BLOB filed to a file.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Roy" <roy@.hotmail.com> wrote in message
> news:OnuaCw3cFHA.3932@.TK2MSFTNGP12.phx.gbl...
let's
>
|||http://support.microsoft.com/default...b;en-us;194975
http://support.microsoft.com/kb/308042/EN-US/
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy" <roy@.hotmail.com> wrote in message
news:uj$FjV4cFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Okie I am ready to write code in VB... any website is available where I
> can
> find that sort of help.
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:O0KTG33cFHA.1036@.tk2msftngp13.phx.gbl...
> let's
>
|||Roy wrote:
> Okie I am ready to write code in VB... any website is available where
> I can find that sort of help.
>
I prefer a book for this. For Vb.Net 2003, I'd recommend "Programming
Microsoft Visual Basic.Net 2003," by Francesco Balena (Microsoft Press).
You're about to get into some very large topics: The .Net 1.1 framework,
Windows Forms, and ADO.Net.
Also, check out http://www.devx.com/
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||here is the simple example for saving images into disk
'=================================
Dim rst As New ADODB.Recordset
Dim Image1() As Byte
Dim ms As New ADODB.Stream
Dim cmd As New ADODB.Command
Dim l As Long
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_getImage" '==== poc_code_look_below
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters("@.ID") = ID
Set rst = cmd.Execute
ms.Type = adTypeBinary
ms.Open
l = rst(0).ActualSize
Image1() = rst(0).GetChunk(l + 1)
rst.Close
ms.Write (Image1())
deletefile ("c:\img.jpg")
ms.SaveToFile "c:\img.jpg", adSaveCreateOverWrite
ms.Close
Set cmd = Nothing
Set rst = Nothing
Set ms = Nothing
'================================
create PROCEDURE proc_GetImage
(@.ID int)
AS
BEGIN
SELECT photo
FROM tbl_images
WHERE ([ID] = @.ID)
END
'========================
it is just an example, so it is up to you
image question
hello friends
I want to specify a field to save icons I want to give this field a maximum
of 120kb for each icon to be stored in the db.
what datatype should I assign to this field and how do I specify the size ?
Thanks
Tom"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> hello friends
> I want to specify a field to save icons I want to give this field a
> maximum
> of 120kb for each icon to be stored in the db.
> what datatype should I assign to this field and how do I specify the size
> ?
> Thanks
> Tom
The image data type is the one used for large binary data, but you can't
specify a size - 2GB is the maximum allowed. Another common approach is to
leave the binary files in the filesystem, and simply store the path to the
file in a varchar column.
Simon|||I considered that option.. however storing path is not the ideal option as
many users may have the same image or icon thus resulting the same image
being sent to all users when the image are suppose to be different as the
image gets overridden when they're the same file name
any other ideas ?
thanks
Tom
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:415175e2$1_2@.news.bluewin.ch...
> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> > hello friends
> > I want to specify a field to save icons I want to give this field a
> > maximum
> > of 120kb for each icon to be stored in the db.
> > what datatype should I assign to this field and how do I specify the
size
> > ?
> > Thanks
> > Tom
> The image data type is the one used for large binary data, but you can't
> specify a size - 2GB is the maximum allowed. Another common approach is to
> leave the binary files in the filesystem, and simply store the path to the
> file in a varchar column.
> Simon|||"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>I considered that option.. however storing path is not the ideal option as
> many users may have the same image or icon thus resulting the same image
> being sent to all users when the image are suppose to be different as the
> image gets overridden when they're the same file name
> any other ideas ?
> thanks
> Tom
<snip
Not really - those are pretty much the two options you have. Either use the
image data type and store the file in the database, or leave it in the
filesystem and store the path. It comes down to which approach is easier for
you, given your application and development environment.
Personally I'd go for the path, since then changing a user's icon just means
updating a single column, instead of loading the whole file into the
database. Also, any default or common icons only have to be stored once,
rather than maintaining a separate copy for each user.
You should also check out "Managing ntext, text, and image Data" in Books
Online, which covers the details of working with image data, and there's a
code sample for ADO, if that's what you're using.
Simon|||I had a similar problem where i wanted people to store files on my web
server, because i was worried about file name clashes i was considering
storing them in the database. Instead I came up with two ideas, one was to
put each users files into a subdirectory named after their user name, and
two the one that im using, was to first upload the file to the server, add a
database entry and return the result of the @.@.IDENTITY to my program then
preclude the name of the file with the identity value.
thus... myresume.doc would become 243_myresume.doc
Hope those help,
Muhd
"Tom" <tomgaomail@.optushome.com.au> wrote in message
news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>I considered that option.. however storing path is not the ideal option as
> many users may have the same image or icon thus resulting the same image
> being sent to all users when the image are suppose to be different as the
> image gets overridden when they're the same file name
> any other ideas ?
> thanks
> Tom
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:415175e2$1_2@.news.bluewin.ch...
>>
>> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
>> > hello friends
>>> > I want to specify a field to save icons I want to give this field a
>> > maximum
>> > of 120kb for each icon to be stored in the db.
>>> > what datatype should I assign to this field and how do I specify the
> size
>> > ?
>>> > Thanks
>> > Tom
>>>>
>> The image data type is the one used for large binary data, but you can't
>> specify a size - 2GB is the maximum allowed. Another common approach is
>> to
>> leave the binary files in the filesystem, and simply store the path to
>> the
>> file in a varchar column.
>>
>> Simon
>>
>>|||thanks thats a great idea
but I'm also curious would it be possible to store the icons into a binary
field and set the size of that binary field ?
Thanks
Tom
"Muhd" <eat@.joes.com> wrote in message
news:cRj4d.493991$gE.369174@.pd7tw3no...
> I had a similar problem where i wanted people to store files on my web
> server, because i was worried about file name clashes i was considering
> storing them in the database. Instead I came up with two ideas, one was
to
> put each users files into a subdirectory named after their user name, and
> two the one that im using, was to first upload the file to the server, add
a
> database entry and return the result of the @.@.IDENTITY to my program then
> preclude the name of the file with the identity value.
> thus... myresume.doc would become 243_myresume.doc
> Hope those help,
> Muhd
>
> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
> >I considered that option.. however storing path is not the ideal option
as
> > many users may have the same image or icon thus resulting the same image
> > being sent to all users when the image are suppose to be different as
the
> > image gets overridden when they're the same file name
> > any other ideas ?
> > thanks
> > Tom
> > "Simon Hayes" <sql@.hayes.ch> wrote in message
> > news:415175e2$1_2@.news.bluewin.ch...
> >>
> >> "Tom" <tomgaomail@.optushome.com.au> wrote in message
> >> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
> >> > hello friends
> >> >> > I want to specify a field to save icons I want to give this field a
> >> > maximum
> >> > of 120kb for each icon to be stored in the db.
> >> >> > what datatype should I assign to this field and how do I specify the
> > size
> >> > ?
> >> >> > Thanks
> >> > Tom
> >> >> >>
> >> The image data type is the one used for large binary data, but you
can't
> >> specify a size - 2GB is the maximum allowed. Another common approach is
> >> to
> >> leave the binary files in the filesystem, and simply store the path to
> >> the
> >> file in a varchar column.
> >>
> >> Simon
> >>
> >>|||Tom wrote:
> thanks thats a great idea
> but I'm also curious would it be possible to store the icons into a binary
> field and set the size of that binary field ?
> Thanks
> Tom
NO, you can't specify the size of an image columt - it's variable up to 2 GB.
Here are all the types MS SQL has:
http://www.databasejournal.com/feat...le.phpr/2212141
WYGL,
Andrey|||
As has already been stated, you can't 'set' the size of field, but since your code would load an image
into the field, it could check that the size is within limits before loading it.
--
__________________________________________________ _____
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous
"Tom" <tomgaomail@.optushome.com.au> wrote:
>thanks thats a great idea
>but I'm also curious would it be possible to store the icons into a binary
>field and set the size of that binary field ?
>Thanks
>Tom
>"Muhd" <eat@.joes.com> wrote in message
>news:cRj4d.493991$gE.369174@.pd7tw3no...
>> I had a similar problem where i wanted people to store files on my web
>> server, because i was worried about file name clashes i was considering
>> storing them in the database. Instead I came up with two ideas, one was
>to
>> put each users files into a subdirectory named after their user name, and
>> two the one that im using, was to first upload the file to the server, add
>a
>> database entry and return the result of the @.@.IDENTITY to my program then
>> preclude the name of the file with the identity value.
>>
>> thus... myresume.doc would become 243_myresume.doc
>>
>> Hope those help,
>> Muhd
>>
>>
>> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> news:41519884$0$20125$afc38c87@.news.optusnet.com.a u...
>> >I considered that option.. however storing path is not the ideal option
>as
>> > many users may have the same image or icon thus resulting the same image
>> > being sent to all users when the image are suppose to be different as
>the
>> > image gets overridden when they're the same file name
>>> > any other ideas ?
>> > thanks
>> > Tom
>>> > "Simon Hayes" <sql@.hayes.ch> wrote in message
>> > news:415175e2$1_2@.news.bluewin.ch...
>> >>
>> >> "Tom" <tomgaomail@.optushome.com.au> wrote in message
>> >> news:41512fbd$0$23894$afc38c87@.news.optusnet.com.a u...
>> >> > hello friends
>> >>> >> > I want to specify a field to save icons I want to give this field a
>> >> > maximum
>> >> > of 120kb for each icon to be stored in the db.
>> >>> >> > what datatype should I assign to this field and how do I specify the
>> > size
>> >> > ?
>> >>> >> > Thanks
>> >> > Tom
>> >>> >>> >>
>> >> The image data type is the one used for large binary data, but you
>can't
>> >> specify a size - 2GB is the maximum allowed. Another common approach is
>> >> to
>> >> leave the binary files in the filesystem, and simply store the path to
>> >> the
>> >> file in a varchar column.
>> >>
>> >> Simon
>> >>
>> >>
>>>>
>>
Wednesday, March 21, 2012
Image file save to DB
How to save image file to database?
Which control use best?
hy..
seaching on google might be beneficial :)
Also.. U can check these articles -->
Creating and Saving Images to DataBase
Saving Images in SQL (1)
Saving Images in SQL (2)
![Yes [Y]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tBgqF1tWJEY37WtMo-6VTsJOdyUkfscnHTK4bRhY_R8fHfxtojsuJCyhdZ1dCRaYtmaa79NnjfrPOjC0j3QU4FDvJdOXcp06Ilh3QMxCVzJTCg3HJZIMI=s0-d)
Which control use best?
hy..
seaching on google might be beneficial :)
Also.. U can check these articles -->
Creating and Saving Images to DataBase
Saving Images in SQL (1)
Saving Images in SQL (2)
Monday, March 19, 2012
Image and tables
Is it possible to save jpgs in a table if the DataType is set to varbinary(MAX), also how would I save the images to the table.
Thanks
Hi,
yes that possible, but depends on the used coding language, would be nice if you could provide some more information about that.
-Jens Suessmeyer.
http://www.sqlserver2005.de
Hi,
sorry for picking up your issue this late, but sometime posts get out of sight in my mailbox and the weekend is preferable my time to pick them all up. Have a look at some sample code here:
http://www.freevbcode.com/ShowCode.Asp?ID=1802
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)