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.




8 comments:

  1. Technology does have some complexities associated with it. Be it be old or new technology, it has some glitches and flaws associated with it. We at Trend Micro Geek Squad, are a team of technical expert professionals, offer support related to any of your digital or electronic device failures. Whenever you get an error, your system is running slow, or not working adequately call us straight away.
    Our Geek Squad Tech Support team is focused on tuning-up your devices and bringing them in workable condition as before.Webroot Geek Squad support related services include providing a quick security solution to resolve the issues that are said to be contagious.

    ReplyDelete
  2. Love is an ice cream sundae, with all the marvelous coverings. Sex is the cherry on top. Hey, i am looking for an online sexual partner ;) Click on my boobs if you are interested (. )( .)

    ReplyDelete

  3. 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
  4. https://programmingtutorialsscript.blogspot.com/

    ReplyDelete
  5. "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
  6. very nice blog. thank you for sharing this post. Dot Net Developer

    ReplyDelete
  7. Watch and Download world's famous drama series Kurulus Osman in English on link below
    👇
    Kurulus Osman in English

    📢Get high quality backlinks for your
    Website with BacklinksIndexer
    BacklinksIndexer

    Crypto trading online course
    Join on link below
    Crypto quantum leap

    📒 Read Home doctor book online
    Then you will be a doctor for your family
    Home Doctor Book

    💰Create own NFTs and earn 1000$
    Complete guide
    Create NFT

    Join online YouTube course
    And be a professional YouTuber
    Tube Mastery and Monetization by matt

    🦷Steel Bite Pro
    Best product for
    Teeth pain, cavities,teeth whitening and other oral health issues with money back guarantee
    Steel Bite Pro

    ReplyDelete