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

Consultar índices particionados de uma Tabela–SqlServer

 

Segue para identificar e consultar os índices particionados de uma determinada tabela:

select distinct
   p.[object_id],
   TbName = OBJECT_NAME(p.[object_id]),
   index_name = i.[name],
   index_type_desc = i.type_desc,
   partition_scheme = ps.[name],
   data_space_id = ps.data_space_id,
   function_name = pf.[name],
   function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
   on p.[object_id] = i.[object_id]
   and p.index_id = i.index_id
inner join sys.data_spaces ds
   on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
   on ps.function_id = pf.function_id
WHERE p.[object_id] = object_id('Nome_tabela')
order by    TbName, index_name ;

Obs.: Substituir pelo nome da tabela desejado

Obrigado

Quais são as consultas mais executadas? - Oracle

 

Segue consulta para identificar quais as 20 consultas mais executadas no seu banco de dados.

SELECT sql_text, executions
  FROM (SELECT sql_text, executions,
        RANK() OVER (ORDER BY executions DESC) exec_rank
        FROM v$sql)
WHERE exec_rank <= 20;

Obrigado

Order by–Nulos primeiro

 

Como realizar a ordenação de uma massa de dados trazendo primeiro os registros cujo a coluna possui valores nulos?

No exemplo abaixo será ordenado pela coluna de nome do cliente trazendo primeiro os valores nulos.

Oracle:

SELECT CdNotaFiscal, NmCliente
FROM NOTAFISCAL
ORDER BY NmCliente NULLS FIRST

Sql Server \ Sybase:

SELECT CdNotaFiscal, NmCliente
FROM NOTAFISCAL
ORDER BY CASE WHEN NmCliente IS NULL THEN 'A' ELSE NmCliente END

Obrigado

Ver conteúdo de uma Procedure - Oracle

 

Segue duas maneiras para visualização do “corpo” de uma procedure no Oracle

1. Script 1

SET SERVEROUTPUT ON SIZE 200000
declare

begin
  FOR CR IN (select *
              from DBA_source u
             where u.type = 'PROCEDURE'
               AND U.OWNER = 'NOME_USUARIO'
               AND U.NAME LIKE '%NOME_PROCEDURE%' ) LOOP
              
     DBMS_OUTPUT.put_line(CR.TEXT);
              
  END LOOP;
end;

Obs: Substituir NOME_USUARIO pelo owner e NOME_PROCEDURE pelo nome da procedure.

 

2. Script 2

SELECT dbms_metadata.get_ddl( 'PROCEDURE','USP_GOMM_CRIA_MVIEW') FROM DUAL;

Obrigado

Index Clustered X Nonclustered- SqlServer

 

Segue algumas das diferenças entre os dois tipos de index do SqlServer:

O tipo de index Clustered realiza a ordenação dos dados com os valores da coluna que foi selecionada para fazer parte do index, ou seja, a ordem é realizada com os próprios dados da coluna pertencente ao index. Dessa forma as informações são gravadas na tabela física na mesma ordem do index facilitando bastante a localização de registros na tabela.

Por ordenar fisicamente os dados na tabela de acordo como o valor da coluna do index, para cada tabela podemos ter apenas um index Clustered.

Apesar de melhorar o desempenho na localização de um registro, temos um tempo extra gasto com inclusões ou exclusões de dados, isso acontece pois caso seja incluído ou excluído um registro pode ser que a ordem seja alterada e que ocorra um reposicionamento dos dados.

Em uma tabela pode existir index Clustered e Nonclustered, mas devemos lembrar de sempre criar primeiramente o index Clustered já que com sua criação todos os dados serão reposicionados na tabela para assumir a ordem da coluna que ira compor o index.

O tipo de index Nonclustered funciona de maneira diferente do Clustered, ou seja, no momento da criação do index as informações físicas da tabela não são reposicionadas, ficando assim o armazenamento de ordem aleatória.

É indicado o uso de index Nonclustered quando há necessidade de localizar dados que possuem diferentes tipos de critérios, ou seja, é indicado quando na localização de registros o filtro realizada utiliza diferentes campos podendo ter um ou mais index Nonclustered para cada critério selecionado no filtro da query.

Obrigado

Boas práticas ao escrever uma Procedure - SQL Server

1. Utilize recuo/endentação adequada, pois terá melhor legibilidade melhorando seu entendimento futuro e de outras pessoas
2. Escreva os comentários apropriados entre as lógicas para que assim, os outros possam entender rapidamente.
3. Escreva todas as palavras-chave do SQL Server em CAPS. Por exemplo SELECT, FROM e CREATE.
4. Procure nomear suas procedures com um nome intuitivo ao processo que ela executa
5. Declare todas as variáveis no início da procedure
6. Verifique o uso de variáveis desnecessárias, pois elas ocupam espaço na memória, tente reutiliza-las dentro do código
7. Não utilizar as iniciais SP_XXX em suas procedures. Utilize como inicial Pr_XXX pois ficará mais fácil identificar suas procedures entre as nativas do Sql Server. Inclusive na própria documentação do Sql Server já diz - “Recomendamos fortemente que você não use o prefixo sp_ no nome de procedimento. Este prefixo é usado pelo SQL Server para designar procedimentos armazenados de sistema. “
8. Defina o SET NOCOUNT ON  no início do procedimento armazenado para evitar a mensagem desnecessária, como número de linhas afetadas pelo SQL Server.
9. Tente fazer uso de tabelas temporárias com moderação, pois elas ocupam espaço no TempDB, além disso procedimento armazenados geralmente utilizam plano de execução em Cache, com o uso de tabelas temporárias é forçada uma nova compilação.
10. Nunca traga todas as colunas de uma consulta (SELECT *)  a não ser que for realmente necessário, use apenas colunas específicas que serão utilizadas no resultado.
11. Tente evitar o cursor no procedimento armazenado, ele consome mais memória. Tente utilizar a variável de tabela e while loop para iterar o conjunto de resultados da consulta.
12. Nos parâmetros de entrada da procedure, defina os types com os mesmos tamanhos e tipos das tabelas. Por exemplo, Nome Char(10) na tabela, mas você declara Nome Char(25) na procedure.
13. Use a instrução catch Try corretamente no procedimento armazenado para lidar com os erros em tempo de execução.
14. Se o retorno da procedure for uma única coluna/linha, prefira utilizar parâmetros de saída na procedure.
15. Evite utilização de sub-queries ao invés disso utilize inner join
16. Em condições de verificação de existência (exists) utilize Select top 1
17. Em consultas  (SELECT * FROM TbProduto where id = @id) para que seu plano de execução seja reaproveitado deve ser utilizado conforme exemplo anterior, mas você estiver usando uma consulta SQL como “SELECT * FROM Tbproduto where id = "+ @ eid, dessa forma não será reutizado o plano
18. Use o ORDER BY e DISTINCT, TOP somente quando for necessário.
19. Verifique a necessidade de índices entre colunas de tabelas grandes que realizam joins ou filtros por essas colunas
20. Verifique se as colunas que compõem os joins utilizam datatypes diferentes, um deles será convertido para o outro. O datatype convertido é o hierarquicamente inferior. O otimizador não consegue escolher um índice na coluna que é convertida.

[]s

Identificar permissões de uma Role – Sql Server

 

Segue consulta:

select dp.NAME AS principal_name,
           dp.type_desc AS principal_type_desc,
           o.NAME AS object_name,
           p.permission_name,
           p.state_desc AS permission_state_desc
   from    sys.database_permissions p
   left    OUTER JOIN sys.all_objects o
   on     p.major_id = o.OBJECT_ID
   inner   JOIN sys.database_principals dp
   on     p.grantee_principal_id = dp.principal_id
order by principal_name

 

[]s

Conceito do banco de dados NoSQL

NoSQL é um termo utilizado para definir um tipo de banco de dados que não segue normas de tabelas (schemas) determinadas previamente. Seu significado é (Not only SQL - Não só SQL) e vem do conceito de que o banco de dados não necessita de normalização e relacionamentos.

A computação na nuvem , análises sociais, performance na consulta/escrita, replicação e a necessidade cada vez maior de prover serviços escaláveis, estão fazendo com que sejam pensadas em soluções onde se necessitem oferecer escalabilidade horizontal. Bancos de dados NoSQL armazenam os dados com técnicas que visam atender a essa necessidade.

O NoSQL surgiu dessa necessidade, ou seja, oferecer performance superior e de uma alta escalabilidade. Os bancos de dados relacionais existentes atualmente, possuem restrições a isso, sendo necessária a distribuição vertical de servidores, ou seja, quanto mais dados, mais memória e mais disco. O NoSQL oferece a facilidade na distribuição horizontal, que em resumo é, mais dados, mais servidores, não necessariamente de alta performance. Um grande utilizador desse conceito é o Google, que usa computadores de pequeno e médio porte para a distribuição dos dados sendo essa forma muito mais eficiente e econômica.

No entanto, o banco de dados NoSQL não têm como objetivo substituir os bancos de dados relacionais, mas apenas propor algumas soluções que em determinados cenários são mais adequadas ou quanto as ferramentas de banco de dados tradicionais não são suficientes ou adequados às necessidades específicas, tais como: baixa latência, grandes volumes de dados, escalabilidade ou estruturas em que as conexões entre os dados são tão importantes quanto o próprio dado.

NoSql é um banco de dados não normalizado, que se refere ao banco de dados não seguir uma estrutura de colunas, chaves e tipos definidos previamente. No caso dos bancos NoSQL, toda a a informação necessária estará agrupada no mesmo registro, ou seja, em vez de você ter o relacionamento entre várias tabelas para formar uma informação, ela estará em sua totalidade no mesmo registro.

Tipos de banco de dados NoSql

• Key/Value Store - Esse é o tipo de banco de dados NoSQL fornecem uma chave eficiente para mapear os valores existentes, o conceito dele é uma chave e um valor para essa chave. Ele é o que aguenta mais carga de dados. Esses tipos de bancos de dados são o que tem a maior escalabilidade.
• Wide Columns Store - Estes são também chamados de bancos de dados orientados a registro. Similar aos bancos de dados relacionais, é um banco de dados grande constituído por várias tabelas, cada uma contendo um conjunto de linhas endereçáveis. Cada linha consiste de um conjunto de valores que são considerados colunas. Fortemente inspirados pelo BigTable, do Google, eles suportam várias linhas e colunas, além de permitir subcolunas.
• Document Store - Esses bancos de dados se concentram em armazenamento e acesso otimizado em documentos em vez de linhas ou registros, são Baseado em documentos XML ou JSON, podem ser localizados pelo seu id único ou por qualquer registro que tenha no documento. Alguns bancos de dados de documentos fornecem RDBMS.
• Graph Store - Com uma complexibilidade maior, esses bancos de dados guardam objetos, e não registros como os outros tipos de NoSQL. A busca desses itens é feita pela navegação desses objetos.Além disso, eles enfatizam alto desempenho para acesso a dados associativo, evitando a necessidade de junção.Uma característica que o Graph DBs possuem é a capacidade de um valor do campo armazenar o ID de outra entidade.
• Column Oriented Store - Esses são bancos de dados relacionais, porém apresentam características do NoSQL. A principal diferença deles é que os dados são armazenados em colunas, ajudando na escalabilidade.

Fontes: http://imasters.com.br - http://www.devmedia.com.br - http://www.fxplabs.com.br - http://www.nosqlbr.com.br

[]s

TRUNCATE Table x DELETE Table

Fazendo uma comparação entre a instrução DELETE e TRUNCATE temos as seguintes vantagens:

• Menos espaço transaction log usado. A instrução DELETE remove as linhas uma de cada vez e registra uma entrada no transaction log para cada linha excluída. TRUNCATE TABLE remove os dados desalocando as páginas de dados usadas para armazenar os dados da tabela e registra somente os desalocações página no transaction log.
• Menos bloqueios. Quando a instrução DELETE é executado cada linha que esta contemplada na instrução de delete da tabela está bloqueada até o final para exclusão. TRUNCATE TABLE sempre bloqueia a tabela e página, mas não cada linha.
• Sem excepção, páginas zeradas são deixadas na tabela. Depois de uma instrução DELETE é executado, a tabela ainda pode conter páginas vazias. Se a operação de exclusão não usar um bloqueio de tabela, a tabela (heap) conterá muitas páginas vazias. Para os índices, a operação de exclusão pode deixar páginas vazias tbm, embora essas páginas serão desalocada rapidamente por um processo de limpeza em segundo plano.

TRUNCATE TABLE remove todas as linhas de uma tabela, mas a estrutura da tabela e suas colunas, restrições, índices, e assim por diante permanecem. Para remover a definição da tabela, além de seus dados, use o comando DROP TABLE.

Se a tabela contiver uma coluna de identity, o contador para essa coluna é redefinido para o valor default informação na criação do identity. Se não tiver sido definido um valor default de inicio, o valor padrão 1 é usado. Para reter o contador de Identity, utilize DELETE.

Restrições:

Você não pode usar TRUNCATE TABLE em tabelas que:

• Possuem uma FOREIGN KEY. (Você pode truncar uma tabela que tem uma chave estrangeira que faz referência em si.)
• Participe de uma indexed view.
• Uso de replicação transacional ou merge replication.
  

Para tabelas com uma ou mais dessas características, use a instrução DELETE .
TRUNCATE TABLE não pode ativar uma trigger porque a operação não registra exclusões de linha individuais.

[]s

Consumo de CPU – SQL Server

 

Retorna as 10 consultas com maior consumo de CPU:

SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;

[]s

Duvidas com Join?

Porque os professores da Faculdade não mostraram essa imagem antes….rs

Fazendo leitura de Campo LOB – Oracle

Segue:

select dbms_lob.substr( campo_clob, 4000, 1 ) from tabela;

 

[]s

Oracle - Renomeando Tabelas

Para renomear uma tabela, basta executar o seguinte comando

Alter table <nome_tabela> rename to <novo_nome_tabela>;

T+....

Reconstruindo o Ìndice

A maneira mais simples para a reconstrução do índice é:

Alter index <index-name> rebuild tablespace <index-tablespace>;

O camando também tem a vantagem de não impedir o índice de ser utilizado enquanto está sendo reconstruído. O Tablespaces deve ser informado, caso contrário, o índice será reconstruído no tablespace atual (padrão) do usuário.

Reconstruir o índice também permite que novos parâmetros(campos) possam ser atribuídos ao índice.

Se o índice estiver danificado, ele deve ser excluído e recriado.
Em primeiro lugar, devemos identificar o índice e seus campos, para isso utilizamos as seguintes consultas:

SELECT COLUMN_NAME FROM USER_IND_COLUMNS
WHERE INDEX_NAME = 'index_name';

SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME, INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,PCT_INCREASE, PCT_FREE FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

Excluindo o índice:

 Drop index <index-name>;

Recriando o índice:

 Create [UNIQUE] index <index-name> on <tablea-name> (<coluna1>,<colunaN>) tablespace <tablespace-name>;

Lembrando que para a PRIMARY KEY ou UNIQUE KEY o índice é criado automaticamento na criação da constraint;

Até a próxima.....

Clausula order by

Um video encontrado no youtube, explicando muito bem a clausula ORDER BY...

Select com Datas - Oracle

Pra você que sempre encontra dificuldades em realizar selects com datas, ou na hora 'H' nunca lembra do comando, segue um select pra se guardar na manga, contendo varias formas de se obter uma data.

SELECT SYSDATE DATA_ATUAL,
ADD_MONTHS(SYSDATE,-1) MENOS_UM_MES,
ADD_MONTHS(SYSDATE,1) MAIS_UM_MES,
LAST_DAY(SYSDATE) ULTIMO_DIA_MES,
TRUNC(SYSDATE,'MONTH') PRIMEIRO_DIA_MES,
MONTHS_BETWEEN(SYSDATE,'01-JAN-2001') QTD_MES,
FLOOR(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ABAIXO,
CEIL(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ACIMA,
ABS(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) VALOR_ABSOLUTO,
ROUND((MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')),2) ARREDONDA,
TRUNC(SYSDATE,'year') PRIMEIRO_DIA_ANO,
to_char(SYSDATE,'dd " DE " FMMONTH " DE " YYYY','nls_date_language=portuguese') DIAMES_EXTENSO,
to_char(SYSDATE,'FMMONTH " DE " YYYY','nls_date_language=portuguese') MES_EXTENSO,
TO_CHAR(SYSDATE,'HH24:MI') HORA_MINUTO,
to_char(sysdate,'FMMonth','nls_date_language=portuguese') mes_corrente,
to_date(lpad(to_char(1234),4,'0'),'hh24mi') dias_data,
Floor(floor(months_between(SYSDATE,SYSDATE)) / 12) IDADE,
decode(TO_NUMBER(TO_CHAR(SYSDATE,'D')),2,'segunda-feira',3,'terça-feira',4,'quarta-feira',5,'quinta-feira',6,'sexta-feira',7,'sabado',1,'domingo')dia_da_semana
FROM DUAL;

É isso ai t+.....