SQL Server: Views Indexadas

Todos nós temos uma idéia da importância de índices para a busca de dados em tabela, dentre on índices temos o CLUSTER, NÃO CLUSTER e UNIQUE. Este tema merece no mínimo um tópico inteiro só para tipos.

por Thiago Pastorello Gervazoni

Todos nós temos uma idéia da importância de índices para a busca de dados em tabela, dentre on índices temos o CLUSTER, NÃO CLUSTER e UNIQUE. Este tema merece no mínimo um tópico inteiro só para tipos.

Na verdade o índice do SQL funciona igual ao índice de um livro, se quiser buscar um dado específico não precisa ler o livro inteiro até encontrar a informação (chamado table Scan), basta consultar o índice e verificar em que capítulo reside a informação, isto optimiza e muito consultas, é fator primordial para quanto maior a base de dados maior a importância de indices bem criados (nos campos certos).

As únicas tabelas que não se faz necessário a criação de índices, são as que tem baixa cardinalidade, ex Masculino e Feminino, nestes casos o SQL ignora o índice e realiza a table scan (busca linha a linha).

Como já temos noção da importância de índices e o que os mesmss nos traz de benefícios, vamos aprender como criar indices em views.

Você pode imaginar como vou criar um índice em um objetvo que não contem dados, ou seja não possui dados residentes ?

Nós usaremos a opção WITH SCHAMABINDING na criação da view, que cola uma ou mais tabelas junto a view, criando uma integridade entre ambas.

Com os indices certos acelera até 30% do tempo de busca. O processo de criação de indice é igual a de uma table.

Onde Usar:

TABELAS CANDIDATAS A VIEWS INDEXADAS
Aplicações OLAP Datawerehouse
2 ou mais tabelas de grande volume e com join
Views que agregam data
Tabelas com poucos inserts, deletes e updates

PÉSSIMAS TABELAS CANDIDATAS
Tabelas com muito Insert, Update e Delete (pois precisam atualizar os indices)
Queries que não usam Joins
Queries que incremetam registros (union, compute etc)
Queries que retornam sempre o mesmo numero de registros

Como Usar:

O Atributo Schemabinding "cola" uma tabela a view, criando uma integridade referencial, ou seja, com este atributo eu não posso apagar a tabela de onde a view está buscando a informação, assim estaria apagando os indices também.

OBS: Ao se criar a tabela estes atributos devem valer da forma abaixo, se a mesma já está criada sem, é preciso dar o create table novamente.

SET para ON

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER

SET para OFF

• NUMERIC_ROUNDABORT

Exemplo:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

CREATE TABLE tblFuncionario(funCodigo INT IDENTITY(1,1) PRIMARY KEY,funNome varchar(150),funSexo char(1))

insert into tblFuncionario values("Thiago Pastorello","M")
insert into tblFuncionario values("Maria da Silva","F")
insert into tblFuncionario values("Marcos Almeida","M")
insert into tblFuncionario values("Alfredo Fonseca","M")
insert into tblFuncionario values("Daniela Ribeiro","F")

CREATE TABLE tblTicket(ticCodigo INT IDENTITY(1,1),funCodigo INT REFERENCES 
tblFuncionario(funCodigo),ticData Datetime DEFAULT(getdate()))

insert into tblTicket VALUES(1,getdate())
insert into tblTicket VALUES(2,getdate())
insert into tblTicket VALUES(3,getdate())
insert into tblTicket VALUES(4,getdate())
insert into tblTicket VALUES(5,getdate())

CREATE VIEW vw_schemaBusca WITH SCHEMABINDING
AS
  SELECT F.funCodigo,funNome,funSexo,ticData  FROM dbo.tblFuncionario as   F,dbo.tblTicket as T WHERE   
F.funCodigo=T.funCodigo

Agora já temos 2 tabelas de testes e a view indexada criada

OBS: Ao criar a view sempre os nomes da tabelas dentro da view tem que estar em 2 partes dbo.nome Agora só falta criar o(s) indice(s) na view. Vamos fazer isto de maneira gráfica ao invês de transact-sql.
Vou criar 2 índices um para nome e outro para data.

1) Botão direito na view all tasks/manage indexes

2) New , entre com o nome do índice, campo(s) e que tipo cluster, unique etc

3) Criação do segundo índice

4) Total 2 índices criados para a minha view

5) Agora basta utilizar da velocidade da mesma em seus sistemas.

Depois veremos ainda em performance, índices que cobrem queries, e uma abordagem sobre tipos de índices.

Um abraço.