If all the rows on a page are deleted by the DELETE command the page will be marked as empty. If all the pages in an extent are empty, that extent will be release for re-use by other SQL tables.
SQL Server uses the ALTER INDEX ... REBUILD statement to globally reorganise space in a table. There is a ALTER TABLE ... REBUILD statement in SQL 2008. These are is similar to (but not the same!) as a Oracle or DB2 reorganise.
ALTER INDEX ... REBUILD is the new form of the REBUILD INDEX command and includes more options. With both of these facilities, all rows will get moved so they use the minimum number of pages and the pages take the minimum number of extents, allowing for the FREE SPACE paramaters. Any space released can be used by other SQL Serve tables.
The database file size on disk is not reduced by these operations. You can shrink the database file size with the SHRINKFILE comand. It is bad practice to shrink the database file size if you expect it to grow again within three months.
Repeated shrink and growth of database files will harm your performance.
Repeated ALTER INDEX ... REBUILD without shrinking your database file will maintain or improve your performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara