kacalapy's questions

Eu tenho as 4 consultas SQL abaixo que combino em um conjunto de resultados no final.

Suponho que, como estou operando nas mesmas tabelas em cada uma das seções SQL, existe uma maneira legal de obter meus resultados de uma consulta SQL unificada.

Atualmente estou fazendo assim:

select 
[date] = CONVERT(DATE, M.crdate), 
[tally sent to smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024 
into #tmp_Attachments_Sent_To_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId 
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =2 and left(mr.EmailAddress,4)='smc+' 
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;
--select * from #tmp_Attachments_Sent_To_smc

select 
[date] = CONVERT(DATE, M.crdate), 
[tally sent from smc w/ attachment] = count(*) ,
[total MB size] =  sum(cast(AD.Size as decimal (10,2)) )/1024/1024 
into #tmp_Attachments_Sent_from_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId 
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =1 and left(mr.EmailAddress,4)='smc+' 
GROUP BY  CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;


select 
[date] = CONVERT(DATE, M.crdate), 
[grand total sent to smc] = count(*) ,
[total MB size sent to smc] =  sum(cast(Size as decimal ) )/1024/1024 
into #tmp_Sent_to_smc
from Message M
where MessageSourceId=2
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;


select 
[date] = CONVERT(DATE, M.crdate), 
[grand total sent from smc] = count(*) ,
[total MB size sent from smc] =  sum(cast(Size as decimal ) )/1024/1024 
into #tmp_Sent_from_smc
from Message M
where MessageSourceId=1
GROUP BY  CONVERT(DATE, crdate)
ORDER BY [date] DESC;


select   A.* ,
         B.[tally sent from smc w/ attachment] ,
         B.[total MB size],
         C.[grand total sent from smc] ,
         C.[total MB size sent from smc],
         D.[grand total sent to smc] ,
         D.[total MB size sent to smc]
from #tmp_Attachments_Sent_To_smc A
join #tmp_Attachments_Sent_from_smc B on A.date = b.date
join #tmp_Sent_from_smc C on A.date = C.date
join #tmp_Sent_to_smc D on A.date = D.date


drop table #tmp_Attachments_Sent_To_smc
drop table #tmp_Attachments_Sent_from_smc
drop table #tmp_Sent_from_smc
drop table #tmp_Sent_to_smc

essa consulta simples resulta no agrupamento dos anexos por dia e me fornece o número total de anexos diários, bem como o tamanho médio?

a única tabela que estou consultando é attachmentdetail (anexos de e-mail)

[attachmentdetail]
    AttachmentId     int
    Name             varchar
    Size             int
    IsInline     bit
    InsertedDatetime datetime

select 
    count(*) as [Tally], 
    avg( cast(size as decimal)/1024/1024) as avgSize,
    DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0) As [date_only]  
from attachmentdetail 
where isinline <>1
group by DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0)
order by [date_only] desc

também como manter os valores que recebo de meu avg () em 2 casas decimais?

Como o SQL Server descobre a ordem dos registros no conjunto de resultados da execução da consulta?

Estou tentando fazer cara ou coroa, mas me pego coçando a cabeça. Quando altero os campos que estou selecionando a ordem também muda. Quando executo o SQL abaixo com a SELECT *, obtenho os mesmos registros, mas em uma ordem muito diferente.

 SELECT TOP (900)
    AD.ATTACHMENTID,
    AD.NAME,
    AD.ISINLINE,
    AD.INSERTEDDATETIME,
    ATMT.ATTACHMENTBLOB,
    U.UFID
  FROM ATTACHMENTDETAIL AD WITH (NOLOCK)
  INNER JOIN MESSAGEATTACHMENT MA ON MA.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN ATTACHMENT ATMT ON ATMT.ATTACHMENTID = AD.ATTACHMENTID
  INNER JOIN MESSAGE MSG ON MSG.ID = MA.MESSAGEID
  INNER JOIN MESSAGEDETAIL MD ON MD.MESSAGEID = MA.MESSAGEID
  INNER JOIN [USER] U ON U.ID = MD.USERID
  LEFT OUTER JOIN XmlExtractionMapping XM ON MA.MESSAGEID = XM.MessageId
    WHERE AD.FILEBOXTOKEN IS NULL 
    AND (XM.XMLEXTRACTIONDATE IS NOT NULL OR 
         (MSG.MESSAGESOURCEID = 1 AND MD.FolderId <> -4))
    AND AD.ISINLINE = 'FALSE'

Eu tenho uma tabela de log que captura o carimbo de data e hora de quando determinados arquivos foram exportados para outro sistema.

A tabela exportedLog atualmente possui três campos:

id                (primary key)
messageId         (int)
exportedDateTime  (datetime)

Revendo isso, descobri que o idcampo não serve para nada, pois não há junções para esta tabela. A única coisa que funciona nesta tabela é a inserção do trabalho em lote que processa as mensagens e insere nesta tabela de log.

Devo remover o idcampo?

Devo ter uma chave primária em um messageIdou em exportedDateTimeambos?

Eu tenho um procedimento armazenado que faz uma atualização simples (veja abaixo).

quero atualizar apenas os campos passados ​​e não os nulos. ou seja, se eu não obtiver um valor parm para algo, não quero atualizar os dados da tabela para nulo, mas apenas deixar o valor atual.

isso pode ser feito em uma instrução de atualização SQL como esta?

ALTER PROCEDURE [dbo].[upd_MessageDetail]  
    @MessageId INT,  
    @IsDraft INT,  
    @IsPreviewed INT,  
    @IsRead INT,  
    @IsFlagged INT,  
    @IsDeleted INT,  
    @IsArchived INT    
AS  
BEGIN  


        UPDATE MessageDetail   
        SET 
            IsDraft = @IsDraft,  
            IsPreviewed = @IsPreviewed,  
            IsRead = @IsRead,  
            IsFlagged = @IsFlagged,  
            IsDeleted = @IsDeleted,  
            IsArchived = @IsArchived   
        WHERE MessageId = @MessageId

Eu tenho duas tabelas: Messageand Attachments(que representam tipos de entidade que têm um relacionamento um-para-muitos) da seguinte forma:

CREATE TABLE message (
    messageId ..., 
    [from]..., 
    [to]..., 
    subject..., 
    body...
);

CREATE TABLE attachments (
    messageId..., 
    filename...
);

Desejo SELECIONAR os dados básicos da mensagem e incluir uma coluna de bits para indicar se a mensagem possui anexos ou não. Estou tentando usar EXISTS na operação mostrada abaixo:

SELECT  
M.*,
hasAttachments = EXISTS(SELECT AttachmentId 
                          FROM Attachment 
                         WHERE messageId = M.messageId),
FROM Message M

Mas isso não funciona - Qual é a maneira de fazer o que estou procurando?

se eu tiver dados de tabela indexados usando pesquisa de texto completo no servidor Microsoft SQL 2005 que inclui dados brutos de tabela, como "teste2", uma consulta usando FTS deve encontrar uma correspondência se a string de pesquisa for "teste"?

Criei uma exibição indexada com alguns campos e criei uma pesquisa de texto completo com base nesses campos.

aqui está a minha opinião:

alter VIEW vFullTextSearch
WITH SCHEMABINDING
AS
    SELECT  M.Id as MessageId, M.Subject , M.Body,MR.EmailAddress, MR.FriendlyName,
            M.Subject + ' ' + M.Body + ' ' + MR.EmailAddress + ' ' + MR.FriendlyName as FTS
    FROM DBO.MESSAGE M
        INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1 
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
    ON vFullTextSearch (MessageId);
GO

Eu indexei todos os campos da tabela.

Estou usando este SQL para encontrar resultados

SELECT M.*
FROM Message AS M
    INNER JOIN FREETEXTTABLE(vFullTextSearch, *, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY];

mas com "John Doe" sendo o [vFullTextSearch.FriendlyName]valor em todos os registros da minha visão, por que não estou obtendo resultados ao pesquisar todos os campos por 'doe'?

Como posso criar um catálogo de pesquisa de texto completo em 3 campos onde um está em uma segunda tabela?

Em relação à maneira mais eficiente de retornar vários agregados em um único procedimento armazenado?

Tenho um aplicativo do tipo e-mail e desejo selecionar todas as mensagens (caixa de entrada) para um usuário. O problema é que eu normalizo a parte do cabeçalho dos e-mails no banco de dados de forma que os dados simples entrem em uma tabela de mensagens e de, para, CC, BCC sejam armazenados em outra tabela.

Qual é a melhor maneira de selecionar mensagens (na íntegra - significando desnormalizar a mensagem completa) para que cada registro contenha todos os campos pertinentes à mensagem, incluindo todos os campos da tabela Message e quaisquer registros relacionados da tabela de destinatários relacionados à mensagem de acordo com o PK/ Relacionamento FK.

Uma coisa em que estou colocando muito peso é a eficiência da solução SQL porque este será o código que será executado muitas vezes e provavelmente será o sql mais executado de todo o banco de dados

Para contextualizar, aqui está uma visão do meu esquema de banco de dados.

Qual é a melhor ou mais eficiente maneira de obter vários valores de resultados agregados?

Basicamente, tenho um aplicativo de e-mail e queria obter todos os números de mensagens para cada tipo de pasta (caixa de entrada, enviada, arquivada, sinalizada...) conforme definido abaixo.

Aqui está uma amostra de coisas que estou tentando fazer em uma chamada de procedimento armazenado. (onde [uid] é o userID)

SELECT * FROM Message
Inbox    WHERE [to]    = [uid] 
Unread    WHERE [to]    = [uid] and isread = 0
Flagged    WHERE [to]    = [uid] and isFlagged = 1
Drafts    WHERE [to]    = [uid] and isDraft = 1
Sent Messages    WHERE [from] = [uid]
Archived Messages    WHERE [to]     = [uid] and isArchived = 1

Tenho uma tabela chamada Pasta com 3 campos: Id (PK), Nome, UserId (FK)

e outra tabela chamada User com dois campos: Id (PK), UserName

Estou tentando executar este sql:

truncate table Folder
truncate table User

Estou tendo o erro a seguir

Cannot truncate table 'Folder' because it is being referenced by a FOREIGN KEY constraint.
Cannot truncate table 'user'   because it is being referenced by a FOREIGN KEY constraint.

... mesmo depois de excluir todos os registros em ambas as tabelas, recebo isso. por que não tenho permissão para truncar minhas tabelas?

como posso redefinir as sementes do PK facilmente sem usar truncate se agora existe uma maneira de executar um truncate nas tabelas de relacionamento pk/fk?

Editar: diagrama de banco de dados adicionado

Qual é uma boa maneira de tornar os procedimentos armazenados robustos o suficiente para que possam ser dimensionados muito bem e também conter tratamento de erros?

Além disso, qual é a melhor maneira de lidar com vários cenários de erro em um procedimento armazenado e ter um sistema de feedback inteligente que retornará informações de erro significativas para os aplicativos de chamada?

Eu tenho uma tabela onde desejo rastrear operações do tipo booleano. além disso, um requisito de negócios é que eu saiba a data da ação e não apenas o valor booleano.

por exemplo, se uma mensagem for lida ou excluída.

Gostaria de usar um campo de data e hora para poder quantificar todas as mensagens não lidas como nulas ou com um valor de data e hora para leitura. Recebo operação booleana e carimbos de data/hora.

Minha única dúvida sobre isso é quão eficiente será a seleção de todas as mensagens não lidas?

Digamos que em uma tabela com alguns milhões de registros e um índice não clusterizado em um campo como read, um select teria um desempenho muito pior do que se o campo fosse um booleano?

Eu quero projetar um aplicativo da Web baseado em banco de dados à prova de balas, tolerante a falhas e queria saber como arquitetá-lo.

o sistema terá uma interface do usuário asp.net, camada intermediária de serviços da web e back-end SQL2005. A interface do usuário e os serviços se comunicarão usando chamadas JSON.

Eu queria saber como garantir que as transações sejam confirmadas e se não for para qualquer erro aparecer e ser registrado. idealmente, a ação deve ser repetida algumas vezes após intervalos de 5 minutos, como faz um aplicativo de e-mail.

Eu estava planejando usar blocos try catch no SQL e queria saber como seria a interface (ou contrato, se preferir) entre os procedimentos armazenados do SQL e os serviços que os chamam. esta interface irá desempenhar 2 funções uma delas é passar parâmetros para que o proc funcione e retorne os resultados esperados. o próximo será para o proc retornar informações de erro. talvez algo como número de erro e mensagem de erro.

meu pântano é como estruturar isso de forma inteligente para que os serviços esperem e reajam de acordo com os dados e informações de erro retornados de procs e lidem com cada um de acordo?

existe uma estrutura para isso porque parece muito clichê?

Uma tabela de muitos para muitos deve ser indexada? Que tipo de índice seria melhor?

Aqui está uma tabela de exemplo:

CREATE TABLE user_role (
  userId INT,
  roleId INT
)

--edit: drachenstern - I added the table def based on the original comments, and assumed ints.

O que é melhor em termos de eficiência do servidor SQL; usar subconsultas ou junções?

Eu sei que as subconsultas não correlacionadas são melhores do que as subconsultas correlacionadas. Mas e as junções?

O SQL se torna mais legível e compreensível usando junções

OUTER JOIN and check for NULLS

mas é pior ou melhor para desempenho do DB?

Estou no meio de um debate sobre se é melhor fazer um PRIMARY KEYde um Identity Columns , ou de um UDF que gera explicitamente um id exclusivo.

• Estou defendendo a Coluna de Identidade.
• Meu sócio está defendendo a geração manual dos valores, ele alega
  • colocando o UDF em outra tabela onde podemos ter um UDF
    • bloquear o recurso
    • incrementar uma tabela de ID com um campo chamado ID_Valuepor1
    • use isso como um identificador exclusivo global
  • Ou faça com que a tabela faça um id+1ao inserir
  • Que é mais simples mover dados entre servidores e/ou ambientes sem a restrição de identificação; mover de um banco de dados onde há dados para outro banco de dados semelhante com, digamos, dados de teste ou fictícios. Para testes em não produção, podemos extrair todos os registros de ontem para a preparação para teste.

Qual implementação faz mais sentido?

Eu tenho trabalhos para aparar tabelas usadas para histórico e registro para mantê-las aparadas. Quero ter certeza de não negligenciar o arquivo de log.

Como e o que posso ou devo fazer para manter o arquivo de log sob controle?

O script sql que tenho em execução em uma programação noturna é:

declare @DBname varchar(500)
set @DBname = 'E:\Database\backup\PMIS_backup_'+ convert(varchar(MAX), getdate(), 23 ) +'.bak'

BACKUP DATABASE [PMIS] TO  DISK = @DBname
WITH NOFORMAT, NOINIT,  NAME = @DBname
, SKIP, REWIND, NOUNLOAD,  STATS = 10

(modo de recuperação é simples)

Eu tenho um banco de dados de produção no servidor SQL e queria dar os retoques finais após a conclusão da funcionalidade. antes de enviá-lo, quero ter certeza de que limpei o banco de dados do servidor SQL e trunquei e reduzi os arquivos de log?

posso executar um trabalho noturno para truncar logs e reduzir arquivos?

isso é o que eu tenho até agora: MEU MODELO DE RECUPERAÇÃO É SIMPLES

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog] como

-- exec sp_helpfile BACKUP LOG PMIS COM TRUNCATE_ONLY

DBCC SHRINKFILE (PMIS, 1)

DBCC SHRINKFILE (PMIS, 1)