I'd run DBCC UPDATEUSAGE against the table as a first step, since the symptoms show inconsistent space usage.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found ...
I've encountered this problem before on production boxes, what you need to do is rebuild tables and indexes for each table (in that order).
Here's the query I use to keep tables in check. It will help you determine which tables need to be rebuilt, and create the SQL queries you need to run. This query is limited to those with higher than 1MB unused space ...
One of the columns is a LOB of type image, and it's storing files that
range in size from a few KB to several hundred MB
You could be experiencing internal fragmentation.
What is the page fragmentation for this table?
And is the fragmentation for the in-row different from the off-row pages?
You say you have files that are a few KB.
SQL Server stores ...
If we assume that your column is an INT then, as John's analysis shows, you will see a saving of approximately 80%, at the expense of some extra work joining things back together when reading the values.
As you only need values from 0 to 9 you could get a 75% saving by simply redefining the column as a TINYINT which consumes one byte instead of four, ...
The only time I've been unable to shrink a DB and reclaim space is because you cannot shrink a DB beyond the initial size of the DB when it was created. So for example, if your DB is a copy of the production DB and you first created the DB at 525GB, sql server will not allow you to shrink the size below 525GB no matter how much data you delete from the DB. ...
Certainly not the most elegant way, but it works with dynamic sql:
sql1 text := '''SELECT user_id AS row_name, field_name AS category, field_value AS value
FROM user_data a JOIN field b ON a.field_id = b.id''';
sql2 text := '''SELECT field_name FROM field ORDER BY 1''';
SELECT string_agg(v, ', ') ...
Use identical table definitions with a single document type column of type json, jsonb or hstore to store additional key/value pairs for the few columns that differ between installations. That's often the most efficient solution if it needs to be dynamic / flexible.
If the total number of dynamic columns is not more than a few dozen, you might even just ...
Is the database in full recovery mode? If so, when you perform a shrink, it's logging all of the changes and won't shrink it the way you're expecting. Depending on your hours of operation, you could take a backup, switch to bulk-shipping recovery mode and then run the shrink on the data file. After that, you'd want to run index scripts to repair/rebuild ...