SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Size of image
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

saurav gautam
Starting Member

Nepal
15 Posts

Posted - 08/30/2011 :  10:26:52  Show Profile  Reply with Quote
In order to save pictures in sql server we can use datatype "IMAGE"... What is the maximum size limit of that data type. If it can be extended then what is the initial(default) size???

saurav123

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/30/2011 :  10:29:30  Show Profile  Reply with Quote
see
http://msdn.microsoft.com/en-us/library/ms187993.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/30/2011 :  10:30:08  Show Profile  Reply with Quote
as link suggests its deprecated. so use varbinary(max) instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

saurav gautam
Starting Member

Nepal
15 Posts

Posted - 08/30/2011 :  10:33:59  Show Profile  Reply with Quote
thank you for your quick reply. Actually i have to estimate the size of a record for my project.. I had no problem with other data types like 'NVARCHAR' with 30 characters length=30 bytes

What could be the best estimation for image?

saurav123
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/30/2011 :  10:42:48  Show Profile  Reply with Quote
are you asking on MAXSIZE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

saurav gautam
Starting Member

Nepal
15 Posts

Posted - 08/30/2011 :  10:48:19  Show Profile  Reply with Quote
Does sql server have FIXED LENGTH RECORD system??? Or does the size of record on which image is stored depends upon the size of that particular record?

saurav123
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/30/2011 :  10:49:43  Show Profile  Reply with Quote
if you're using varbinary size will be variable based on size of image

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

saurav gautam
Starting Member

Nepal
15 Posts

Posted - 08/30/2011 :  10:52:24  Show Profile  Reply with Quote
I am not using varbinary. I am using 'image' data type. Will my database be fixed length?

saurav123
Go to Top of Page

saurav gautam
Starting Member

Nepal
15 Posts

Posted - 08/30/2011 :  10:54:03  Show Profile  Reply with Quote
I just have to predict the size of database file where there are 50 images saved. What in your view would be the size?? Can it be predicted or depends upon the size of image..

saurav123
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/30/2011 :  10:58:05  Show Profile  Reply with Quote
it can be predicted only using max size of image

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 08/30/2011 :  11:13:43  Show Profile  Reply with Quote
The IMAGE datatype can hold 2^31-1 (2,147,483,647) Bytes. But, as mentioned, it only uses what it needs. So you can predict the size based on the size of the images you are storing. If you want to know the actual size of the image column in your database you can use the DATALENGTH function to tell the size of the column in Bytes. For example:
CREATE TABLE Foo (Val IMAGE)

INSERT Foo (Val) SELECT CAST(CAST(NEWID() AS VARBINARY) AS IMAGE)

SELECT DATALENGTH(Val) FROM Foo

DROP TABLE Foo
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/30/2011 :  12:19:13  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by saurav gautam

thank you for your quick reply. Actually i have to estimate the size of a record for my project.. I had no problem with other data types like 'NVARCHAR' with 30 characters length=30 bytes
...


Nope. Guess again.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Baldwin
Starting Member

USA
2 Posts

Posted - 03/25/2013 :  03:14:47  Show Profile  Reply with Quote
i cannot remember the exact maxmum data of image size, see here http://www.rasteredge.com/how-to/csharp-imaging/resize-image/ to get the image size in sql.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1645 Posts

Posted - 03/25/2013 :  18:22:45  Show Profile  Reply with Quote
Image is a VARIABLE sized data type with a max size of 2GB. The size of the image in the database depends on the size of the actual image. This is similar the variable length character data types, such as nvarchar. If your string is 30 characters then the database uses 30 characters of data space; if the string is shorter, it uses less space. The fixed length character data types, such a CHAR and NCHAR, do use the same amouint of space regardless of the length of the string. In your example a CHAR(30) column would use 30 characters to store an empty string.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000