CODEWITHSUNDEEP
t-sqlblobfilestream
Levitikon
Levitikon

Reputation: 7847

How to convert varbinary(max) with filestream to actual varbinary(max) in T-SQL

I have a database that was setup to use a blob FileStream for audio files on a varbinary(max) field. It's since grown in size in upwards of 80GB and I'm facing performance issues.

After doing some looking around I discovered my average blob size is about 180k. And since according to MSDN filestreams should be used for objects over 1MB, I'm re-evaluating how I store these blobs. MSDN also states, "For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance." So I'm considering moving from varbinary(max) with filestream to just using varbinary(max) fields.

So my question is, is there a great way using an sql script to move each filestream blob from the filestream into the actual varbinary field itself? The alternative, which I've been working on before deciding to ask is to have a c# app query the database for the blobs and write each blob to the file system. Then manually remove the filestream stuff from the database. Then have the c# app read the blobs from the file system and write back into the database. I figured there has to be an easier way.

Upvotes: 4

Views: 6777

Answers (1)

Dusty
Dusty

Reputation: 3971

Assuming your source table looks like this:

CREATE TABLE audioFiles
(
    AudioID INT IDENTITY NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL,
    [AudioData] VARBINARY(MAX) FILESTREAM NULL,
    RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWID())
)

Then you could create a 2nd table:

CREATE TABLE audioBlobs
(
    AudioID INT IDENTITY NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL,
    [AudioData] VARBINARY(MAX) NULL,
    RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWID())
)
GO

(Note that FILESTREAM is missing from the AudioData column in the second table ... causing the binary data to be stored on-page with the rest of the record, rather than in the separate FILESTREAM filegroup.)

Then you can just insert the data from one table to the other:

SET IDENTITY_INSERT audioBlobs ON

INSERT INTO audioBlobs (AudioID, Name, AudioData, RowGuid)
    SELECT AudioID, Name, AudioData, RowGuid FROM audioFiles

SET IDENTITY_INSERT audioBlobs OFF

Once you're finished, you could drop your original table, and rename your new table to the original table's name:

DROP TABLE audioFiles
GO

EXECUTE sp_rename N'dbo.audioBlobs', N'audioFiles', 'OBJECT' 
GO

Alternately, you could create a 2nd VARBINARY(MAX) column right alongside your FILESTREAM column in your original table, and just update the value of the new column with the old column's data. Note that either way you go, you will more than double your total disk space usage -- double the space of your actual audio data, migrating it from your FILESTREAM filegroup to your PRIMARY filegroup (or wherever your main data file is), plus a lot of space in your transaction log.

Upvotes: 8

Related Questions