Sempre que linhas ou colunas são excluídas de uma tabela, o SQL Server reutiliza o espaço para inserir novos dados. No entanto, este não é o caso quando uma coluna de comprimento variável é excluída. O espaço usado por este tipo de coluna não é recuperado automaticamente. Colunas de comprimento variável como: VARCHAR, NVARCHAR, VARBINARY, TEXT, NTEXT, etc. O SQL Server disponibiliza um recurso para recuperar esse espaço.

Vamos criar um tabela e carregar alguns dados:

CREATE DATABASE Teste;
GO
USE Teste;
GO
CREATE TABLE teste (id int IDENTITY(1, 1), coluna1 CHAR(20), coluna2 VARCHAR(MAX));

DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
	SET @i += 1;
	INSERT into teste (coluna1, coluna2) VALUES ('Conteudo aqui ' + CAST(@i AS VARCHAR(10)), REPLICATE('Conteudo aqui', 3000));
END

GO

SELECT coluna1,
coluna2
FROM teste;

Acima estamos criando um banco de dados, em seguida uma tabela com 3 colunas e carregando 10 mil registros na tabela.

Após executar o script acima, podemos ver o espaço e verificar quanto a tabela está utilizando, usando a DMV “sys.dm_db_index_physical_stats”, ela nos informa a fragmentação dos dados.

SELECT alloc_unit_type_desc, 
page_count,
avg_page_space_used_in_percent, 
record_count
FROM sys.dm_db_index_physical_stats(DB_ID('Teste'), OBJECT_ID('teste'), NULL, NULL, 'Detailed');

Acima executamos um “select” para ver os o espaço utilizado pela tabela “teste” do banco “Teste”.

clearTable1

Como podemos ver, temos 1000 páginas de dados, com uma média de 99% de uso de cada página.
Vamos deletar alguns dados e executar o “select” da DMV novamente, para ver o espaço utilizado

DELETE FROM teste WHERE id <= 5000;

clearTable2
A média de espaço de cada página é de 61% agora. Vamos executar o script de carga novamente:

DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
	SET @i += 1;
	INSERT into teste (coluna1, coluna2) VALUES ('Conteudo aqui ' + CAST(@i AS VARCHAR(10)), REPLICATE('Conteudo aqui', 3000));
END

Se execurtamos o “select” da DMV novamente iremos perceber que a média subiu para 99% de novo.

clearTable3

Excluindo uma coluna
Vamos analisar o que ocorre quando uma coluna de tamanho variável é excluída da tabela.

ALTER TABLE teste
DROP COLUMN coluna2;

Depois de excluir a “coluna2” da tabela teste, e executarmos o “select” da DMV, percebemos que nada mudou:

clearTable3

Vamos inserir apenas 100 linhas e ver o espaço utilizado pela tabela

DECLARE @i INT = 0;
WHILE @i < 100
BEGIN
	SET @i += 1;
	INSERT into teste (coluna1) VALUES ('Conteudo aqui ' + CAST(@i AS VARCHAR(10)));
END

clearTable4
Como podemos ver na imagem, que o espaço não foi reutilizado, depois de excluir uma coluna de tamanho variável da tabela.

Solução
Para resolver esse problema devemos executar um comando para recuperar o espaço não reutilizado (esse comando também pode ser utilizado para uma “view”). Este comando bloquea a tabela durante sua execução, podendo levar um bom tempo, dependendo da quantidade de espaço inutilizado:

DBCC CLEANTABLE (Teste, 'teste');

Acima executamos um comando para recuperar o espaço inutilizado pelo SQL Server.
Se executarmos o “select” da DMV novamente teremos:

clearTable5

Acima podemos perceber a queda na média.

Espero ter ajudado!
Até a próxima pessoal!