Diferença entre DML , DDL , DCL e TCL

DML

DML é abreviação de Data Manipulation Language. Ele é usado para recuperar, armazenar, modificar, apagar, inserir e atualizar dados no banco de dados, ou seja, utilizado para gerenciamento de dados do esquema.

Exemplos de comandos: SELECT, INSERT, UPDATE, DELETE, MERGE, LOCK TABLE, CALL, EXPLAIN

DDL

DDL é abreviação de Data Definition Language. Ele. é usado para criar e modificar a estrutura dos objetos de banco de dados.

Exemplos de comandos: CREATE, ALTER, DROP, ROLES, COMMENTS, RENAME, TRUNCATE.

DCL

DCL é abreviação de Data Control Language. Ele é usado para criar permissões e integridade referencial e também é usado para controlar o acesso a banco de dados.

Exemplo de comandos: GRANT, REVOKE

TCL

TCL é abreviação de Transactional Control Language. Ele é usado para gerenciar diferentes operações que ocorrem dentro de um banco de dados (Mudanças realizadas dor DML).

Exemplo de comandos: COMMIT, ROLLBACK, SAVEPOINT.

Obrigado

Gerenciamento e Manutenção de Índices–SQL Server 2008

 

Os dados dentro de um índice são armazenados  em ordem classificatória. Devido a repetidos eventos de inclusão e exclusão, podemos ter uma fragmentação dos índices, por exemplo, ao remover uma linha da tabela, a entrada referente ao índice também precisa ser removida, com isso, ficamos com uma lacuna na página do índice que não é recuperado pelo SQL Server devido ao custo da reclassificação do índice.

Vamos entender como controlar a taxa de fragmentação de nossos índices:

FILL FACTOR

Determina a porcentagem de espaço livre em cada página de índice, ou seja, o quanto pode ser preenchido e quanto deve ser mantido em branco reservando para alterações realizadas na tabela (inclusão e alteração). Por exemplo ao criarmos um índice, se determinarmos FILL FACTOR em 90%, isso quer dizer que o SQL Server irá reservar apenas 10% de cada página com espaço livre que resulta em uma reorganização mais rápida pois temos 10% de espaço em branco para manobras em cada página de dados.

E quando devo usar FILL FACTOR?

A resposta é depende… do número de alterações que sua tabela recebe.

CUIDADO: ao configurar o FILL FACTOR do seu índice com porcentagem de preenchimento muito baixo, pois o excesso de informação em branco nas páginas faz com que o SQL Server tenha que percorrer muitas paginas para retornar a informação desejada podendo tornar sua Query lenta.

Desfragmentando um índice

Com os espaços vazios deixados nas tabelas, periodicamente será necessário realizar a desfragmentação do índice que deve ser executada utilizando o comando ALTER INDEX:

REBUILD: Reconstrói todos os índices deixando as páginas com o preenchimento configurado na opção FILL FACTOR. A reconstrução do índice implica em criar toda a estrutura B-Tree novamente. Caso seja necessário a manutenção com concorrência no banco de dados, será necessário recriar o índice com a opção ONLINE para que seja obtido um bloqueio compartilhado e impeça alterações até a finalização do REBUILD.

Exemplo para reconstruir todos os índices de uma tabela:

ALTER INDEX ALL ON tabela REBUILD

REORGANIZE: Remove a fragmentação apenas do nível folha, ou seja, as páginas de nível intermediário e raiz não são desfragmentadas. REORGANIZE é uma operação que não gera bloqueio a longo prazo (online)

Exemplo para reorganizar o índice de uma tabela:

ALTER INDEX nome_índice ON tabela REORGANIZE

Recomendação: Devemos usar REBUILD quando a fragmentação do índice estiver acima de 40% e utilizar REORGANIZE quando a fragmentação estiver entre 10% a 40%

Consultando a fragmentação de um índice

A consulta abaixo deve ser utilizada para identificar o nível de fragmentação do seu índice:

SELECT a.index_id, name, avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorksLT2008'), OBJECT_ID(N'SalesLT.Product'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

avg_fragmentation_in_percent - O percentual de fragmentação.

fragment_count - O número de fragmentos (fisicamente páginas de folha consecutivos) no índice.

avg_fragment_size_in_pages - Número médio de páginas em um fragmento de um índice.

 

Desativando um ìndice

Um índice pode ser Desativado utilizando ALTER INDEX:

ALTER INDEX nome_índice ON tabela DISABLE

 

Obrigado

Índices XML–Sql Server

 

Sabemos que colunas do tipo XML possuem estrutura que podem ser “varrida” pelo SQL Server para localização de dados dentro de um documento XML, mas para melhorar o desempenho da localização de uma dado dentro de uma estrutura XML, podemos criar um índice XML.

Existem 2 tipo de índices XML:

Índice XML Primário:

Colunas XMLs são armazenadas como objetos binários (Blob) no banco de dados, com isso, as buscas em uma coluna desse tipo torna-se muito lenta devido ao grande volume de informação. Para acelerar a busca de informações em uma coluna do tipo XML é recomendado a criação de um índice XML primário.

Na verdade um índice XML primário particiona as informações do XML de forma que fiquem armazenadas divididas com:

• Nome da Tag do XML
• caminho raiz do documento
• valor do nó
• tipo do nó
• Chave primária correspondente da tabela

Índice XML Secundário

Após criar um índice primário para uma coluna do tipo XML, podemos criar mais 3 índices secundários para a mesma coluna. Índices secundários ajudam com determinados tipos de consultas XML e só é permitido sua criação após a criação de um índice primário.

Existem 3 tipos de índices secundários:

• PATH – para consultas que utilizam expressões de caminho XML
• VALUE – para consultas que buscam valores em qualquer lugar do documento XML
• PROPERY– para consultas que recuperam particularidades de objetos em qualquer lugar do documento XML junto com colunas adicionais da tabela.

A seguir será demonstrado um script exemplo de criação de índices XML

Criação da tabela:

CREATE TABLE DocumentXML (
   ID int IDENTITY NOT NULL,
   DocumentStore xml NOT NULL,
   CONSTRAINT PK_Document PRIMARY KEY CLUSTERED
   (ID ASC))

Carga da tabela:

INSERT INTO DocumentXML (DocumentStore)
VALUES('<?xml version="1.0" ?>
<Document Name="Poema">
<Author>Fabio</Author>
<Text>Teste 1.</Text>
</Document>')

INSERT INTO DocumentXML (DocumentStore)
VALUES('<?xml version="1.0" ?>
<Document Name="Romance">
<Author>Otavio</Author>
<Text>Teste 2.</Text>
</Document>')

INSERT INTO DocumentXML (DocumentStore)
VALUES('<?xml version="1.0" ?>
<Document Name="Policial">
<Author>Jose</Author>
<Text>Teste 3.</Text>
</Document>')

Consultando resultados inseridos:

SELECT DocumentStore.value('(/Document/@Name)[1]',
                           'varchar(50)' ) as Tipo
FROM DocumentXML

Tipo

Poema

Romance

Policial

SELECT DocumentStore.query('(/Document/Text)') as Texto

Texto

<Text>Teste 1.</Text>

<Text>Teste 2.</Text>

<Text>Teste 3.</Text>

Criando índice primário:

CREATE PRIMARY XML INDEX PkXML_Document
   ON DocumentXML (DocumentStore)

 

Criando índices secundários:

CREATE XML INDEX Ind_Value
ON DocumentXML (DocumentStore)
USING XML index PkXML_Document
FOR VALUE

CREATE XML INDEX Ind_PATH
ON DocumentXML (DocumentStore)
USING XML index PkXML_Document
FOR PATH

CREATE XML INDEX Ind_PROPERTY
ON DocumentXML (DocumentStore)
USING XML index PkXML_Document
FOR PROPERTY

 

Obrigado

Configurando opções de acesso a banco de dados Sql Server 2008

 

Existem algumas opções de controle de acesso e capacidade de mudança de dados parametrizáveis no banco de dados, são elas:

ONLINE

Define o status do banco de dados. Quando o banco de dados estiver com o status ONLINE, significa que todas as operações serão executadas normalmente.

OFFLINE

Define também o status do banco de dados. Quando o banco de dados estiver com o status OFFLINE, significa que o mesmo não esta acessível.

EMERGENCY

Outro status do banco de dados que define que apenas pode ser acessado por um membro do role db_owner e que o único comando que permite ser executado é SELECT.

READ_ONLY

Um banco de dados configurado no modo READ_ONLY indica que está disponível apenas para consulta, não sendo possível realizar qualquer tipo de gravação e todo o log de transação será removido.

READ_WRITE

Indica que o banco de dados está disponível para leitura e gravação. Toda alteração no banco de dados para modo de read_only ou read_write, faz com que o log de transação seja recriado.

SINGLE_USER

Indica que apenas um usuário por vez pode estar conectado ao banco de dados

RESTRICTED_USER

Permite que somente membros das roles db_owner, dbcreator e sysadmin tenham acesso ao banco de dados.

MULTI_USER

Configuração padrão de um banco de dados, permite que vários usuários tenham acesso simultaneamente.

Para realizar a alteração do modo de acesso ou capacidade de mudança do banco de dados deve ser realizado com ALTER DATABASE conforme exemplo a seguir:

ALTER DATABASE <banco_de_dados> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Rollback immediate faz com que todas as transações abertas sejam revertidas imediatamente e usuários não autorizados sejam desconectados para que a nova alteração de modo de acesso passe a valer. Pode ser utilizado também a opção ROLLBACK AFTER <segundos>  que irá respeitas o número de segundos indicados antes de reverter ou finalizar transações.

Obrigado

Configurando opções automáticas de banco de dados–Sql Server 2008

 

Existem opções que podem ser habilitadas no banco de dados que permitem sua execução automática, são elas:

AUTO_CLOSE

Se esta opção estiver ativada em seu banco de dados, faz com que ao ser finalizada a última conexão o Sql Server desligue o banco de dados e libere todos os recursos da máquina ocupados. Assim que uma conexão ao banco de dados é solicitada, o Sql Server inicia o banco de dados e volta a alocar os recursos necessários. Por padrão na criação do banco de dados essa opção é desativada;

 

AUTO_SHRINK

Quando ativada essa opção, o Sql Server passa a verificar constantemente a utilização de espaço alocado para os arquivos de dados e log de transação. Ao finalizar a verificação, se o Sql Server identificar que a utilização do espaço tiver um percentual de 25% de espaço livre alocado, os arquivos serão reduzidos automaticamente para liberação de espaço em disco. É recomendado manter essa opção desativada e realizar a redução de espaço livre manualmente quando necessário

 

AUTO_CREATE_STATISTICS

Se essa opção estiver ativada, faz com o que o Sql Server crie automaticamente as estatísticas não encontradas no momento da otimização do processamento da query. É sabido que a criação  das estatísticas gera uma certa sobrecarga de processamento e tempo, mas como vantagem temos o desempenho da consulta que com certeza compensa “pagar o preço”.

 

AUTO_UPDATE_STATISTICS e AUTO_UPDATE_STATISTICS_ASYNC

Quando ativada uma das opções acima, permite que o Sql Server atualize as estatísticas desatualizadas durante a otimização da consulta (AUTO_UPDATE_STATISTICS) ou realiza a atualização das estatísticas de forma assíncrona durante a otimização da consulta (AUTO_UPDATE_STATISTICS_ASYNC).

 

Obrigado

Modos de recuperação de banco de dados–SQL Server 2008

 

O modo de recuperação indica as formas de gerenciamento do log de transação além disso determina os tipos backups disponibilizados para serem aplicados no banco de dados, são eles:

• Completo (Full)
• Registro em massa (Bulk-logged)
• Simples (Simple)

Completo (Full)

Um banco de dados no modo de recuperação completo, todas as alterações realizadas (DML e DDL) são registradas no log de transação, sendo possível recuperar o banco de dados a partir de um determinado ponto no tempo. Todas as alterações realizadas no banco de dados são mantidas no log de transação e só serão removidas com a execução de um backup de log de transação.

 

Registro em massa (Bulk-logged)

Banco de dados com alto volume de dados em transações podem sofrer problemas de performance com o modo de recuperação definido como FULL. O modo de recuperação de registro em massa diferentemente do modo FULL não registra linha a linha das alterações no log de transação para bulk operation (bcp, bulk insert, select..into, create index alter index…rebuild) e sim registra as extensões. Desta forma, não é possível realizar o backup de um banco de dados a partir de um determinado ponto no tempo.

 

Simples (Simple)

Esse modo de recuperação, registra no log de transação as operações exatamente da maneira que é realizado no modo FULL, porem, isso não indica que os arquivos de log serão armazenados permanentemente, ou seja, sempre que o processo de checkpoint do banco de dados for executado serão truncados. Um banco de dados no modo de recuperação Simple, não pode ser recuperado a partir de um ponto no tempo

Script para identificar qual modo de recuperação parametrizado no banco de dados:

SELECT name, recovery_model_desc    FROM sys.databases       WHERE name = 'XXXX' ; –substituir pelo nome do banco
GO

Script para alteração do modo de recuperação do banco de dados:

ALTER DATABASE XXXXX SET RECOVERY FULL ;

ALTER DATABASE XXXXX SET RECOVERY BULK_LOGGED ;

ALTER DATABASE XXXXX SET RECOVERY SIMPLE ;

Obrigado

Criação de arquivo utilizando Select–Oracle

 

Segue dica para geração de arquivo a partir de uma consulta de forma rápida utilizando SqlPlus:

 

{
echo "set pagesize 0"
echo " Select ColunaExemplo1||','||ColunaExemplo2||','||ColunaExemplo3 ||','||ColunaExemplo4 ||','||ColunaExemplo5||','||
ColunaExemplo6||','||ColunaExemplo7||','||ColunaExemplo8 FROM Tabela_TESTE;"
} | sqlplus -s usário/Senha@Servidor >> Resultado.log

Como saída, será gerado o arquivo resultado.log com o resultado do Select, podendo esse arquivo futuramente ser importado como um CSV.

O script foi utilizado para geração de uma arquivo para uma grande massa de dados e a resposta\performance foi satisfatória.

 

Obrigado