SQL Tutorials: BINARY VS VARBINARY

What are the differences between BINAY and VARBINAY [BINARY VS VARBINARY]?

Which data type should use for defining a column in a table [BINAY VS VARBINARY]?

BINARY

VARBINARY

Bytes

It consumes 1 byte per character.

It is introduced in SQL Server 2005.

Data Length

Maximum storage size is 8000 bytes.

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

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.

Variable-length binary data. N can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

Data

Storage

Type

BINARY is used to store the image files (BMP, TIFF, GIF, or JPEG format files) and binary data.

BINARY(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.

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.

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

Declaring

Varchar Length	Suggested Binary Length for Casting/Converting /Storing
VARCHAR(10)	BINARY(128)
VARCHAR(50)	BINARY(128)
VARCHAR(100)	BINARY(256)
VARCHAR(200)	BINARY(512)
VARCHAR(300)	BINARY(1024)
VARCHAR(500)	BINARY(1024)
VARCHAR(500)	BINARY(2048)

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:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAB

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

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAB

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)
VARCHAR(500)	VARBINARY(1024)
VARCHAR(500)	VARBINARY(2048)

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:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAB

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

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAB

Conversion /CAST

DECLARE @Binary BINARY(128);

SET @Binary = 123456;

SET @Binary = @Binary + 1;

SELECT CAST(@Binary AS INT);

RESULT: 123457

SELECT CONVERT(INT, @Binary);

RESULT: 123457

DECLARE @VarChar VARCHAR(128)

SET @VarChar = 'Govindaraj Kanniappan';

SELECT CAST(@VarChar AS BINARY(128));

RESULT:

0x476F76696E646172616A204B616E

6E69617070616E0000000000000000

0000000000000000000000000000000

000000000000

SELECT CONVERT(BINARY(128), @VarChar);

RESULT:

0x476F76696E646172616A204B616E

6E69617070616E0000000000000000

0000000000000000000000000000000

000000000000

DECLARE @VarBinary VARBINARY(128);

SET @VarBinary = 123456;

SET @VarBinary = @VarBinary + 1;

SELECT CAST(@VarBinary AS INT);

RESULT: 123457

SELECT CONVERT(INT, @VarBinary);

RESULT: 123457

DECLARE @VarChar VARCHAR(128)

SET @VarChar = 'Govindaraj Kanniappan';

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

RESULT:

0x476F76696E646172616A204B616E

6E69617070616E0000000000000000

0000000000000000000000000000000

000000000000

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

RESULT:

0x476F76696E646172616A204B616E

6E69617070616E0000000000000000

0000000000000000000000000000000

000000000000

Index

The maximum permissible key length is 900 bytes for creating the Index.

Index can’t be created on BINARY data type if the data length above 900 bytes. It throws the exception error when we create an index on this data type.

CREATE NONCLUSTERED INDEX IX_CompanyImage_ImageBinary

ON dbo.CompanyImage(ImageBinary)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

Error:

Index 'IX_CompanyImage_ImageBinary' was not created. This index has a key length of at least 2000 bytes. The maximum permissible key length is 900 bytes.

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.

CREATE NONCLUSTERED INDEX IX_CompanyImage_ImageVarBinary

ON dbo.CompanyImage(ImageVarBinary)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

Error:

Column 'ImageVarBinary' in table 'dbo.CompanyImage' is of a type that is invalid for use as a key column in an index.

Use When?

Use BINARY when the sizes of the column data entries are consistent.

Use VARBINARY when the sizes of the column data entries vary considerably.

Example

CREATE TABLE CompanyImage

(

ImageID int IDENTITY(1,1) NOT NULL,

ImageVarBinary VARBINARY(max),

ImageBinary BINARY(8000)

);

-- 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(ImageBinary)

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	ImageBinary
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 BINARY data type using PNG image which is physically stored in E:\TutorialTips\sql\Images\ path of SQL server machine.

SQL Tutorials

Navigation

Wednesday 30 November 2016

BINARY VS VARBINARY

2 comments:

Pages

Pages

Pages