Royi Namir's questions

Eu tenho uma Imagestabela onde essa tabela tem essas colunas básicas -

ImageId (int)
VotesUp (int)
VotesDown (int)

Cada imagem pode receber votos positivos (up, down, retrair vote)

A tabela de upvotes tem:

ImageId (int)
UserId (int)
Score (int )   [-1,0,+1]

Então se -

• Um usuário insere uma pontuação de +1 então (se não existir) eu adiciono uma nova linha à upvotestabela e atualizo o valor na Imagestabela (para busca rápida)

• Um usuário altera sua pontuação de +1 para 0 e depois diminuivotes up

• Um usuário altera sua pontuação de +1 para -1 e depois diminui votes upe aumenta votes down
• Um usuário altera sua pontuação de 0 para +1 e depois aumentavotes up

• Um usuário altera sua pontuação de 0 para -1 e depois aumentavotes down

• Um usuário altera sua pontuação de -1 para 0 e depois diminuivotes down

• Um usuário altera sua pontuação de -1 para +1 e depois diminui votes down e aumentavotes up

É uma lógica bem simples.

Aqui está o gatilho na upvotestabela:

alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1  FROM   DELETED ) --update
BEGIN

UPDATE imgs
SET    VotesUp = CASE

                    WHEN deleted.Score = 1 AND INSERTED.score =0      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 0 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    ELSE ISNULL(VotesUp, 0)
                END  
                ,
    VotesDown = CASE 

                    WHEN deleted.Score = 0 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesDown, 0) -1
                    WHEN deleted.Score = -1 AND INSERTED.score =0      THEN ISNULL(VotesDown, 0) -1
                    ELSE ISNULL(VotesDown, 0)
                END   
FROM   Images imgs
    JOIN DELETED 
        ON  imgs.ImageId = deleted.ImageId
        JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId


END
ELSE
--insert
BEGIN
UPDATE imgs
SET    VotesUp = CASE 
                    WHEN INSERTED.Score = 1 
                    THEN ISNULL(VotesUp, 0) + 1
                    ELSE VotesUp
                END,
    VotesDown = CASE 
                    WHEN INSERTED.Score = -1 
                        THEN ISNULL(VotesDown, 0) + 1
                    ELSE VotesDown
                END
FROM   Images imgs
    JOIN INSERTED
        ON  imgs.ImageId = INSERTED.ImageId
END
END

Está funcionando como esperado.

Então onde está o problema ?

Pergunta :

Acho que esse gatilho pode ser simplificado sem todos esses if's/cases . Não consigo ver a simplificação.

É possível simplificar este gatilho?

(Pergunta movida do SO)

Eu tenho uma tabela (dados fictícios) com índice clusterizado contém 2 colunas:

Agora eu executo essas duas consultas:

declare 
@productid int =1 , 
@priceid  int = 1




SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)  


SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

O plano de execução real para ambas as consultas é:

Como você pode ver, o primeiro está usando SCAN enquanto o segundo está usando SEEK.

No entanto - adicionando OPTION (RECOMPILE)à primeira consulta , fez o plano de execução também usar SEEK :

Amigos no bate-papo do DBA me disseram que:

Em sua consulta, @productid=1, o que significa que (productID=@productID OU @productID IS NULL) pode ser simplificado para (productID=@productID). O primeiro requer uma varredura para funcionar com qualquer valor de @productID, o último pode usar uma busca. Então, quando você usa RECOMPILE, o SQL Server vai olhar qual valor você realmente tem em @productID e fazer o melhor plano para isso. Com um valor não nulo em @productID, uma busca é melhor. Se o valor de @productID for desconhecido, o plano deve se adequar a qualquer valor possível em @productID, o que exigiria uma varredura. Esteja avisado: OPTION (RECOMPILE) forçará uma recompilação do plano toda vez que você executá-lo, o que adicionará alguns milissegundos a cada execução. Embora isso seja apenas um problema se a consulta for executada com muita frequência.

Também :

Se @productID for nulo, qual valor você procuraria? Resposta: não há nada para buscar. Todos os valores se qualificam.

Eu entendo que OPTION (RECOMPILE)força o SQL Server a ver quais valores reais os parâmetros têm e ver se ele pode SEEK com ele.

Mas agora perco o benefício da compilação antecipada.

Pergunta

IMHO-SCAN só ocorrerá se um parâmetro for nulo.
Tudo bem - deixe o SQL SERVER criar um plano de execução para SCAN.
MAS se o SQL Server vê que eu executo essa consulta muitas vezes com valores: 1,1, então por que ele não cria OUTRO plano de execução e usa SEEK para isso?

AFAIK-SQL cria plano de execução para as consultas mais acessadas .

• Por que o SQL SERVER não salva um plano de execução para:

  @productid int =1 , @priceid int = 1

(Eu corro muitas vezes com esses valores)

• É possível forçar o SQL a manter esse plano de execução (que usa SEEK) - para invocação futura?

Script completo de criação de tabela + dados

Eu tenho esta tabela que representa a hierarquia:

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

A estrutura é:

a
+----ab
+----ac
     +---aca
+----ad

(A siblingOrdercoluna é para controlar a ordem de ab, ac, ad )

Eu já tenho essa solução que usa o siblingOrder:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Então order by PATH, na verdade, faça o trabalho:

O problema:

O problema é que devo (!) colocar valores no siblingOrderpara que funcione!

Caso contrário, por exemplo:

Se eu colocar 0tudo siblingOrderisso é o resultado:

(sim, agora classificando por caminho - não funciona...)

Eu preciso que acasempre estará sobac

(A única razão pela qual adicionei siblingOrderé para pedir irmãos!) e não quero forçar a adição siblingOrderquando não for necessário

Pergunta :

É possível aprimorar a consulta para que siblingOrderafete apenas os irmãos?

Quero dizer , se eu não me importo com a ordem dos irmãos ( colocando 0) , ainda espero acaque eles estejam sobac

Sqlonline: com siblingOrder

Sqlonline - sem pedido de irmão

Digamos que eu tenha um site mundial (quero dizer que tem usos de todo o mundo).

Eu tenho 1 servidor que está na Alemanha (em alguma cidade). nunca será movido da Alemanha - nunca.

Minha pergunta é :

Quando os usuários salvam dados, preciso salvar a data da ação:

Devo salvá-lo (e acho que sim) como UTC?

Ou desde - o servidor está em um local - não há problema em salvar todas as datas, GetDATE() pois o ponto da relatividade é o mesmo ponto.

Estou errado ? Se eu fizer isso, você pode fornecer um cenário simples onde vai falhar?

Estou usando o SQL Server 2008 R2 e tenho esta pseudo consulta (SP):

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

O problema é que a consulta demora muito para ser executada - mesmo que eu execute o SP com @LinkMode=2.

Como você notou, a consulta de execução longa deve ser executada apenas se @LinkMode for nulo, o que não é o caso aqui. No meu caso @LinkMode = 2!

No entanto, se eu mudar para:

 select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

o SP corre rápido.

Já ouvi falar que às vezes o otimizador pode otimizar a ordem dos critérios.

Então eu pergunto :

• Mesmo que o otimizador escolha uma rota diferente, o que pode ser mais rápido do que verificar se =null? Quero dizer, acho que verificar if a==nullé muito mais rápido do que executar a outra consulta longa ...

• Como posso forçar o SQL Server a executar a consulta conforme a escrevi (na mesma ordem)?

Não entendo :

Eu instalei o SQL Server 2008 R2. o processo de instalação me pediu o nome da instância desejada, então escrevi: MSSQLSERVER.

após a instalação, a execução do Relatório de descoberta de configuração mostra o nome da instância:

Também instalei todos os service packs.

Além disso, todos os serviços estão ativos:

Além disso, todas as portas estão abertas (tcp:1433 + udp:1434)

Além disso, os pipes nomeados estão em:

Então onde está o problema ?

Não consigo me conectar por meio do nome da instância:

Também incapaz com:

user\MSSQLSERVERonde useré o nome do computador.

Também incapaz com:.\MSSQLSERVER

Mas (estranho):

Esses 3 funcionam :

• user( "usuário" é o nome da minha máquina)
• (local)
• .

Pergunta :

por que não posso usar meu nome de instância para conectar ao meu servidor sql?

Informações relacionadas:

Depois de conectar por meio de um dos 3 que trabalham, executo uma informação útil:

Eu tenho este código:

Select name, age , row_number() over (partition by name order by age desc) rn from my table

(objetivo: selecionar o cara mais velho entre os mesmos nomes)

Se eu quiser selecionar todos os registros numerados de linha 1, acho que devo fazer:

Select ... from ( above query) where rn=1

Mas isso me obriga a envolvê-lo com outro select.

Existe alguma outra solução em vez de embrulhar select? (cte também é como embrulhar)

Se eu escrever no SQL Server:

SELECT number FROM master..spt_values WHERE Type = 'P' ORDER BY Number

Obtenho uma lista de números sequenciais que já posso usar, ao invés de escrever uma tabela com números sequenciais.

Isso pode ser muito útil para pequenas junções, numeração de linhas, etc.

Eu queria saber se existem outras tabelas ocultas como esta que podem me ajudar a fazer outras coisas também. Por exemplo, posso querer select someNonImportantTextColmn from master..unknowne aplicar uma função minha.

Onde posso encontrar uma lista completa dessas " tabelas de ajuda "?

Olhando para

update MyTable
set Status = 1
where Status = 0

claro, o whereé calculado antes da atualização real.

mas será que essa filtragem ( where Status = 0) também fica dentro da fechadura ?

Quero dizer, onde está a fechadura?

aqui :update ...

ou aqui : filter and update...

Eu quero monitorar apenas sp's.

O SQL Server Profiler escuta todos os eventos e filtra os dados desejados (por exemplo, sp's)?

Ou melhor, o sql emite apenas eventos pré-configurados do Profiler?

Estou usando o sql server 2008 r2.

Eu loguei no ssms que por sua vez se conecta a uma máquina remota do servidor sql.

Estou escrevendo uma consulta que grava um arquivo.

Eu preciso saber - para quais permissões do Windows devo concederc:\myfolder

Existe algum select queryque possa me fornecer a conta do Windows que finalmente grava o arquivo?

Eu tenho uma tabela no Microsoft SQL Server. Às vezes preciso atualizar e às vezes preciso inserir. Eu poderia escrever 2 procedimentos armazenados:

InsertNewPerson
UpdatePertsonById

Mas eu estava pensando em escrever 1 procedimento armazenado ( SetPerson) que faria as duas coisas (se houver um ID, é uma updateoperação , caso contrário insert).

Devo criar um procedimento armazenado (apenas um para manter) ou devo criar dois procedimentos armazenados diferentes?

Depois de perguntar isso , ainda tenho uma pergunta.

Uma empresa precisa armazenar dados confidenciais. Por favor, não vamos falar sobre certificado agora.

Um DBA faz o seguinte:

  CREATE SYMMETRIC KEY SecureSymmetricKey1 
  WITH ALGORITHM = DESX
  ENCRYPTION BY PASSWORD = N'blabla';

Um programador deseja criptografar dados e faz o seguinte:

  -- open key
  OPEN SYMMETRIC KEY SecureSymmetricKey1
  DECRYPTION BY PASSWORD = N'blabla'

  -- actual encrpyt
  DECLARE @encrypted_str VARBINARY(MAX)
  SET @encrypted_str =  EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');

Outro programador quer LER os dados, então ele faz:

  DECLARE @decrypted_str VARBINARY(MAX)
  SET @decrypted_str = DecryptByKey(...encrypted_str...) 

Tudo bem.

Perguntas :

1. Quando um programador abre uma chave simétrica, ele deve saber a senha. Não acho que um programador deva saber a senha. Como isso pode ser resolvido ?

2. Se um hacker GOD obtiver o .BAKarquivo inteiro e restaurar o backup em sua própria máquina - ele poderá visualizar a fonte do procedimento armazenado que um dos programadores escreveu e ver a senha. e então o HACKER pode fazer:

   ABRIR CHAVE SIMÉTRICA SecureSymmetricKey1 DESCRIPTAÇÃO POR SENHA = N'blabla'

O que estou perdendo ?

Obrigado por ajudar.

Então, eu estava perguntando no chat sobre a segurança dos arquivos de backup do SQL Server.

Disseram-me que, se um invasor tiver acesso ao arquivo .bak não criptografado, poderá ter acesso aos dados.

Então, vamos olhar para este cenário:

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

aviso - não há senha aqui.

e então criptografamos nossas tabelas por:

UPDATE tbl1
 SET namePAss = ENCRYPTBYKEY(KEY_GUID('MySymetricKey'),name)
GO

Agora digamos que um hacker pegou meu bakarquivo. Tudo o que ele precisa fazer para visualizar os dados (em seu próprio computador e sql-server) é:

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM tbl1

Ele ainda seria capaz de acessar os dados?

Do msdn :

Ao contrário de uma tabela derivada, uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.

Estou usando bastante CTEs, mas nunca pensei profundamente sobre os benefícios de usá-los.

Se eu fizer referência a um CTE várias vezes na mesma consulta:

• Existe algum benefício de desempenho?
• Se eu estiver fazendo uma autojunção, o SQL Server verificará as tabelas de destino duas vezes?

Eu tenho um .mdfarquivo de 10 GB com 250 MB .LDF.

Eu quero diminuir os 10 GB .mdf(se possível)

Eu li sobre o comando dbcc, mas li no contexto de encolher .LDF(em qualquer tamanho desejado usando truncar).

No entanto, quero reduzir o .MDFarquivo sem perder dados.

Qual é o comando preciso para isso?

1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

Estou tentando descobrir sobre a criptografia do SQL Server.

• depois de executar o código acima, existe alguma maneira de descobrir mais tarde qual é o valor da senha para o SecureSymmetricKey?

2)

Se agora estou fazendo isso com Certificates: Sou o administrador e criei:

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

só eu sei a senha.

mais tarde eu :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

até agora, está tudo bem.

Agora, quando um hacker chega ao computador, tudo o que ele precisa fazer é :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

e depois :

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

então onde está a proteção em certificados? Ninguém pediu uma senha a ele (como na criptografia de senha (como na minha primeira pergunta) ...? Ele só precisava saber o nome do certificado

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

e não é um problema descobrir qual é o nome do certificado ... Então, onde está a proteção em decifrar dados do hacker?

1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

Estou tentando descobrir sobre a criptografia do SQL Server.

• depois de executar o código acima, existe alguma maneira de descobrir mais tarde qual é o valor da senha para o SecureSymmetricKey?

2)

Se agora estou fazendo isso com Certificates: sou (o administrador) criado

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

só eu sei a senha.

mais tarde eu :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'


CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

até agora, está tudo bem.

agora, quando um hacker chega ao computador, tudo o que ele precisa fazer é :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

e depois :

SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

então, onde está a proteção nos certificados? ninguém pediu uma senha a ele (como na criptografia de senha (como na minha primeira pergunta) ...? ele só precisava saber o nome do certificado

ABRIR CHAVE SIMÉTRICA MySymetricKey DECRIPTOGRAFIA POR CERTIFICADO MyCertificate

e não é um problema descobrir qual é o nome do certificado ... Então, onde está a proteção em decifrar dados do Hacker?

3)

quando estou usando

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';


DECLARE  @str NVARCHAR(100)
SET @str = 'lala';

OPEN SYMMETRIC KEY SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongPassword';

De quem estou tentando proteger os dados? os dados sendo enviados do cliente para o servidor? (os dados estão sendo enviados por texto simples - não consigo ativar os comandos SQL antes de enviar os dados...) ou pessoas que tenham acesso ao servidor SQL?