Arquivo

Archive for the ‘SQL Server’ Category

SQL Procedure Otimization Tips

Achei bem interessante as dicas de como otimizar suas Stored Procedures.

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/

 

Categorias:SQL Server

if update no SQL Server

Nesse último projeto que estou trabalhando fazemos a integração entre dois sistemas. Para isso criamos triggers no SQL de origem que controla qualquer alteração que precise ser espelhada.

Mas na nossa regra de negócio só queremos inserir na fila de integração se o registro estiver ativo.
Inicialmente utilizamos o comando “if update(NOMEDOCAMPO)”.
O problema é que esse comando simplesmente verifica se o campo sofreu alteração, e não se ele mudou de valor.

Nesse caso mudamos a trigger para funcionar na seguinte forma:

if ((select inserted.FlagAtiva from Inserted) <> 
(Select deleted.FlagAtiva from deleted))

 

Só é importante tomar cuidado que a tabela “inserted” pode conter mais de um registro. Faça uma programação defensiva.

Categorias:SQL Server

SQL Data Backup

Muitas vezes queremos fazer backup (cópia) de um banco de dados SQL Server e também transportar os dados junto. Tem uma ferramenta bem simples que já cria o script completo pra você. Baixe esse programa:  http://go.microsoft.com/fwlink/?LinkId=119368.

Fonte: http://wiki.locaweb.com.br/pt-br/Backup_da_Base_MSSQL_com_Database_Publishing_Wizard

 

Categorias:Ferramentas, SQL Server

SQL Server Dictionary

17/06/2013 1 comentário

Galera, para quem precisa de uma documentação do dicionário de dados do SQL  Server, segue a query prontinha.

select a.name [Table],b.name [Attribute],c.name [DataType],
b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],
CASE WHEN e.parent_object_id
is null THEN '-' ELSE g.name END [Ref Table],
CASE WHEN h.value is null THEN '-' ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT so.id,sc.colid,sc.name
FROM syscolumns sc
JOIN sysobjects so ON so.id = sc.id
JOIN sysindexkeys si ON so.id = si.id
and sc.colid = si.colid
WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e 
on a.id = e.parent_object_id and b.colid = e.parent_column_id
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h 
on a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name

Fonte: CodeProject.com

Categorias:SQL Server

SQL Server – Delete com inner join

Preparei um script que limpa o banco de dados SQL. Em um dos scripts foi necessário fazer um inner join com outra tabela e a solução é bem simples e trivial. Mas não segue o padrão dos outros comandos:

delete produto from produto left join sku
on produto.idProduto = sku.idproduto
where produto.IdProduto IN (select idProduto from #ProdSemCodRef)		
     and (sku.IdSku is null or sku.IdSku not in 
        (select StockKeepingUnitId from skuseller))

Fonte: http://databasically.com/2011/08/31/using-sql-to-delete-rows-from-a-table-using-inner-join/

 

Procedure com tabela temporária

A algumas semanas atrás precisei montar um script para verificar em que nível está cada produto dentro de uma categoria. Vale a pena documentar e lembrar a utilização de tabela temporária.

—————————-

CREATE TABLE #CategoriaNivel (
idCategoria int NOT NULL,
idCategoriaPai int NULL,
ultimoNivel bit NOT NULL)

-- INICIALIZA AS VARIÁVEIS
DECLARE @idcat INTEGER;
DECLARE @idcatPai INTEGER;
DECLARE @idcatTemp INTEGER;
DECLARE @nivel bit;

set @idcat = NULL;
set @idcatPai = NULL;
set @idcatTemp = NULL;
set @nivel = NULL;
-- Declara cursor
DECLARE db_cursorAllCategorias CURSOR FOR
SELECT idCategoria, idCategoriaPai from Categoria with (NOLOCK)

OPEN db_cursorAllCategorias
FETCH NEXT FROM db_cursorAllCategorias INTO @idcat, @idcatpai;

-- LOOP NOS REGISTROS SELECIONADOS
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- COLOQUE AQUI OS COMANDOS SQL DA SUA PROCEDURE

set @idcatTemp = null;

SELECT @idcatTemp = idCategoria from categoria with (NOLOCK) 
where idCategoriaPai = @idcat;

if @idcatTemp is null
BEGIN
set @nivel = 1
END
else
BEGIN
set @nivel = 0
END

INSERT INTO #CategoriaNivel(idCategoria,idCategoriaPai,ultimoNivel) 
VALUES(@idcat, @idcatpai, @nivel)
-- BUSCA AS INFORMAÇÕES DO PRÓXIMO REGISTRO
FETCH NEXT FROM db_cursorAllCategorias INTO @idcat, @idcatpai;
END
-- (FIM DO LOOP NOS REGISTROS SELECIONADOS) ///

-- FECHA E DESTROI O CURSOR
CLOSE db_cursorAllCategorias;
DEALLOCATE db_cursorAllCategorias;

--------

-- Validação
/*
SELECT * from categoria where idCategoriaPai IN(
SELECT idCategoria
FROM #CategoriaNivel
where ultimoNivel =1)
*/
select count(*) TOTPRODUTOSNIVELINTERMEDIARIO from produto
where idCategoria in(
SELECT idCategoria
FROM #CategoriaNivel
where ultimoNivel = 0)
and FlagAtiva = 1

select count(*) TOTPRODUTOSULTIMONIVEL from produto
where idCategoria in(
SELECT idCategoria
FROM #CategoriaNivel
where ultimoNivel = 1)
and FlagAtiva = 1

drop table #CategoriaNivel

—————————-

INSTEAD OF UPDATE SQL 2005

Estava passando por um problema. Precisava criar uma trigger no SQL 2005 que verificava se um campo do tipo “text” tinha sido alterado. Estava recebendo o seguinte erro:

“Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.”

Procurando na internet encontramos que é possível sobrescrever o método de update criando uma trigger “INSTEAD OF UPDATE”.

Ela ficou assim:

CREATE TRIGGER [dbo].[TrProdutoCampoValorUpdate]

ON [dbo].[ProdutoCampoValor]

INSTEAD OF UPDATE

AS

UPDATE ProdutoCampoValor   SET

IdProduto = (SELECT IdProduto FROM Inserted),

IdCampo = (SELECT IdCampo FROM Inserted),

IdCampoValor = (SELECT IdCampoValor FROM Inserted),

Texto = (SELECT Texto FROM Inserted)

WHERE IdProdutocampoValor = (SELECT IdProdutocampoValor FROM Inserted)

IF (UPDATE (Texto))

BEGIN

DECLARE @oldValue nvarchar(max)

DECLARE @newValue nvarchar(max)

SET @oldValue = (SELECT CONVERT(nvarchar(max), Texto) FROM Deleted)

SET @newValue = (SELECT CONVERT(nvarchar(max), Texto) FROM Inserted)

IF (@oldValue != @newValue)

BEGIN

SET NOCOUNT ON;

DELETE ProductFieldIntegration

FROM ProductFieldIntegration AS a

INNER JOIN INSERTED AS b ON a.ProductId = b.IdProduto AND a.FieldId =b.IdCampo

INSERT INTO ProductFieldIntegration (ProductId,FieldId)

SELECT IdProduto , IdCampo FROM INSERTED

GROUP BY IdProduto , IdCampo

END

END

Mais informações em http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148387.

Categorias:SQL Server