BLOBs and booleans in MS-SQL
Michael Skusa
I am developing applications that have to store boolean values
and binary large data (BLOBs) in relational databases. Does
MS-SQL support these two SQL-types ? What are the names of
these types (e.g. in Oracle BLOBs are stored using LONG RAW
columns). How large may the binary data be that is stored in
such a BLOB column ?
Greetings,
Michael Skusa
(* remove .nospam from mail address when replying via mail *)
bmc...@my-dejanews.com
Michael Skusa <2sk...@informatik.uni-hamburg.de> wrote:
> I am developing applications that have to store boolean values
> and binary large data (BLOBs) in relational databases. Does
> MS-SQL support these two SQL-types ? What are the names of
> these types (e.g. in Oracle BLOBs are stored using LONG RAW
> columns). How large may the binary data be that is stored in
> such a BLOB column ?
SQL Server supports two kinds of BLOB datatypes: text and image. text is used
for large (over 255 characters) amounts of text, image is used for anything
else, like storing executable files or binary pictures. If you're planning to
use them, do some research on complications of introducing BLOBs to your
database. (Performance, transactional integrity, storage and backup issues
come to mind.) You can store up to 2Gb in a BLOB.
Booleans are supported by the bit datatype, which may not be null. It's
common to define a default constraint on bit columns to replace the null
functionality. -- - Ben McEwan Geist, LLC bmc...@global2000.net
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Michael
Binary data types that are either fixed-length (binary) or variable-length
(varbinary).
binary[(n)]
Fixed-length binary data of n bytes. n must be a value from 1 through
8,000. Storage size is n+4 bytes.
varbinary[(n)]
Variable-length binary data of n bytes. n must be a value from 1 through
8,000. Storage size is the actual length of the data entered + 4 bytes, not
n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for
varbinary is binary varying.
Remarks
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 binary when the data entries in a column are expected to be consistently
close to the same size.
Use varbinary when the data entries in a column are expected to vary
considerably in size.
text, ntext, and image Data
Text, ntext, and image values are not stored as part of the data row but in
a separate collection of pages of their own. For each text, ntext, or image
value, all that is stored in the data row is a 16-byte pointer. For each
row, this pointer points to the location of the text, ntext, or image data.
A row containing multiple text, ntext, or image columns has one pointer for
each text, ntext, or image column.
Each table has only one collection of pages to hold text, ntext, and image
data. The sysindexes row that has indid = 255 is the anchor for the
collection. The text, ntext, and image data for all the rows in the table is
interleaved in this collection of text and image pages.
In Microsoft® SQL Server™ version 7.0, individual text, ntext, and image
pages are not limited to holding data for only one occurrence of a text,
ntext, or image column. A text, ntext, or image page can hold data from
multiple rows; the page can even have a mix of text, ntext, and image data.
While the user always works with text, ntext, and image data as if it is a
single long string of bytes, the data is not stored in that format. The data
is stored in a collection of 8 KB pages that are not necessarily located
next to each other. In SQL Server 7.0, the pages are organized logically in
a B-tree structure, while in earlier versions of SQL Server they were linked
together in a page chain. The advantage of the method used by SQL Server 7.0
is that operations starting in the middle of the string are more efficient.
SQL Server 7.0 can quickly navigate the B-tree, while older versions of SQL
Server had to scan through the page chain. The structure of the B-tree
differs slightly depending on whether there is less than 32 KB of data or
more.