Wednesday 30 November 2016

IMAGE VS VARBINARY(MAX)

What are the differences between IMAGE and VARBINAY(MAX) [IMAGE VS VARBINARY(MAX)]
Which data type should use for defining a column in a table [IMAGE VS VARBINARY(MAX)]?


IMAGE
VARBINARY(MAX)

Bytes
It consumes 1 byte per character.
It consumes 1 byte per character.
It is introduced in SQL Server 2005 .

Data Length
Maximum storage size is (2147483647) 2^31-1 bytes (2 GB).
MAX indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.

Data
Storage
Type
IMAGE is used to only used to store the image files (BMP, TIFF, GIF, or JPEG format files).
VARBINARY(MAX) data type is used to store images/pdf/word etc files and any data.

Index
Index can’t be created on IMAGE data type if needed. It throws the exception error when we create an index on this data type.
Index can’t be created on VARBINARY (MAX)  data type even though it is needed. It throws the exception error when we create an index on this data type.

Use When?
Use IMAGE data type when only used to store the image files.

NOTE:
Microsoft is suggesting to use VARBINARY(MAX)  instead of  IMAGE data type for storing the large amount of data in a single column as  it will be removed in a future versions of MS SQL Server.

Can’t cast/Convert  to IMAGE data type and vise versa.





Example:


DECLARE @VarChar VARCHAR(100) = '';
DECLARE @Cnt INT = 1

WHILE @Cnt < 100
BEGIN
          SET @VarChar = @VarChar + 'A'
          SET @Cnt = @Cnt + 1
END
SET @VarChar = @VarChar + 'B'
SELECT @VarChar
SELECT CONVERT(VARCHAR(100),CONVERT(IMAGE, @VarChar));

RESULT:  ERROR
Msg 529, Level 16, State 2, Line 11
Explicit conversion from data type image to varchar is not allowed.
VARBINARY(N)

When N is not specified in a data definition or variable declaration statement, the default length is 1.

When N is not specified with the CAST function, the default length is 30.

Use VARBINARY(MAX) when the column data entries exceed 8,000 bytes.

VARBINARY(MAX) data type is used to store images/pdf/word etc files and  any data.


Example:

DECLARE @VarBinary VARBINARY(MAX);

SET @VarBinary = 123456; 
SET @VarBinary = @VarBinary + 1; 


SELECT CAST(@VarBinary AS INT); 

RESULT:  123457


SELECT CONVERT(INT, @VarBinary); 

RESULT:  123457


DECLARE @VarChar VARCHAR(MAX) 

SET @VarChar = 'Govindaraj Kanniappan'; 

SELECT CAST(@VarChar AS  VARBINARY(MAX)); 



RESULT: 

0x476F76696E646172616A204B616E6E6961707
0616E00000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000




SELECT CONVERT(VARBINARY(MAX), @VarChar);


RESULT:
 0x476F76696E646172616A204B616E6E6961707
0616E00000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000000000000000000000000
0000000000000000000

Declaring

Varchar Length
Suggested Varbinary Length for Casting/Converting/Storing
VARCHAR(10)
VARBINARY(128)
VARCHAR(50)
VARBINARY(128)
VARCHAR(100)
VARBINARY(256)
VARCHAR(200)
VARBINARY(512)
VARCHAR(300)
VARBINARY(1024)

Test  Conversion Script:

DECLARE @VarChar VARCHAR(100) = '';
DECLARE @Cnt INT = 1

WHILE @Cnt < 100
BEGIN
          SET @VarChar = @VarChar + 'A'
          SET @Cnt = @Cnt + 1
END
SET @VarChar = @VarChar + 'B'


SELECT @VarChar

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB

SELECT CONVERT(VARCHAR(100),CONVERT(VARBINARY(258), @VarChar)); 

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB



Example
CREATE TABLE CompanyImage
  (
     ImageID int IDENTITY(1,1) NOT NULL,
     ImageVarBinary VARBINARY(max),
     ImageImage    IMAGE
  );
GO
-- Insert ER_Attribute.png file data to varbiinary
INSERT INTO CompanyImage(ImageVarBinary)
SELECT *
FROM   OPENROWSET(BULK 'E:\TutorialTips\sql\Images\ER_Attribute.png', SINGLE_BLOB) IMG_DATA;

-- Insert ER_Attribute.png file data to image
INSERT INTO CompanyImage(ImageImage)
SELECT *
FROM   OPENROWSET(BULK 'E:\TutorialTips\sql\Images\ER_Attribute.png', SINGLE_BLOB) IMG_DATA;

-- Select the data
SELECT * FROM CompanyImage WITH (NOLOCK)

Result:

ImageID
ImageVarBinary
ImageImage
1
0x89504E470D0A1A0A00000
NULL
2
NULL
0x89504E470D0A1A0A00000


NOTE:

We have use OPENROWSET with BULK mode to read the image data. BULK mode allows us to interpret and read the image data in a binary stream. This is where we take the advantage and insert the data as a binary stream into our table.
The first example we shall look at queries inserting in column with VARBINARY data type using PNG image which is physically stored in E:\TutorialTips\sql\Images\  path of SQL server machine.
The second example we shall look at queries inserting in column with IMAGE data type using PNG image which is physically stored in E:\TutorialTips\sql\Images\   path of SQL server machine.

The VARBINAY(MAX) data type is a replacement for IMAGE data type.




4 comments:


  1. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql server dba online training
    sql dba training

    ReplyDelete
  2. "IMAGE is used to only used to store the image files (BMP, TIFF, GIF, or JPEG format files)."

    Not true at all. IMAGE is the legacy name for a binary data type. You can store whatever you want in it.

    ReplyDelete
  3. very nice blog. thank you for sharing this post. Dot Net Developer

    ReplyDelete