Wednesday, 30 November 2016

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 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
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAB

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

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
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
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAB

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

Result:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
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
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
000000000000



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

RESULT: 

0x476F76696E646172616A204B616E
6E69617070616E0000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
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
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
000000000000


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


RESULT:

0x476F76696E646172616A204B616E
6E69617070616E0000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
0000000000000000000000000000000
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)
  );
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(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.



3 comments:

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

    ReplyDelete
  2. This helps to understand binary data type usage. Thank You.

    ReplyDelete