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

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

Quando foi atualizado as estatísticas da tabela–SQL Server

 

Segue consulta para identificar quando foi atualizada a estatística de uma determinada tabela:

No exemplo a seguir foi utilizada a tabela 'HumanResources.Department'

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')
GO

index_name	StatsUpdated
PK_Department_DepartmentID	2012-03-29 13:52:19.380
AK_Department_Name	2012-03-29 13:52:22.550

Obrigado

Criar LDF a partir do MDF–SQL Server

 

Baixei o AdventureWorksLT2008.MDF da Microsoft, mas ao tentar importar o banco de dados o SQL Server informa que não foi possível encontrar o arquivo de LOG (.ldf).

Segue o comando para criar o ldf a partir de uma arquivo mdf:

EXEC sp_attach_single_file_db
    @dbname = 'AdventureWorksLT2008',
    @physname = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2008_Data.mdf'

Onde:

@dbname – Nome do Banco de Dados

@physname – Diretório onde esta armazenado o arquivo mdf.

Obrigado

SQL Server Data Compression–Introdução

 

Há uma coisa que todos os DBA sabe com certeza, e isso é que seus bancos de dados irão crescer com o tempo. Quanto mais dados tivermos, mais trabalho o SQL Server tende realizar.

Ok, mas em que o Data Compression me ajuda?

A compressão de dados em SQL Server oferece dois benefícios potenciais ao DBA:

• Reduzir o tamanho dos arquivos físicos (MDF/NDF), reduzindo a quantidade de armazenamento em disco.
• Reduzir a quantidade de I/O necessário para uma carga de dados, ajudando a impulsionar
  desempenho.

Tipos de compressão de dados

A partir do SQL Server 2008 foi oferecido duas formas de compressão de dados:

ROW - Nível de compressão de dados por linha. Esta característica de compressão leva em consideração os tipos de dados que definem a coluna da tabela, por exemplo, uma coluna char(50) e o valor armazenado na coluna em sua maioria é 15 caracteres, neste caso a compressão por linha vai ocupar em disco somente o espaço exigido para os 15 caracteres, ou seja, com esse tipo de compressão não será alocado espaço em disco para valores zero ou nulos fazendo com que mais linhas sejam alocadas em uma mesma página. Alguns outros tipos de dados podem ser comprimidos, mas não é aplicado para todos.

PAGE – Nível de compressão de dados por página. Além de armazenar dados de forma eficiente dentro de uma linha, a compressão otimiza a página de armazenamento de várias linhas em uma página, minimizando a redundância de dados. A compressão de página usa compactação de prefixo(procura padrões comuns no início de cada coluna, exemplo, todas que comecem com XPTO%)  e compressão de dicionário (procura por correspondências exatas em todas as colunas e linhas de cada pagina) que são substituídas por uma referencia abreviada de menor caracteres ocupando menos espaço.

Quando uma tabela ou índice não possui configuração para compressão de dados o seu tipo estará definido como NONE.

Obrigado

Diferença entre Replace e Stuff–SqlServer

 

Abaixo exemplo com as diferença da aplicação das 2 funções:

Utilizando o Replace, parte da string é substituída por outra. A seguir será substituída a string “Martinez” por “Teste”.

Select Replace('Fabio Martinez','Martinez','Teste')

column1
-----------
Fabio Teste

Já com a função Stuff podemos substituir uma posição específica da string. A seguir será substituída na string a partir da sétima posição 5 caracteres.

Select stuff('Fabio Martinez',7,5,'Teste')

column1
--------------
Fabio Testenez

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

Sistemas operacionais–SqlServer 2008

 

Quais os sistemas operacionais suportados para cada versão do SqlServer 2008?

SqlServer Express:

• Windows XP Professional SP2 ou superior
• Windows Vista Home Basic ou superior
• Windows XP Home Edition SP2 ou superior
• Windows XP Home Reduced Media Edition
• Windows XP Tablet Edition SP2 ou superior
• Windows XP Media Center 2002 SP2 ou superior
• Windows XP Professional Reduced Media Edition
• Windows XP Professional Embedded Edition Feature Pack 2007 SP2
• Windows XP Professional Embedded Edition para Point of Service SP2
• Windows Server 2003 Samall Bussiness Server Standard Edition R2 ou superior

SqlServer Developer e Evaluation:

• Windows XP Professional SP2 ou superior
• Windows Vista Home Basic ou superior

Sistemas operacionais suportados por todas as versões do SqlServer 2008:

• Windows Server 2008 Standard ou superior
• Wndows Server 2003 Standard SP2 ou superior

Obs: Por utilizar recursos .Net Framework, o SqlServer 2008 não é suportado pelo Windows Server 2008 Server Core que não possui tais.

 

Fonte – Kit de treinamento MCTS (Exame: 70-432)

Obrigado

 

Requisitos mínimos para instalação do SqlServer 2008

 

Segue os requisitos mínimos para instalação do SqlServer 2008 em 32 e 64 bits:

 

32 Bits:

Processador – Pentium III ou superior

Velocidade do processador – 1,0 gigahertz (GHz) ou superior

Memória – 512 megabytes (MB)

64 Bits

Processador – Itanium, Opteron Athelon ou Xeon/Pentium com suporte para EM64T

Velocidade do processador – 1,6 gigahertz (GHz) ou superior

Memória – 512 megabytes (MB)

Quanto ao espaço livre para instalação, isso vai depender dos serviços e ferramentas selecionadas no momento da instação.

Obrigado

Uso do Log de Transação–Sql Server

 

Informa o percentual de uso do log de transação de todos os banco de dados.

dbcc sqlperf (logspace)

 

Database Name	Log Size (MB)	Log Space Used (%)	Status
master	0,9921875	64,56693267822266	0
tempdb	399,9921875	60,40694046020508	0
msdb	3,9921875	37,279842376708984	0
CURSO	5999,9921875	100,00083923339844	0

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

 

Obrigado

Como identificar o Isolation Level–Sql Server

 

Para identifcar não só o Isolation Level mas também outros comandos:

DBCC useroptions

Set Option	Value
textsize	2147483640
language	us_english
dateformat	mdy
datefirst	7
lock_timeout	-1
quoted_identifier	SET
ansi_null_dflt_on	SET
ansi_warnings	SET
ansi_padding	SET
ansi_nulls	SET
concat_null_yields_null	SET
isolation level	read committed

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

Usuários associados a uma Rule – Sql Server

Segue comando para identifica os usuários associados a uma Rule:

sp_helpgroup Nome_rule

Exemplo:

sp_helpgroup Usuarios_consulta

Group_name	Group_id	Users_in_group	Userid
Usuarios_consulta	5	fmartinez	1
Usuarios_consulta	5	rcorreia	2
Usuarios_consulta	5	pedroso	3
Usuarios_consulta	5	josineis	4

 

[]s

Query Notification – Sql Server

Query Notification ou notificações de consulta foi introduzido na versão Microsoft SQL Server 2005, permitindo notificar aplicativos quando os dados carregados pela aplicação, forem alterados na base de dados. Este recurso é muito útil para aplicativos que utilizam informações de banco de dados em cache (muito utilizado em aplicativos da Web) e precisam ser notificado quando os dados de origem são alterados.

As aplicações podem tirar proveito do recurso Query Notification para reduzir as idas e vindas ao banco de dados. Em vez de utilizar utilizar processos amarradas a job que são executados periodiacamente, as aplicações podem ser notificadas automaticamente quando os resultados estiverem desatualizados.

Exemplo:

Imagine uma pagina Web que exibe os 10 produtos mais vendidos na última hora, a cada nova requisição da pagina, não é necessário consultar toda a lista de pedido para identificar os mais vendidos. Podemos armazenar essa informação no Cache e utilizar do recurso Query Notification assim que a lista de produtos mais vendidos for alterada na base de dados.

Assim que o aplicativo receber a Notificação, codificamos para que seja executado um Select ou Procedure que recupera os produtos mais vendidos , limpamos e atualizamos o cache.

O Database Engine usa o Service Broker para entregar mensagens de notificação. Portanto, o Service Broker deve estar ativo no banco de dados onde o aplicativo estiver solicitando o serviço.

Para saber mais e como implementar, consulte:  http://msdn.microsoft.com/en-us/library/ms175110(v=sql.105).aspx

[]s

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