2018-04-22

Reclaim Data Space in VarBinary(Max) Column

SQL Server allows applications to store binary data, such as word files and images, in varbinary(max) column. Most likely the applications don't need to store those binary data permanently, so that we can define a retention period for the binary data, and data purging job should be created in order to free up disk space. Some applications only allow purging binary data such as photos, but other data fields related, such as id-number and name must be kept permanently. By the way, UPDATE varbinary(max) column to NULL cannot free up the unused space, only DELETE the row can make it. Below example is a proof:
CREATE TABLE [dbo].[TestBlob](
    [pk] [int] NOT NULL PRIMARY KEY,
    [blob] [varbinary](max) NULL
)
GO

TRUNCATE TABLE TestBlob;

SELECT 'EMPTY'
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

SET NOCOUNT ON;
DECLARE @i int = 1
WHILE @i < 100000
BEGIN
INSERT TestBlob (pk, blob) SELECT @i, CONVERT(varbinary(max), '
asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
')
SET @i +=1
END

SELECT 'FULL'
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

UPDATE TestBlob SET blob = NULL;

SELECT 'UPDATE NULL';
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

DELETE TestBlob;

SELECT 'DELETE';
SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
EXEC sp_spaceused @updateusage = N'TRUE'
EXEC sp_spaceused 'TestBlob';

GO
Here's the result:
As the result shows, UPDATE varbinary(max) to NULL cannot reduce the used space by data, only DELETE can reduce it. In order to make it possible to free up disk space, one method is separating it into another table, e.g.
UserTable
userId int primary key
username varchar(50)
...
PhotoTable
userId int primary key
photo varbinary(max)
Then the binary data can be deleted.

No comments:

Post a Comment