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.

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF