How to Set Default Value or Binding for BLOB DataTypes on SQL Server

Introduction

This tip/trick helps to set a default value for BLOB data type such as Image and VarBinary.

Background and Problem

There is a time you need to have image/file on a database table. The table field may be required after a certain time or it must contain default value if its value wasn't available at the time of data insertion. The existing data should also be updated from null to some real data.

There are a number of ways to achieve such situations. I'll present a couple of ways as follows. Before the solution, let's closely see the values of an Image/VarBinary column . A simple SQL Select statement will show us that the value consists of hexadecimals which start with an 0x. Example could be 0x89504E47... From this, we can observe that the value should start with a 0x (a zero and an x, say Zerox/Zerobyte) and a hexadecimal value(0-9 and A-F) values. The table below summarizes the observation.

To make the solution easy, let's assume we have:

1. A table Employee with columns ID, Name, Photo with data types int, nvarchar and image respectively. Note: For simplicity, I omitted lots of columns that can represent an Employee.
   SQL

   CREATE TABLE [dbo].[Employee](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [Name] [nvarchar](50) NULL,
       [Photo] [image] NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   ))

2. An image file which is located in a specific directory say (c:\) with a name nophoto.png. A sample image can be downloaded from here.

How to Solve the Problem

1. Simplest but Clumsy way (Zerox/Zerobyte): The simplest way is to use Zerox/Zerobyte(0x) or set this value to the desired column default value property.
   SQL

   -- Insert new Employee
   INSERT INTO 
   Employee(Name,Photo) 
   VALUES('Wonde',0x);
   
   -- Update Employees where photo is unavailable
   UPDATE Employee 
   SET Photo = 0x 
   WHERE Photo IS NULL

   And to set as default value.

   SQL

   ALTER TABLE [dbo].[Employee] 
   ADD  CONSTRAINT [DF_Employee_Photo]  
   DEFAULT (0x) FOR [Photo]

   Although this solves the problem, Zerox/Zerobyte(0x) may not represent a true image/file content value for future use.

2. Graceful way: The handy way is to create a function that read an image/file from a specific directory and returns it as Varbinary(MAX) datatype so it can be used directly or can be set directly to desired column default value property.

CREATE FUNCTION [dbo].[GetBlobData]()
RETURNS VARBINARY(MAX) 
AS
BEGIN
    DECLARE @IsFileExists INT,
        @BinaryData VARBINARY(MAX)
    -- Set minimum binary representation Hexadecimal number as default
    SET @BinaryData =(0x) 
    -- First check if the file exists on the directory 
    EXEC Master.dbo.xp_fileexist N'C:\nophoto.png', @IsFileExists OUT 
    IF @IsFileExists = 1
    
        -- If exists then read the file content
        SET @BinaryData = (SELECT * FROM OPENROWSET(BULK N'C:\nophoto.png',SINGLE_BLOB) _
	AS BLOBData)

    RETURN @BinaryData
END  

Notice the code, if the file does not exist, it will send a Zerox/Zerobyte(0x). Let's modify the previous example using this function.

-- Insert new Employee
INSERT INTO 
Employee(Name,Photo) 
VALUES('Wonde',[dbo].[GetBlobData]());

-- Update Employees where photo is unavailable
UPDATE Employee 
SET Photo = [dbo].[GetBlobData]() 
WHERE Photo IS NULL

And to set as default value.

ALTER TABLE [dbo].[Employee] 
ADD  CONSTRAINT [DF_Employee_Photo]  
DEFAULT ([dbo].[GetBlobData]()) FOR [Photo]

Points of Interest

I found the tip is useful and wanted to share it with the community. I hope you will find it useful as well.

History

• January 01, 2013 First version
• January 05, 2013 Updated version
• January 15, 2013 Updated version