Think of SQL data files as a commercial self-storage unit:
Data from tables and indexes is organized into these units (called "pages"), and SQL server is very meticulous about keeping track of what belongs where.
If you fill up all the storage units, you can buy land and build more (expanding the data file), but what happens when some units get emptied?
- If the empty unit is at the end of the row, sure, you could demolish the unit you built and sell off the land, but that's a lot of work, especially if you might need to buy it right back again.
- But if the empty unit is in the middle of the row of storage units, you can't even do that without a lot of extra work. You'd have to move all the stuff from full units at the end of the row into the empty units in the middle before demolishing units and selling off the property.
Not only is moving all those units around a lot of (unnecessary) work, it also can cause table and index fragmentation, which can impact performance.
Imagine if you had a family that bought 10 units next to each other, and you later forced them to move 4 of those units to random empty units elsewhere in the facility. When they came to add or remove stuff from their units, they will have to do a lot of extra work wandering around between the scattered units.
Obviously this analogy isn't perfect, and clearly there are situations that you really do want to shrink a data file, but you don't want SQL server doing it on its own, you want to be in control of when and how exactly that happens. For example, you'd probably want to do a
DBCC SHRINKFILE to a specific size, leaving some portion of internal free space, then follow immediately by a reindex/defrag of your tables/indexes, to fix any fragmentation you've just created.
Moral of this story: Never turn on AutoShrink!