Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Size of image

Author  Topic 

saurav gautam
Starting Member

15 Posts

Posted - 2011-08-30 : 10:26:52
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

52326 Posts

Posted - 2011-08-30 : 10:29:30
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

52326 Posts

Posted - 2011-08-30 : 10:30:08
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

15 Posts

Posted - 2011-08-30 : 10:33:59
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

52326 Posts

Posted - 2011-08-30 : 10:42:48
are you asking on MAXSIZE?

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

Go to Top of Page

saurav gautam
Starting Member

15 Posts

Posted - 2011-08-30 : 10:48:19
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

52326 Posts

Posted - 2011-08-30 : 10:49:43
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

15 Posts

Posted - 2011-08-30 : 10:52:24
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

15 Posts

Posted - 2011-08-30 : 10:54:03
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

52326 Posts

Posted - 2011-08-30 : 10:58:05
it can be predicted only using max size of image

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

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-30 : 11:13:43
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 12:19:13
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

2 Posts

Posted - 2013-03-25 : 03:14:47
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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-03-25 : 18:22:45
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
   

- Advertisement -