Impact of empty tables on database size and performance

Hello All,

When creating my database I have modeled some of the tables after the Adventureworks sample database.

There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).

In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.

Thanks for your help!

Blair:

There will be little impact from tables that are not used provided these tables are small and do not grow. If the tables are large then they can impact your storage requirements and the size and speed of your backups.

Fields that are not used are a different story -- especially if the fields are populated with large amounts of "spectator" data. These "spectator" fields will then impact the amount of space required for their tables and will have an impact on the amount of time required for a "table scan". This becomes amplified to some extent if these fields participate in any indexes. If your fields are all null the amount of bloating will not be as great.

Another problem that can occur if you leave in fields that have at the moment no use is that in the future it can become tempting for somebody to start "using" these fields in ways that are not planned. Eliminating unused fields can stop this kind of "cobbling" before it takes place.

I would suggest that you are better off with a well designed, well thought out database in which each column has a specific meaning with a specific intended use.

Images in SQL database or filesystem

We are wondering which storage type to choose. The number of images will be
about 30 million and the size of one image will be about 30kb.
What are the advantages and divantages of the two storage areas?
Is there anyone who can help me with his expertise in this area?
Please no answers like: On my filesystem everythings works great.I prefer to store images on the file system, but I know that there are
others that don't share my view. In any case there are advantages and
divantages for each method.
The advantages of storing the BLOB in the database are:
1) You don't need to worry about synchronizing the data in the DB and
the images.
2) When you backup the database you already backup the images.
3) You can manage permissions in the database only.
The divantages of storing the files in the database are:
1) Backup and restore time for the database will increase
significantly.
2) Writing the code to handle the images will be harder.
3) When you work with image data type you'll might have performance
problem (comparing to storing the images on the file system). I have
to admit that I only read it and never tested it.
Adi|||Hi
You will probably get a few replies saying "Put it on the File System".
The issue with that is:
1. How do you keep the DB and FS in sync so during a restore, you don't
loose links? With difficulty.
2. All file systems start have performance problems if you have too many
files in one directory. 30 Million files does result in a very complicated
structure to keep the number of files at a manageable level (10'000 or less,
mileage may vary)
3. You have permissioning issues. If you want to read/write to the files,
the people need read/write access to the files. Files can get modified and
deleted very easily. You control the DB, and once written, you can stop
modifications.
I am in favor of DB storage.
Regards
Mike
"morerice" wrote:

> We are wondering which storage type to choose. The number of images will b
e
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>|||My rule of thumb (but rule are meant to be broken) is that if you have a
large number of small files, it is better to store them in the database
because of synchronization issues and file system problems managing a large
number of files.
In your case though, you say that you have them in a fileSystem and
everything works great.
I don't know how things will evolve in you situation (file modifications, 40
million files next year, etc.), but unless you have a very good reason to
change a good working system... don't.
"morerice" <morerice@.discussions.microsoft.com> wrote in message
news:FB2F0C09-5E26-4996-8022-CFBC57D3960B@.microsoft.com...
> We are wondering which storage type to choose. The number of images will
be
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>|||Morice,
It sounds like you will have a headache coming one way or the other. My
experience working with blobs is that they stink. Database size tend grow ou
t
of control, online backup start to become expensive if you pay for storage
like we do and maintenance on the database get longer and longer. Here's a
link to an article that describe a problem you will encounter one day.
http://support.microsoft.com/?id=324432
My point is this: DO YOUR HOMEWORK and communicate, communicate communicate
before you embark on putting the images in the database.
Joseph
"morerice" wrote:

> We are wondering which storage type to choose. The number of images will b
e
> about 30 million and the size of one image will be about 30kb.
> What are the advantages and divantages of the two storage areas?
> Is there anyone who can help me with his expertise in this area?
> Please no answers like: On my filesystem everythings works great.
>

Image size while export

Hello,

I'm using Reporting Services 2005.

I have image that data value set to take the image from a web server.

I also set the sizing to "AutoSize".

In the report while the image size is more than I set in the design mode the report, the image grow and all other object in the report move.

but while I export to the excel the image size in the excel is the same size I set in the design mode.

While I export to PDF the image grow but all other objects are not moving and the image go up on other object.

can you help me ?

How are you positioning this image? Is this a single image or multiple images in a data table/matrix?|||Single image|||

I'm not sure why this is happening but I suspect it's down to the implementation of each Rendering Extension. You see each output format (HTML, XLS, PDF etc...) is programmed separately and it sounds like they treat image rendering differently.

Considering it's a single image and assuming that this image doesn't change size, the best advice I can give you is to check the dimensions of the image and to set them explicitly within the report instead of using the Auto option. That way you'll also have a better feel for what effect the image has on the rest of the report at design time.

Also try putting the image in the header or the footer to prevent it from affecting other report items in the body of the report.

Sorry I couldn't be of more help.

Image size : 2Gb per row or per column?

Does the limit on an image column mean that I can put up to 2Gb in each row,
or can I only put a TOTAL of 2Gb of image data into a table.
It is 2gb for each row.
hth
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
> Does the limit on an image column mean that I can put up to 2Gb in each
> row,
> or can I only put a TOTAL of 2Gb of image data into a table.
|||Thanks!
"Vikram Vamshi" wrote:

> It is 2gb for each row.
> hth
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
> news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
>
>

Image size : 2Gb per row or per column?

Does the limit on an image column mean that I can put up to 2Gb in each row
,
or can I only put a TOTAL of 2Gb of image data into a table.It is 2gb for each row.
hth
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
> Does the limit on an image column mean that I can put up to 2Gb in each
> row,
> or can I only put a TOTAL of 2Gb of image data into a table.|||Thanks!
"Vikram Vamshi" wrote:

> It is 2gb for each row.
> hth
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
> news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
>
>

Image size : 2Gb per row or per column?

Does the limit on an image column mean that I can put up to 2Gb in each row,
or can I only put a TOTAL of 2Gb of image data into a table.It is 2gb for each row.
hth
--
Vikram Vamshi
Database Engineer
Eclipsys Corporation
"Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
> Does the limit on an image column mean that I can put up to 2Gb in each
> row,
> or can I only put a TOTAL of 2Gb of image data into a table.|||Thanks!
"Vikram Vamshi" wrote:
> It is 2gb for each row.
> hth
> --
> Vikram Vamshi
> Database Engineer
> Eclipsys Corporation
> "Jonny D" <Jonny D@.discussions.microsoft.com> wrote in message
> news:8B843B3C-CE55-43C6-BA1D-D496C3BDE459@.microsoft.com...
> > Does the limit on an image column mean that I can put up to 2Gb in each
> > row,
> > or can I only put a TOTAL of 2Gb of image data into a table.
>
>sql

image size

Hello

I added a background jpg file into my report. It seems the image is bigger then my page size, is there any way I can say fit the image size to page size?

Sounds like your image is huge! Why not make a smaller image and do a repeating background?

Background Image Repeat

You can have a background image repeat vertically (y), horizontally (x), in both directions, or in neither direction.

CSS Code:

p {
background-image: url(smallPic.jpg);
background-repeat: repeat; }
p {
background-image: url(smallPic.jpg);
background-repeat: repeat-y; }
p {
background-image: url(smallPic.jpg);
background-repeat: repeat-x; }
p {
background-image: url(smallPic.jpg);
background-repeat: no-repeat; }

Image size

I have a SQL database that stores image files (pdf, doc, tif etc). Is it
possible to write a query that shows the size of the file being stored ?
Any help would be great.
Thanks
SiYou can get the bytes used with DATALENGTH -
SELECT DATALENGTH(YourImageColumn)
FROM YourTable
You can find more information on DATALENGTH in Books Online.
-Sue
On Mon, 31 Jul 2006 08:36:01 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:
>I have a SQL database that stores image files (pdf, doc, tif etc). Is it
>possible to write a query that shows the size of the file being stored ?
>Any help would be great.
>Thanks
>Si

Image size

I have a SQL database that stores image files (pdf, doc, tif etc). Is it
possible to write a query that shows the size of the file being stored ?
Any help would be great.
Thanks
SiYou can get the bytes used with DATALENGTH -
SELECT DATALENGTH(YourImageColumn)
FROM YourTable
You can find more information on DATALENGTH in Books Online.
-Sue
On Mon, 31 Jul 2006 08:36:01 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:

>I have a SQL database that stores image files (pdf, doc, tif etc). Is it
>possible to write a query that shows the size of the file being stored ?
>Any help would be great.
>Thanks
>Si

Image Retrieving from database

hi,

I have a SQL database that has Images stored. I am able to read the image from the database and display it. But i am not able to control the size and the position where it is displayed. I want to develop something like a photo album. the images should be displayed in a smaller size. but when the user clicks on the image, it should blow up to its original size.
I am struggling with this for some time. Any suggestions as how to develop it??.
Any help in this regard would be highly appreciated.

thanks,
-sriramCreate thumbnails of the images when they're uploaded.|||Or when they are retrieved.|||You could try streaming the file and resizing it on the fly with GDI+

or using HTTP Handlers:

a good article is at
http://www.c-sharpcorner.com/Code/2003/June/HTTPHandlersForImages.asp

alternatively:

the following code allows an .aspx file to receive parameters (file name and width of picture) and return a .jpg so that in you HTML code you can use access an image like this:

src="SmartPicture.aspx?f=yourpic.jpg&w=50"

and the file yourpic.jpg will be displayed with a width of 50 pixels and a calculated proportional height. The file size will also be reduced - ie: if your original picture was 4Mb, it would be scaled down to around 15kb.

code as follows:

save it as SmartPicture.aspx

<%@. Page Language="C#" %>
<%@. Import Namespace="System.Drawing" %>
<%@. Import Namespace="System.Drawing.Imaging" %>
<script runat="server"
 void Page_Load(Object s, EventArgs e) {
 double photoWidth,photoHeight;
 double percentageDifference = 0;
 bool heHasAccess = false;
 System.Drawing.Image inputImage;
 //get information being sent
 heHasAccess = true;
 //get file name
 string pictureFileName = Request.QueryString["f"];
 if(pictureFileName == null || pictureFileName == "") {
 heHasAccess = false;
 }
 //get width
 try {
 if (Request.QueryString["w"] == null) {
 photoWidth = 0;
 } else {
 photoWidth = Int32.Parse(Request.QueryString["w"]);
 }
 }
 catch {
 photoWidth = 0;
 }
 //if anything went wrong, show error picture
 if(!heHasAccess) {
 inputImage = System.Drawing.Image.FromFile(Server.MapPath("images/pictureNoAccess.jpg"));
 } else {
 inputImage = System.Drawing.Image.FromFile(Server.MapPath("images/" + pictureFileName));
 }
 //if no width was given, assume the default now
 if(photoWidth==0) {
 if(!heHasAccess) {
 photoWidth = 100;
 } else {
 photoWidth = inputImage.Width;
 }
 }
 //define size for new image
 percentageDifference = inputImage.Width / photoWidth;
 photoHeight = inputImage.Height / percentageDifference;
 //output new image with different size
 Bitmap outputBitMap = new Bitmap(inputImage,Convert.ToInt32(photoWidth),Convert.ToInt32(photoHeight));
 Response.ContentType = "image/jpeg";
 outputBitMap.Save(Response.OutputStream, ImageFormat.Jpeg);
 }
</script>

|||This works fine.

But i have the image in a SQL database. I have image in a binary format.

So i am using the Response.BinaryWrite method.

But i have some other infromation also to be dispalyed with the image. For example some descrption and other deatils. But the image gets displayed at the top left corner hiding all other literals and labeles.

Any suggestions please.

thanks,
-sriram|||Could you post the code you are using?|||On button click: I am retreiving an image name :Test1 from my Sql Server database.
I am posting the code:

Dim imgid As String = Request.QueryString("Test1")
Dim conn As String = "data source=ERB112SERV;initial catalog=TEST;persist security info=False;user id=artak;password=po4d8dor1ju;workstation id=ERB112SERV;packet size=4096"

Dim myConnection As New SqlConnection("data source=ERB112SERV;initial catalog=TEST;persist security info=False;user id=artak;password=po4d8dor1ju;workstation id=ERB112SERV;packet size=4096")

Dim sql As String = "SELECT imgdata, imgtype FROM ImageStore WHERE imgtitle = 'Test1'"
Dim command As SqlCommand = New SqlCommand(sql, myConnection)
myConnection.Open()
Dim dr As SqlDataReader = Command.ExecuteReader()
If (dr.Read()) Then

Response.ContentType = dr("imgtype").ToString()
Response.BinaryWrite(dr.Item("imgdata"))

End If
myConnection.Close()

Thanks for mailing.

-sriram|||But the image gets displayed at the top left corner hiding all other literals and labeles.

Sounds like you're mixing absolute, and relative positioning, no?

If thatis the case, then you're going to have to either specify the positioning for the image, or go to all relative positioning (flow layout) for the controls involved.|||You have to assing the image to img tag, then you could display it where ever you want!

Below example might helps you to understand it!

Live example
http://www.w3coder.com/contest/examples/showipinimage.aspx

and code for it at
http://www.w3coder.com/contest/examples/ImageIPonaspxpage.zip

Hope these helps!|||hi,

Sorry. I didnt quiet get it. I am not even able to assign the image from a database.

So i am not able to write the output from a database to an Image.

I am not able to get the idea of assigning relative positioning.

can you help me with a sample codr or so so that i can understand it well.

thanks,
-sriram|||Would you have code for saving an image to a database?|||I don't understand your question.|||

can anyone pls enlighten my on to retrieve the image file from MS Access?

what is the syntax and the way of approving and maybe a sample code.. im a noob... thanks

sql

Image data type Size

I need to store images in MS SQL. I have the upload procedures and stuff but I'm missing the point about the image data type size.

It is supposed to be able to store up to 2Gb!!! but when I declare the data field image I can't specify the max size for the field and by default is 16 !!

16 bytes!! what can I do with that?
How can I insert a file?

Please helpThe pointer to the data is the only thing stored in this database row. The data is stored elsewhere and can be up to 2G. You don't get to specify the size.

Bill|||Thanks for the reply!

But that leads me to this question: How can I write the Insert Statement for the image field?

create table ImgTest (
imgID Int,
imgFName varchar(30),
imgFType varchar(30),
imgFile image
)

All I got so far is a procedure in vb net that tryes to insert a byte array asigned to parameter value:

Insert Into ImgTest Values (1, 'file.jpg', 'image file', @.bytearray)

That's where I'm having trouble because it returns the error: "Data will be truncated" or something like that. I guess it's because it tryes to store the file in the 16bytes that are reserved for the pointer...

Oh.. I'm so lost..

Thank you!sql

Image files and SQL Server

Hello,
What's the best way to store files of an arbitrary size in a SQL
Server / MSDE database? Some files will be text, but most should be
binary (images, audio, and video). I'd like to be able to store all
the files directly in the database so that backing everything up is a
single operation (I can do this, right?). Data retrieval speed is not
overly critical, as the code in question will be running on the same
machine as the database and will only have a single instance of the
client application. Any advice and/or hyperlinks to whitepapers on the
subject would be appreciated.

Thanks,
Will Gant
foehammer@.hotmail.comHi

Check out:
http://www.aspfaq.com/show.asp?id=2149

http://msdn.microsoft.com/library/d...amples_2isp.asp
http://msdn.microsoft.com/library/d...asp?frame=true

John

"Foehammer" <foehammer@.hotmail.com> wrote in message
news:27d68359.0407051151.2adb94ea@.posting.google.c om...
> Hello,
> What's the best way to store files of an arbitrary size in a SQL
> Server / MSDE database? Some files will be text, but most should be
> binary (images, audio, and video). I'd like to be able to store all
> the files directly in the database so that backing everything up is a
> single operation (I can do this, right?). Data retrieval speed is not
> overly critical, as the code in question will be running on the same
> machine as the database and will only have a single instance of the
> client application. Any advice and/or hyperlinks to whitepapers on the
> subject would be appreciated.
> Thanks,
> Will Gant
> foehammer@.hotmail.com|||Well,
I guess the 2 gig limit on MSDE file size is going to be a bit of a
problem. It'll add some complications, but I think I've come up with a
slightly smarter way to do this than in my orginal design.

Thanks,
Will Gant
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VQiGc.303$UV3.4418796@.news-text.cableinet.net>...
> Hi
> Check out:
> http://www.aspfaq.com/show.asp?id=2149
> http://msdn.microsoft.com/library/d...amples_2isp.asp
> http://msdn.microsoft.com/library/d...asp?frame=true
> John
> "Foehammer" <foehammer@.hotmail.com> wrote in message
> news:27d68359.0407051151.2adb94ea@.posting.google.c om...
> > Hello,
> > What's the best way to store files of an arbitrary size in a SQL
> > Server / MSDE database? Some files will be text, but most should be
> > binary (images, audio, and video). I'd like to be able to store all
> > the files directly in the database so that backing everything up is a
> > single operation (I can do this, right?). Data retrieval speed is not
> > overly critical, as the code in question will be running on the same
> > machine as the database and will only have a single instance of the
> > client application. Any advice and/or hyperlinks to whitepapers on the
> > subject would be appreciated.
> > Thanks,
> > Will Gant
> > foehammer@.hotmail.com

Image data type Size

I need to store images in MS SQL. I have the upload procedures and stuff but I'm missing the point about the image data type size.

It is supposed to be able to store up to 2Gb!!! but when I declare the data field image I can't specify the max size for the field and by default is 16 !!

16 bytes!! what can I do with that?
How can I insert a file?

Please helpThe pointer to the data is the only thing stored in this database row. The data is stored elsewhere and can be up to 2G. You don't get to specify the size.

Bill|||Thanks for the reply!

But that leads me to this question: How can I write the Insert Statement for the image field?

create table ImgTest (
imgID Int,
imgFName varchar(30),
imgFType varchar(30),
imgFile image
)

All I got so far is a procedure in vb net that tryes to insert a byte array asigned to parameter value:

Insert Into ImgTest Values (1, 'file.jpg', 'image file', @.bytearray)

That's where I'm having trouble because it returns the error: "Data will be truncated" or something like that. I guess it's because it tryes to store the file in the 16bytes that are reserved for the pointer...

Oh.. I'm so lost..

Thank you!sql