Will frequent delete and inserts of the same data lead to fragmentation? I have a table which caches images from another data source:

CREATE TABLE [dbo].[ProductImages] (
    [ProductId] INT NOT NULL , 
    [ImageId] INT NOT NULL, 
    [Image] VARBINARY(MAX) NOT NULL, 
    PRIMARY KEY ([ProductId], [ImageId])
)


The images are served together with [Product] data in the form of:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123


Now, the problem is that the images from the data source may sometimes be updated, and there is no trigger-like or event-like mechanism to detect this change. So, my solution is to have a scheduled job which periodically downloads images from the source. Since the images are pre-processed (format changed, resized) before being stored into the database, I have no way of actually knowing whether the image has changed. Therefore, every time I do the following:

DELETE FROM [ProductImages] WHERE [ProductId] = 123
INSERT INTO [ProductImages] VALUES (123, 0, {Image_0})
INSERT INTO [ProductImages] VALUES (123, 1, {Image_1})


Most of the time (say at least 98%), there has been no change - I am just deleting and re-inserting the same data for nothing.

If this scheduled job is executed long enough, will it eventually lead to poor table performance because the data is scattered all over the place?
Let's consider what data will actually be stored on the data pages. You said that your images are around 10 KB - 17 KB which means that the VARBINARY(MAX) column will never be stored in row. Instead, it will always be stored as LOB data. So on the in row page for each row you'll have two INT columns along with a 24 byte pointer to the LOB data. Assuming a generous overhead of 10 bytes per row you can fit around 8000 / (10+4+4+24) = 190 rows on a single page. The row data on each page may be physically out of order and the different data pages may not be in order but SQL Server won't just insert the rows wherever it can find space. It's not true that for a given ProductId you'll have ImageId 1 stored on page 1, ImageId 2 stored on page 2, ImageId stored on page 3, and so on. So when you run the following query:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123


Unless you have hundreds of images for one product, all of the IO is going to be from going to the LOB pages to get the [Image] data. There will only be one or two relevant pages that store the pointers to the LOB data. I don't know how SQL Server picks which page is used for the LOB data. I suppose it's possible for the LOB pages to become more spread out over time.

No one here can say for sure if fragmentation will be a problem for you. As I see it your options are to schedule a maintenance job that deals with any fragmentation, to add code to your application to prevent unnecessary updates, or to simulate your workload to see if you need to take further action. From SQL Server's point of view it doesn't matter that the VARBINARY data corresponds to an image. Anything that you store to the column of the right size should work fine for a test.