Understanding the MS-SQL Server IMAGE Data Type

The MS-SQL Server IMAGE data type is similar to the TEXT data type in that it you can store 2,147,483,647 bytes of data in a column declared as data type IMAGE. You would use an image type, for example, if you wanted to create a table of graphics images such as:

CREATE TABLE graphic_images
 (id          INTEGER,
  description VARCHAR(250),
  picture     IMAGE)

Typically, you won't use an INSERT statement to enter binary data into an IMAGE column. Instead, you will use an application program that passes the binary (picture) data to the DBMS for storage in the table.

Similarly, an IMAGE column is not meant for direct output using a SELECT statement, although such a SELECT statement is not prohibited. Instead, you would have the DBMS pass the image data to a graphics program (like WinJPeg) or to a Web browser for display.

If you do display an IMAGE column using the SELECT statement, you will find that the SELECT statement does not translate the values in the IMAGE column to ASCII. For example, suppose that you use the INSERT statement

 INSERT INTO graphic_images
  VALUES (123,'Picture 123','Picture'123')

to place data into a row in the GRAPHICS_IMAGES table created as the example at the beginning of this tip. If you use the SELECT statement

 SELECT * FROM graphic_images

MS-SQL Server would display:

id        description    picture
----------------------------------------------------
123       Picturel23     0x50696374757265313233

By not translating the hexadecimal representation of data in the IMAGE column to ASCII when SELECTED, the DBMS makes it easy to pass the actual "raw" picture file to a graphics program in answer to a query sent to the DBMS by an application program.

Understanding the LONG (Oracle) or TEXT (MS-SQL Server) Data Type

If you need to store a large amount of text data in a table, you may run into the problem of needing to store a character string larger than the maximum number or characters allowed for the CHARACTER (or VARCHAR) data type. Suppose, for example, that you had a HUMAN_RESOURCES table and one of the columns was RESUME. If you are using MS-SQL Server as your DBMS, you could store only the first 4,000 characters of the resume in the RESUME column of the HUMAN_RESOURCES table. Fortunately, Microsoft has the TEXT data type which, like Oracle's LONG data type, lets you store character strings of up to 2,147,483,647 characters. (If you are storing text strings in Unicode using columns of type NTEXT, you can store only 1,073,741,823 characters. Each Unicode character takes 2 bytes of storage, so you can store only half as many of them.)

It would be wasteful to preallocate 2GB of disk space for each column you declare as type TEXT. As such, MS-SQL Server preallocates only a small portion (8K) of the maximum TEXT space and allocates the remainder in 8K (8,192 byte) increments as you need it. As such, when it is ready to save character 8,193 of a TEXT string to disk, the DBMS allocates another block (page) of 8,192 bytes and creates a link from the page holding the previous 8,192 bytes to the page holding the next 8,192 bytes.

Once the DBMS stores the data in the TEXT column to disk, the entire TEXT block is logically contiguous. This is to say that the DBMS "sees" the TEXT block as one huge character string, even if the individual 8K blocks (pages) that make up the TEXT block are not physically contiguous. As such, you can display the entire contents of a TEXT column using a single SELECT statement such as:

 SELECT resume FROM human_resources

if, for example, HUMAN_RESOURCES were a table defined as:

 CREATE TABLE human_resources
  (id              INTEGER,
   name            VARCHAR(25),
   department_code TINYINT,
   data_of_hire    DATE,
   resume          TEXT)

Note	The actual number of characters of TEXT data displayed by the SELECT statement is limited by the value of the Global Variable @@Textsize. If you don't change the value of @@Textsize, MS-SQL Server limits the number of TEXT characters displayed to 64K (64,512) by default.

Chapter 1 - SQL Tips and Techniques

Chapter 1 - SQL Tips and Techniques

Chapter 1: Understanding SQL Basics and Creating Database Files

Understanding Flat Files - SQL Tips and Techniques

Understanding the Hierarchical Database Model - SQL Tips and Techniques

Understanding the Network Database Model

Understanding the Relational Database Model

Understanding Codd's 12-Rule Relational Database Definition

Understanding Terms Used to Define an SQL Database

Understanding the Components of a Table

Understanding Table Names

Understanding Column Names

Understanding Views

Understanding Schemas

Understanding the SQL System Catalog

Understanding Domains

Understanding Constraints

Understanding the History of SQL

Understanding the Difference Between SQL and a Programming Language

Understanding Data Definition Language (DDL)

Understanding Data Manipulation Language (DML)

Understanding Data Control Language (DCL)

Understanding SQL Numeric Integer Data Types

Understanding SQL Character Data Types

Understanding the Advantages of Using the VARCHAR Data Type

Understanding the LONG (Oracle) or TEXT (MS-SQL Server) Data TypeUnderstanding the MS-SQL Server IMAGE Data Type

Understanding Standard SQL Datetime Data Types and the DATETIME Data Type

Understanding the SQL BIT Data Type

Understanding Constants

Understanding the MS-SQL Server ISNULL() Function

Understanding the MS-SQL Server IDENTITY Property

Understanding Assertions

Understanding the SQL DBMS Client/Server Model

Understanding the Structure of SQL Statements

Understanding SQL Keywords

Using the MS-SQL Server Query Analyzer to Execute SQL Statements

Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log

Using the ED Command Within ISQL to Edit SQL Statements

Using the MS-SQL Server ISQL to Execute SQL Statements from the Command Line or Statements Stored in an ASCII File

Using the MS-SQL Server Enterprise Manager to Create a Database and Transaction Log

Using DROP DATABASE to Erase an MS-SQL Server Database and Transaction Log

Understanding How to Size MS-SQL Server Databases and Transaction Logs

Understanding the MS-SQL Server TempDB Database