O que é SET NOCOUNT ON?

Olá pessoal, ontem aprendi um comando legal e gostaria de compartilhar.
Ao executar uma procedure o SQL Server retorna para a aplicação o total de linhas afetadas.

Ex: (1 row(s) affected)

Esse número pode parecer pequeno, mas se a aplicação executa um procedimento que faz muitas operações ( insert, update, delete ou select) várias vezes durante o dia esse número pode crescer assustadoramente compromentendo o desempenho da aplicação.

O SET NOCOUNT ON é uma instrução sql que desativa a contagem de registros feita em cada instrução SQL dentro de uma procedure.
Isso evita o excesso de tráfego durante a execução da procedure.
Para utiliza-lo basta colocar no começo do procedimento a cláusula SET NOCOUNT ON.

Dica interessante para usar antes de fazer update ou delete

Bom gente eu sou mestra em fazer as coisas no impulso e acabo fazendo coisa errada! Isso era um problema até que me ensinaram a usar o begin tran antes de usar o update, delete ou até mesmo o insert no banco de dados. Sabe aquela coisa de esquecer a cláusula where e dar update na tabela inteira? Pois é, usando o begin tran antes isso não é mais problema.
Faço assim:

--abro uma transação

Begin tran

--verifico quantas transaçãoes estao abertas. Se executei o begin tran uma vez, tenho que ter uma transação apenas aberta.

select @@trancount

--faço o update, delete , insert...

delete from exemplo where id_exemplo = 1

--verifico se fiz tudo certo. Se deu alguma coisa errada:

rollback

--se deu certo:

commit

--verifico se fechou a transação:

select @@trancount

--se tiver fechado retorna 0.

O importante é não esquecer de fechar a transação com o commit no caso de ter dado tudo certo ou o rollback se alguma coisa deu errada.

Restore de uma banco de dados sql 2000

Um dia desses eu precisei criar um procedimento para efetuar o restore do banco de dados de um sistema. A princípio eu criei o procedimento no mesmo banco que eu queria restaurar e logicamente nao funcionou, pois como não tem como restaurar um banco em uso.
Então esse procedimento tem que ser criado em outro banco, no meu caso eu criei no banco Master (banco de dados de sistema). Se esse procedimento for usado em um sistema será necessário criar outra string de conexão com o banco que será utilizado, no meu caso com o banco Master.
Outra observação é limitar o acesso ao banco de dados a um usuário simples. Fazemos isso na seguinte query:
ALTER DATABASE EXEMPLO SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Quando limitamos o acesso a um determinado banco de dados, como single user, estamos eliminando a possibilidade deste banco ser acesso simultaneamente por diversas conexões ou usuários.
Esse procedimento tem como parâmetro de entrada o caminho (diretório) onde se encontra o arquivo de backup.

CREATE PROCEDURE S_RESTORE_EXEMPLO

@ARQUIVO VARCHAR(255)

AS

DECLARE @strSQL varchar(300)

--Limita o acesso ao banco de dados a um usuário simples.

ALTER DATABASE EXEMPLO SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--efetua o restore
set @strSQL = 'RESTORE DATABASE EXEMPLO FROM DISK ='''+ @ARQUIVO +''' WITH FILE = 1, NOUNLOAD, STATS = 10'
exec (@strSQL)

--retorna o acesso ao banco a usuários múltiplos.

ALTER DATABASE EXEMPLO SET MULTI_USER;

Efetuando backup do banco de dados SQL 2000

Hoje vou mostrar como criar um procedimento para efetuar backup completo de um banco de dados sql 2000.
Nesse procedimento o usuário informa o nome que será dado ao arquivo de backup e dentro do procedimento eu informo onde esse backup será salvo.

CREATE PROCEDURE S_BACKUP_EXEMPLO

@NOME VARCHAR(255)

As

DECLARE @CAMINHO VARCHAR(100)

SET @CAMINHO = 'D:\backup_banco\'+@NOME+'.BAK'

BACKUP DATABASE EXEMPLO
TO DISK = @CAMINHO

Verificar maior numero de bytes usado em um campo de um tabela

Olá pessoal,

Em alguma situação talvez seja necessário descobrir o número máximo de bytes utilizado em um campo da tabela.
Por exemplo, tabela Usuario, o campo nome é do tipo text. Quero saber o tamanho da maior string desse campo, ou seja o maio nome.
Isso é simples usando a seguinte query:

SELECT MAX(DATALENGTH(nome) ) as tamanho_maximo FROM Usuario

Resultado:

Update envolvendo duas tabelas

Hoje precisei atualizar uma tabela tomando como base uma segunda tabela e por um momento esqueci como fazer isso.
Então segue no blog para eu não esquecer mais!!!

update atlas_distorcao_estadual
set codigo_municipio = id_municipio
from atlas_distorcao_estadual e ,atlas_municipio_sre m
where e.municipio = m.municipio and e.codigo_sre = m.id_sre

Até a próxima!

Erro na consulta sql: "Cannot resolve collation conflict for equal to operation"

Hoje ao fazer a seguinte consulta entre duas tabelas , uma tabela normal e outra temporária comparando um campo texto apareceu a seguinte mensagem de erro no sql:"Cannot resolve collation conflict for equal to operation".

SELECT *
FROM #gabarito g , opcao o
WHERE g.texto= o.texto

Nunca tinha visto esse erro. Pesquisando na net encontrei a seguinte explicação:
No novo ambiente, as tabelas vão assumir o collation default, a não ser quando for explicito no campo.Mas o mesmo não acontece com as tabelas temporárias.
O collation dos dois campos que devem ser comparados vão ser diferentes e o servidor vai retornar uma mensagem de erro.

A solução:

SELECT *
FROM #gabarito2 g , opcao o
WHERE g.texto COLLATE collate_do_banco = o.texto COLLATE collate_do_banco

Para descobrir qual o collate do banco:

SELECT SERVERPROPERTY ('Collation')

Usando as funções SUBSTRING , CHARINDEX e LEN

Substring e Charindex são funções de manipulação de strings.

CHARINDEX - Retorna a posição inicial onde a string ("String a ser pesquisada") foi encontrada no texto. Se a tring não for encontrada, a função retorna zero.

Sintaxe: Charindex("String a ser pesquisada",texto)
Exemplo: Select Charindex('a', 'Brasil')
Resultado: 3

SUBSTRING - Retorna uma string com o tamanho definido em "posição final", extraída da string "texto", a partir da "posição inicial".

Sintaxe: Substring("texto", "posição inicial", "posição final")
Exemplo: Select Substring('Brasil hexa campeão', 1, 6)
Resultado: Brasil

LEN - Retorna o tamanho da string informada.

Sintaxe: LEN (string)
Exemplo: Select Len('A copa do mundo é nossa')
Resultado: 23

Um exemplo utilizando as três funções:

Tenho uma coluna (ano) em uma tabela (Copa_Mundo) que possui dados da seguinte forma: 2008_2010 .
Preciso fazer uma consulta que traga esses dados a partir do primeiro caracter depois do '_' até o final da string.

Ficaria assim:

SELECT substring(ano, (charindex('_', ano)+1), LEN (ano)) AS Ano
from Copa_mundo

Resultado:

Como transformar linhas em colunas no SQL 2000?

Essa dica é bem interessante, pois o sql 2000 não possui a função PIVOT que é utilizada no SQL 2005.
Então vamos lá.Eu tenho a seguinte tabela:







Eu preciso retornar os dados assim:





A minha sugestão é a seguinte:

CREATE TABLE #Produto(
id_produto INT,
cor VARCHAR(10),
qtde INT
)

INSERT INTO #Produto (id_produto,cor,qtde) VALUES (1030,'azul',10)
INSERT INTO #Produto (id_produto,cor,qtde) VALUES (1030,'verde',5)
INSERT INTO #Produto (id_produto,cor,qtde) VALUES (1030,'amarelo',3)

SELECT DISTINCT id_produto,MAX(azul) AS azul,MAX(verde) AS verde,MAX(amarelo) AS amarelo FROM (
SELECT id_produto,
azul = ( CASE cor WHEN 'azul' THEN QTDE END),
verde = ( CASE cor WHEN 'verde' THEN QTDE END),
amarelo= ( CASE cor WHEN 'amarelo' THEN QTDE END)
FROM #produto
) AS Tabela
GROUP BY ID_produto

DROP TABLE #produto

Resultado:

Trabalhando com campos datetime

Como separar a data da hora de um campo datetime?
Segue:

Declare @date datetime
Set @date = getdate()
Select Convert (CHAR(10),@date,103) As Data, CONVERT(CHAR(5),@date,108) AS HORA

Resultado:

E para remover a hora de um campo datetime?

Declare @date datetime
Set @date = getdate()
Select Cast(floor(cast(@date as float)) as datetime) as data

Resultado:

Como listar um campo de forma aleatória no SQL ?

Certa vez precisei criar códigos de forma aleatória e uma boa opção foi usar o seguinte comando:

SELECT (CAMPO)
FROM TABELA
ORDER BY NEWID()

Ex: SELECT COD_ALUNO FROM ALUNO ORDER BY NEWID()

Comando para listar procedures do banco de dados

O seguinte comando lista as procedures, a data de criação e a data da última alteração:

SELECT SPECIFIC_NAME, LAST_ALTERED,CREATED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = 'DBO'
ORDER BY LAST_ALTERED DESC

Como resetar campos auto incremento no SQL Server

Muitas vezes quando um banco de dados é criado, antes de finalizar o projeto, é necessário testar se os dados estão sendo armazenados corretamente e então são realizados vários testes.
Se um campo de uma tabela for definido como auto incremento, depois dos testes finalizados os dados possivelmente serão excluídos e pode haver a necessidade de zerar esse campo auto incremento.
No SQL Server utiliza-se o seguinte comando:

DBCC CHECKIDENT ('nome da tabela', RESEED, 0)

Onde 0 é o valor inicial -1. Sendo assim, para que o registro comece do 1 deve-se setar o valor para 0. Para começar do 5, o valor deve ser setado para 4.