Por default, no SQL Server uma coluna declarada como chave primária (constraint PRIMARY KEY) cria automaticamente um índice clustered (se você não está familiarizado com os tipos de índice do SQL Server, veja este vídeo sobre o assunto [ainda não disponível]). No índice clustered, como a ordem lógica dos registros em disco é mesma ordem da chave do índice, se os valores da chave nas linhas sendo inseridas são monotônicos (sempre crescente ou sempre decrescente) as inclusões não causam fragmentação na tabela. Se a chave não for monotônica a tabela sofrerá fragmentação.
O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!
/* =============================================================================================================================== 1 - Chave não-monotônica: causa fragmentação Neste script criamos uma tabela de testes (Pessoas) que tem uma PK na coluna EMail (varchar). Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e vemos a fragmentação causada. Reconstruímos o índice base da PK e vemos a fragmentação ser eliminada pela reordenação dos registros e das páginas da tabela. Inserimos mais 8.000 linhas e novamente a tabela se fragmenta. A PK não é monotônica. =============================================================================================================================== */ -- Criação de tabela com PK "natural" (E-mail) DROP TABLE Pessoas CREATE TABLE Pessoas ( Codigo int not null identity, Email varchar(200) not null, Nome varchar(50) not null, Sobrenome varchar(50) not null, CPF char(11) not null, UF char(2) CONSTRAINT PK_Pessoas PRIMARY KEY (Email), ) GO -- Pega nome e sobrenome de AdventureWorks2012.Person.Person (download em http://msftdbprodsamples.codeplex.com/releases/view/93587) pra gerar dados de teste -- O uso de um cursor na inserção é para simular uma aplicação transacional inserindo linhas na tabela -- (Se usarmos um INSERT INTO ... SELECT ... o Query Optimizer ordena as linhas antes de fazer a inserção, e a fragmentação não ocorre) -- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela DECLARE @nome varchar(100), @sobrenome varchar(100) DECLARE c CURSOR FOR SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName ASC OPEN c FETCH NEXT FROM c INTO @nome, @sobrenome WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO Pessoas VALUES ( @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email @nome, -- Nome @sobrenome, -- Sobrenome LEFT(NEWID(), 11), -- CPF LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF ) FETCH NEXT FROM c INTO @nome, @sobrenome END CLOSE c DEALLOCATE c GO SELECT COUNT(*) FROM Pessoas -- PK EMail não é monotônica: fragmentação altíssima DBCC SHOWCONTIG('Pessoas') GO -- Se reconstruirmos o índice a fragmentação é eliminada pela reordenação das páginas, mas isto é só temporário - novas -- operações de inclusão e alteração irão fragmentar novamente a tabela (o que não ocorre numa PK IDENTITY) ALTER INDEX PK_Pessoas ON Pessoas REBUILD DBCC SHOWCONTIG('Pessoas') GO -- Inserção de mais 8.000 linhas fragmenta a tabela de novo DECLARE @nome varchar(100), @sobrenome varchar(100) DECLARE c CURSOR FOR SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person order by LastName DESC OPEN c FETCH NEXT FROM c INTO @nome, @sobrenome WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO Pessoas VALUES ( @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email @nome, -- Nome @sobrenome, -- Sobrenome LEFT(NEWID(), 11), -- CPF LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF ) FETCH NEXT FROM c INTO @nome, @sobrenome END CLOSE c DEALLOCATE c GO DBCC SHOWCONTIG('Pessoas') /* =============================================================================================================================== 2 - Chave monotônica: não gera fragmentação. Neste script repetimos a sequencia do script anterior, com a diferença de que a PK agora é monotônica (uma coluna IDENTITY). Incluimos 8.000 linhas com valores de e-mails "aleatórios" (não ordenados), e não ocorre fragmentação, pois as chaves dos registros é crescente. Inserimos mais 8.000 linhas e nenhuma fragmentação ocorre. A PK é monotônica. =============================================================================================================================== */ -- Criação de tabela com PK artificial (Codigo - identity) DROP TABLE Pessoas CREATE TABLE Pessoas ( Codigo int not null identity, Email varchar(200) not null, Nome varchar(50) not null, Sobrenome varchar(50) not null, CPF char(11) not null, UF char(2) CONSTRAINT PK_Pessoas PRIMARY KEY (Codigo), ) GO -- Pega os 1os 8.000 registros do SELECT que gera os dados de teste e os insere na tabela DECLARE @nome varchar(100), @sobrenome varchar(100) DECLARE c CURSOR FOR SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName ASC OPEN c FETCH NEXT FROM c INTO @nome, @sobrenome WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF) VALUES ( @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email @nome, -- Nome @sobrenome, -- Sobrenome LEFT(NEWID(), 11), -- CPF LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF ) FETCH NEXT FROM c INTO @nome, @sobrenome END CLOSE c DEALLOCATE c GO SELECT COUNT(*) FROM Pessoas -- PK Codigo é monotônica: nenhuma fragmentação ocorre - inserção é sempre no "fim" da tabela DBCC SHOWCONTIG('Pessoas') GO -- Inserção de mais 8.000 linhas: tabela se mantém compacta DECLARE @nome varchar(100), @sobrenome varchar(100) DECLARE c CURSOR FOR SELECT DISTINCT TOP 8000 FirstName, LastName = isnull(MiddleName,'') + LastName FROM AdventureWorks2012.Person.Person order by LastName DESC OPEN c FETCH NEXT FROM c INTO @nome, @sobrenome WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO Pessoas (Email, Nome, Sobrenome, CPF, UF) VALUES ( @nome + '.' + @sobrenome + '@srnimbus.com.br', -- Email @nome, -- Nome @sobrenome, -- Sobrenome LEFT(NEWID(), 11), -- CPF LEFT(@nome,1) + LEFT(@sobrenome,1) -- UF ) FETCH NEXT FROM c INTO @nome, @sobrenome END CLOSE c DEALLOCATE c GO DBCC SHOWCONTIG('Pessoas') |
