Example of binary data type in SQL Server

There are two datatypes for storing binary data type in the SQL Server:

1. binary - for fixed length binary data
2. varbinary - for variable length data

My question is: how is data inserted into them? Do they have any
delimiters that go into the insert statement like strings and datetimes
have? What format (hex/decimal?) do they accept data in? Can you please
give me an insert statement example?

When we wrote the SQL Stanardards, we wanted to design a high level
abstract language for data that would be portable beause it would NEVER
deal with the low level PHYSICAL representation of the data.

Why do you wish to write kludges?

Water Cooler v2  wrote:
> There are two datatypes for storing binary data type in the SQL Server:
>
> 1. binary - for fixed length binary data
> 2. varbinary - for variable length data
>
> My question is: how is data inserted into them? Do they have any
> delimiters that go into the insert statement like strings and datetimes
> have? What format (hex/decimal?) do they accept data in? Can you please
> give me an insert statement example?

Apparently Celko forgot about the part where you answer the question.

I've always done it by encoding it in hex first.  Then you can do
something like this:

insert myTable (myBinaryColumn) values  (0x8d7e6a5d4b3e2e1f)


Just put the 0x before whatever hex value you come up with.  The
difference between the two is the same as the difference between char
and varchar.  In general, if the values in that column are either short
or all roughly (or exactly) the same length, use binary.  Otherwise,
use varbinary.

In addition to what ZeldorBlat said, if you need to make larger values, keep
in mind that the + operator is used to concatenate binary strings.

eg: 0xA34B + 0xCA02 is 0xA34BCA02

This can help break such strings up over multiple lines in a script.

HTH,

Greg

"Water Cooler v2" <wtr_clr@yahoo.com> wrote in message
news:1138493601.782824.249580@o13g2000cwo.googlegroups.com...
> There are two datatypes for storing binary data type in the SQL Server:
>
> 1. binary - for fixed length binary data
> 2. varbinary - for variable length data
>
> My question is: how is data inserted into them? Do they have any
> delimiters that go into the insert statement like strings and datetimes
> have? What format (hex/decimal?) do they accept data in? Can you please
> give me an insert statement example?
>

--CELKO--  (jcelko212@earthlink
.net)  writes:
> When we wrote the SQL Stanardards, we wanted to design a high level
> abstract language for data that would be portable beause it would NEVER
> deal with the low level PHYSICAL representation of the data.

And what if the data is binary? How would you store a JPEG image in a
database? You would disassemble it, and store it in some high-level format?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>>  How would you store a JPEG image in a database? You would disassemble it
, and store it in some high-level format? <<

1) Store a file name as a string, so that the application can grab the
file.

2) If this is a serious problem, then use an image base tool.  IBM has
one that lets you make a drawing as the query and it will pull out the
ones that are closest; there are several products that do nothign but
face and fingerprint matching; CAD systems that handle 3D objects; etc.


SQL is not the answer to everything.

--CELKO--  wrote: 
>
> 1) Store a file name as a string, so that the application can grab the
> file.
>
> 2) If this is a serious problem, then use an image base tool.  IBM has
> one that lets you make a drawing as the query and it will pull out the
> ones that are closest; there are several products that do nothign but
> face and fingerprint matching; CAD systems that handle 3D objects; etc.
>
>
> SQL is not the answer to everything.

Again, you still haven't answered the question.

Amazingly, I agree with Celko here. #1 is what we do and it works
really well. It allows you to have a separate file server when you need
greater storage space for user files without haveing to make your SQL
Server a file server.

--CELKO--  (jcelko212@earthlink
.net)  writes: 
it, and store it in some high-level format? <<
>
> 1) Store a file name as a string, so that the application can grab the
> file.

Yes, this is an option. Just as you could store employee names,
number of car doors or what ever on a file, and then store the file
name in the database.

However, it's difficult to get transactional consistency with a file
system. You commit the transaction, but the file could not be stored
on disk. Or the file is stored on disk, but then the transaction was
rolled back for some resaon. Or consider the case that the file system
is rearranged, and all the file pointers become stale.

It is not uncommon to use this approach in SQL Server though, and
the main reason is probably that the image data type has been difficult
to work with. (Which has nothing to do with its binary-ness to do, but
it's largeness.) With the advent of varbinary(MAX) in SQL 2005, there
may be less reason for this.

> 2) If this is a serious problem, then use an image base tool.  IBM has
> one that lets you make a drawing as the query and it will pull out the
> ones that are closest; there are several products that do nothign but
> face and fingerprint matching; CAD systems that handle 3D objects; etc.
>
> SQL is not the answer to everything.

But it's surprising to hear that it's not the tool to store data.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

> #1 is what we do and it works really well.

What's your backup/recovery strategy? What happens if someone replaces
a file? Overwrites the file without changing datetime stamp? Renames a
folder? How do you know that your data has been corrupted / messed up
with?