Sql-server – Finding integer database columns that may have their data type changed to reduce size

This is a very tough question to answer and the only correct thing to say is "it depends". Yes, it's trite but that doesn't make it untrue. That being said I'll try my best to give you an answer (use Standard) and explain the thought process behind it.

I'm not familiar with the "Web" Edition of SQL but I can certainly rattle off the differences between Standard and Enterprise. At the risk of a massive generalization I'd say that if you're looking at between 10 and 100GB for a single DB and you're not sure you need Enterprise features, the upfront cost difference combined with the ease of upgrading the SKU (Edition) if/when you need to mean that choosing Standard over Enterprise probably makes the most sense. I'm guessing that "Web" edition is some semi-stripped down Standard version and personally I'd avoid it based on the "Web Workloads Only" disclaimer. What does that even mean?!

So how does one truly determine if they'll need Enterprise features? Well, the best way I can think of is to download the Standard and Enterprise trials (or get an MSDN license) and do performance testing with both editions. If you find you can't hit/exceed your performance targets with the lower edition identify the bottlenecks and then see if the Enterprise features would assist. If so, upgrade & make the needed changes to use the features then re-test. In my experience the main features one makes use of in Enterprise are (in order of most common to least):

• online reindexing
• row/page compression
• backup compression
• filtered indexes
• partitioning & partitioned indexes
• indexed views
• parallel index operations

And finally... When moving up from something that's running well on SQL Express I don't think there would be many cases that justify the jump to Enterprise. Alone, the additional system resources that the full version of SQL can use should deliver big gains.

The sum of the file size for all data files has to be < 10 GB, not the amount of data in the file. So, deleting data from some tables, or even dropping some tables, does not solve problem. You need to shrink the file, something like this:

ALTER DATABASE mydb MODIFY FILE (name = N'logical_name', size = 2048MB);

This will fail if the database size can't be reduced to 2 GB. You may need to first issue:

DBCC SHRINKFILE(logical_name, 2048);

If you use any form of SHRINKFILE, then you'll need to validate in File Explorer that the data file(s) are actually as small as you think (because shrink operations will shrink as much as they can, and stop silently when they can't reach your target size).

Then take a backup, then restore on SQL Server Express (with @@VERSION the same or higher than the source, of course).

You may come across other issues, for example if you have used any features that aren't supported on Express.