 |
|
SQL Server Tips by Gama and Naughter
|
Binary
The binary data type will store binary fixed length data, such as
images, sounds, video, Office documents, compressed data and other
non-alphanumeric data. The 8000 byte size limitation restricts its
applications, but it is still very useful. The following code
demonstrates the right padding with zeros:
DECLARE @b binary(5)
SET @b=CAST('abc' as binary(3))
select DATALENGTH(@b)
SELECT @b
5
0x6162630000
The length of the variable is always the same because of the
padding. If @b was NULL the length would be NULL, not zero.
Varbinary
The varbinary data type will store binary variable length data. The
advantages over the binary data type are saving storage space and
keeping an accurate representation of the data. This code
demonstrated the differences:
DECLARE @b varbinary(5)
SET @b=CAST('abc' as binary(3))
select DATALENGTH(@b)
SELECT @b
3
0x616263
The length of the variable is the number of bytes of its data, there
are no extra bytes for headers or padding.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
