Storing binary data in SQL Server

In this recipe, we will store some binary data, including some images, a PDF, and a Word document, in SQL Server.

Getting ready

Let's create a sample table that we can use for this recipe. Run the following script in SQL Server Management Studio to create a table called SampleBLOB that has a BLOB, or VARBINARY(MAX), field:

USE SampleDB
GO
IF OBJECT_ID('SampleBLOB') IS NOT NULL
    DROP TABLE SampleBLOB
GO

CREATE TABLE SampleBLOB
(
      ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
      FileName VARCHAR(200) ,
      InsertedDate DATETIME DEFAULT GETDATE() ,
      InsertedBy VARCHAR(100) DEFAULT SUSER_SNAME() ,
      BLOBStuff VARBINARY(MAX) ,
      FileExtension VARCHAR(50)
)

Create a directory called C:\DATA\ if it doesn't already exist and copy the sample BLOB files that come with the book scripts, or use your own directory and BLOB files.

How to do it...

These are the steps required to save binary data in SQL Server:

1. Open PowerShell ISE...