Opção PERSISTED e suas utilizações!

Posted on outubro 18, 2011

1


 

Explicação

 

A opção PERSISTED é utilizada durante a criação de uma coluna computada (Computed Column), ela faz com que o SQL Server armazene fisicamente o resultado da expressão que foi especificada na consulta, invês de recalcular toda a vez em Run-Time. A coluna é recalculada somente quando é atualizada.

 

Utilizações e Estudo de Caso

 

  1. Coluna Computada com PERSISTED e sem PERSISTED

 

Aqui veremos a diferença entre a criação de uma coluna computada criada com a opção PERSISTED e sem PERSISTED em relação a espaço utilizado dentro da tabela.

–*************************************************************

–Computed Column With Persisted and Not Persisted                                     

–*************************************************************

USE tempdb

go

 

— No Persisted = On Run-Time

— With Persistd = Stored in Data Table

 

— DROP TABLE PersistedTable

 

CREATE TABLE PersistedTable

(

      ID INT NOT NULL,

      Nome VARCHAR(50),

      Sobrenome VARCHAR(50)

)

GO

 

INSERT INTO PersistedTable (ID, Nome, Sobrenome)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, ‘Bob’,

          CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 2 = 1 THEN ‘Smith’ ELSE ‘Brown’ END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

Para vizualizarmos o tamaho da tabela iremos utilizar a SP_SPACEUSED

 

sp_spaceused ‘PersistedTable’

image

 

ALTER TABLE PersistedTable

ADD NomeCompleto AS (Nome + ‘ ‘ + Sobrenome)

go

 

sp_spaceused ‘PersistedTable’

image

 

ALTER TABLE PersistedTable

ADD NomeCompletoPersisted AS (Nome + ‘ ‘ + Sobrenome) PERSISTED

go

 

sp_spaceused ‘PersistedTable’

image

 

Ou seja a diferença da criação de uma coluna computada é que quando for utilizado o PERSISTED, na hora da criação do campo ele irá realizar a expressão e gravar fisicamente na tabela, sendo assim essa coluna só será recomputada quando houver um update nos campos da expressão da coluna computada.

 

Quando a coluna computada for utilizada sem PERSISTED toda vez que uma consulta envolver essa tabela a coluna será computada e mostrada ou seja em RUN-TIME, sendo assim o overhead pode ser grande.

 

  1. Criação de Índices em Coluna Computada e Validação PERSISTED

 

CREATE NONCLUSTERED INDEX idxNCL_PersistedTable_NomeCompleto

ON PersistedTable (NomeCompleto)

go

 

sp_spaceused ‘PersistedTable’

go

image

Realizando a criação de um índice em cima de uma coluna computada sem PERSISTED fará somente que o tamanho do índice cresça e o espaço reservado tambêm.

 

  1. Opção de Persisted com Foreign Key

Podemos utilizar uma coluna computada com PERSISTED para a utilização de FOREIGN KEY, a criação da coluna SEM PERSISTED não é possível, porque para ser uma FOREIGN KEY é necessário que a mesma esteja em disco ou seja persistida em.

 

USE tempdb

GO

  DROP TABLE AreaTelefone

  DROP TABLE DadosTelefone

 

CREATE TABLE AreaTelefone

(

      CodigoArea CHAR(3) NOT NULL

)

go

 

ALTER TABLE AreaTelefone

ADD CONSTRAINT PK_AreTelefone_CodigoArea

PRIMARY KEY (CodigoArea)

 

INSERT INTO AreaTelefone (CodigoArea)

VALUES (‘061’),

         (‘048’),

         (‘031’)

 

CREATE TABLE DadosTelefone

(

      ID INT IDENTITY(1,1) NOT NULL,

      Name VARCHAR(50) NOT NULL,

      Telefone VARCHAR(20) NULL

)

go

 

ALTER TABLE DadosTelefone

ADD CONSTRAINT PK_DadosTelefone_ID

PRIMARY KEY (ID)

go

 

ALTER TABLE DadosTelefone

ADD CodigoArea AS CAST(SUBSTRING(Telefone,2,3) AS CHAR(3)) PERSISTED

Depois da criação da coluna iremos fazer com que a tabela DadosTelefone possua referência com a tabela de Area Telefone, sendo assim toda vez que for colocado um telefone ele irá tirar somente os DDD’s e verificar se existe o DDD na tabela de AreaTelefone, sendo assim temos.

ALTER TABLE DadosTelefone

ADD CONSTRAINT FK_DadosTelefone_AreaTelefone_CodigoArea

FOREIGN KEY (CodigoArea)

REFERENCES AreaTelefone (CodigoArea)

 

INSERT INTO DadosTelefone (Name, Telefone)

VALUES (‘Luan Moreno Medeiros Maciel’,‘(061)8139-8966’)

 

INSERT INTO DadosTelefone (Name, Telefone)

VALUES (‘Luan Moreno Medeiros Maciel’,NULL)

image

 

Analisamos que a coluna computada CodigoArea pegou os 3 dígitos do campo telefone e inseriu no CodigoArea, e logo apos verificou com os dados na tabela de AreaTelefone, sendo assim a coluna só será recomputada se realizamos um Update na tabela….

UPDATE DadosTelefone

      SET Telefone = ‘(048)8139-8966’

WHERE ID = 1

image

 

  1. Vemos que aqui ele modificou o CodigoArea da tabela isso porque o update foi bem sucedido, mais se tentarmos utilizar um CodigoArea que não exista na tabela de AreaTelefone temos….

UPDATE DadosTelefone

      SET Telefone = ‘(066)8139-8966’

WHERE ID = 2

 

 

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint “FK_DadosTelefone_AreaTelefone_CodigoArea”. The conflict occurred in database “tempdb”, table “dbo.AreaTelefone”, column ‘CodigoArea’.
The statement has been terminated.

 

Como não existe esse DDD cadastrado então não é realizado o Update do Dado.