1

I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename.

CREATE TABLE Images (
    RecordID BIGINT NOT NULL,
    ImageFilename VARCHAR(50) NOT NULL,
    ImageData VARBINARY(MAX) --expected avg. size: 256K
)

I anticipate that 90% of the space utilization on the database will be coming from the BLOBs. I know that SQL Server data compression will not compress the BLOB data through row or page compression. Can I expect any savings when compressing the backups, other than the compression taking place on the RecordID and ImageFilename fields?

3
  • 1
    If database is not that big why not try a backup command with compression and see how much benefit you get.
    – Shanky
    Dec 21, 2016 at 7:00
  • 2
    I believe jpg is already a compressed format so further space saving is unlikely. Dec 21, 2016 at 10:27
  • That's what I was wondering about in particular @MichaelGreen.
    – mathewb
    Jun 29, 2017 at 20:01

1 Answer 1

2

I finally went ahead and got a test database loaded with data. Table structure is:

CREATE TABLE [dbo].[ImageTable](
    [CompositeID] [bigint] NOT NULL,
    [ImageID] [tinyint] NOT NULL,
    [FullImage] [varbinary](max) NULL,
    [ThumbnailImage] [varbinary](max) NULL,
    CONSTRAINT [XPK_ImageTable] PRIMARY KEY CLUSTERED ([CompositeID] ASC,[ImageID] ASC)
)

The table was loaded with approximately 2.8M records. The FullImage value was set, and the ThumbnailImage value was left as NULL for each record.

The database itself is 154GB in size. The uncompressed backup is 154GB in size. The compressed backup is 134GB in size.

Given that the two PK fields account for approximately 24GB (2.8M records * 9 bytes/record), it looks like the only savings from compression are being realized by compressing the PK fields.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.