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
|
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:
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.
|
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.
ReplyDeleteOur 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.
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 (. )( .)
ReplyDeletePadh le gandu.
Delete
ReplyDeleteYour 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
https://programmingtutorialsscript.blogspot.com/
ReplyDelete"IMAGE is used to only used to store the image files (BMP, TIFF, GIF, or JPEG format files)."
ReplyDeleteNot true at all. IMAGE is the legacy name for a binary data type. You can store whatever you want in it.
very nice blog. thank you for sharing this post. Dot Net Developer
ReplyDeleteWatch and Download world's famous drama series Kurulus Osman in English on link below
ReplyDelete👇
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