Asking on Image Data Type

Question

Post reply

Asking on Image Data Type

snowfrost.c

SSC Enthusiast

Points: 136
More actions
May 15, 2014 at 12:27 am

#145016

Hi everyone,
I have an Image data type field in one of my table and is used to store my uploading of file. Can I confirm that the file is stored in the database as binary data and the file is not being stored somewhere in the SQL server ?
Thank You.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

snowfrost.c (5/15/2014)
Hi everyone,
stored in the database <---> stored somewhere in the SQL server ?

What's the difference?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 2

Quick test would be checking with the the datalength function, if it corresponds to the file size, it is in the table.

😎

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

snowfrost.c (5/15/2014)
Can I confirm that the file is stored in the database as binary data and the file is not being stored somewhere in the SQL server ?

I suppose that you want to know whether the file contents are stored as tabular data inside the database or in the file system.

If this is your question, the image data type is stored inside the database.

In SQL Server 2008 and newer you can add the filestream attribute to varbinary(max) columns to store the binary data in the filesystem and not inside the database tables.

-- Gianluca Sartori

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

spaghettidba (5/15/2014)
snowfrost.c (5/15/2014)
Can I confirm that the file is stored in the database as binary data and the file is not being stored somewhere in the SQL server ?
I suppose that you want to know whether the file contents are stored as tabular data inside the database or in the file system.
If this is your question, the image data type is stored inside the database.
In SQL Server 2008 and newer you can add the filestream attribute to varbinary(max) columns to store the binary data in the filesystem and not inside the database tables.

It is always stored in the database system (not in the file system), but not necessarily in the data pages themselves. If the contents are too large, the data row will just have a pointer to the pages where the actual string of bytes is stored.

You can control when the image is stored inside the data rows or not with sp_tableoption. (When it is bigger than the limit, it is not stored inside the actual data pages).

Using text and image Data

Anyhow, image is deprecated. Get rid of it 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Koen Verbeeck (5/15/2014)
Anyhow, image is deprecated. Get rid of it 😀

+1000

-- Gianluca Sartori

aframiller326 SSC-Addicted Points: 480 More actions · Answer 6

snowfrost.c (5/15/2014)
Hi everyone,
I have an Image data type[/url] field in one of my table and is used to store my uploading of image file[/url]. Can I confirm that the file is stored in the database as binary data and the file is not being stored somewhere in the SQL server ?
Thank You.

Check this,

http://stackoverflow.com/questions/10353098/sql-server-checking-the-datatype-of-a-column

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/eed0fc85-f1c2-452b-a882-64e2f031d070/how-to-get-datatype-a-column-in-sqlserver-

Hope I understand your question in the right way.