kacalapy's questions

我有下面的 4 个 SQL 查询，最后将它们组合成一个结果集。

我猜测，因为我在每个 SQL 部分中对相同的表进行操作，所以有一种很酷的方法可以从一个统一的 SQL 查询中获取结果。

目前我是这样做的：

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

这个简单的查询是否会导致按天对附件进行分组并给我每日附件的总数以及平均大小？

我查询的唯一表是附件详细信息（电子邮件附件）

[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

还如何将我从我的 avg() 获得的值保留到小数点后 2 位？

SQL Server 如何计算查询执行结果集中记录的顺序？

我试图对此做出正面或反面，但发现自己摸不着头脑。当我更改字段时，我选择的顺序也会更改。当我用 a 执行下面的 SQL 时，SELECT *我得到了相同的记录，但顺序却大不相同。

 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'

我有一个日志表，它捕获某些文件何时导出到另一个系统的日期时间戳。

exportLog 表目前有三个字段：

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

回顾这一点，我发现该id字段没有任何用途，因为该表没有连接。唯一在这个表上工作的是批处理作业的插入，它处理消息并插入到这个日志表中。

我应该删除该id字段吗？

messageId我应该在其中一个或exportedDateTime两个上都有主键吗？

我有一个执行简单更新的存储过程（见下文）。

我只想更新传递的字段，而不是那些为空的字段。也就是说，如果我没有得到某个参数的值，我不想将表数据更新为 null，而只是保留当前值。

这可以在这样的 sql update 语句中完成吗？

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

我有两个表：Message和Attachments（表示具有一对多关系的实体类型）如下：

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

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

我想选择基本消息数据并包含一个位列来表示消息是否有附件。我正在尝试在如下所示的操作中使用 EXISTS：

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

但这不起作用 - 做我正在寻找的方法是什么？

如果我有在 Microsoft SQL Server 2005 中使用全文搜索索引的表数据，其中包括原始表数据，例如“test2”，如果搜索字符串是“test”，使用 FTS 的查询是否应该找到匹配项？

我创建了一个包含几个字段的索引视图，我基于这些字段创建了全文搜索。

这是我的观点：

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

我索引了表的所有字段。

我正在使用此 SQL 来查找结果

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

但是“John Doe”是[vFullTextSearch.FriendlyName]我认为所有记录中的值，为什么在搜索所有字段中的“doe”时我没有得到结果？

如何在其中一个位于第二个表中的 3 个字段上创建全文搜索目录？

关于在单个存储过程中返回多个聚合的最有效方法？

我有一个电子邮件类型的应用程序，想为用户选择所有消息（收件箱）。问题是我将电子邮件的标题部分规范化到数据库中，以便平面数据进入消息表，而从、到、CC、BCC 存储到另一个表。

选择消息的最佳方法是什么（完整 - 意味着对完整消息进行非规范化），以便每条记录包含所有消息相关字段，包括所有消息表字段以及收件人表中与消息相关的任何相关记录，根据 PK/ FK 关系。

我非常重视的一件事是 SQL 解决方案的效率，因为这将是多次执行的代码，并且可能是整个数据库中运行次数最多的 sql

对于上下文，这里是我的数据库模式的视图。

获取多个聚合结果值的最佳或最有效方法是什么？

基本上我有一个电子邮件应用程序，想要获取每种类型的文件夹（收件箱、已发送、已存档、已标记...）的所有消息数量，如下定义。

这是我在一个存储过程调用中尝试做的事情的示例。（其中 [uid] 是用户 ID）

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

我有一个名为 Folder 的表，其中包含 3 个字段：Id (PK)、Name、UserId (FK)

另一个名为 User 的表有两个字段：Id (PK), UserName

我正在尝试运行此 sql：

truncate table Folder
truncate table User

我收到以下错误

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.

...即使在删除两个表中的所有记录后，我也得到了这个。为什么我不允许截断我的表格？

如果现在可以在 pk/fk 关系表上运行截断，我如何在不使用截断的情况下轻松重置 PK 的种子？

编辑：添加了数据库图

什么是使存储过程足够健壮以使其可以很好地扩展并且还包含错误处理的好方法？

此外，在存储过程中处理多个错误场景并拥有一个智能反馈系统，将有意义的错误信息返回给调用应用程序的最佳方法是什么？

我有一张表，我想在其中跟踪布尔类型的操作。此外，业务要求是我知道操作的日期，而不仅仅是布尔值。

例如，如果一条消息被阅读或删除。

我想使用日期时间字段，以便我可以将所有未读消息量化为具有空值或日期时间值以供读取。我得到布尔运算和时间戳。

我对此唯一的疑虑是选择所有未读消息的效率如何？

假设在具有几百万条记录和字段上的非聚集索引（例如读取）的表中，选择的执行方式会比字段为布尔值时更差吗？

我想设计一个防弹、容错、数据库驱动的 Web 应用程序，并且想知道如何构建它。

该系统将有一个 asp.net 用户界面、web 服务中间层和 SQL2005 后端。UI 和服务将使用 JSON 调用进行通信。

我想知道如何确保事务被提交，如果没有任何错误冒泡并被记录下来。理想情况下，每隔 5 分钟重试几次操作，就像电子邮件应用程序一样。

我正计划在 SQL 中使用 try catch 块，并且想知道 SQL 存储过程和调用它们的服务之间的接口（或合同，如果你愿意的话）会是什么样子。该接口将扮演 2 个角色，一个是为 proc 传递参数以运行并返回预期结果。接下来是proc返回错误信息。也许像错误号和错误消息之类的东西。

我的难题是如何智能地构建它，以便服务期望并相应地响应从 proc 返回的数据和错误信息，并相应地处理每一个信息？

是否有一个框架，因为它看起来很样板？

是否应该索引多对多表？什么样的索引最好？

这是一个示例表：

CREATE TABLE user_role (
  userId INT,
  roleId INT
)

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

在 SQL Server 效率方面有什么更好的；使用子查询或连接？

我知道不相关的比相关的子查询更好。但是加入呢？

使用连接使 SQL 变得更具可读性和可理解性

OUTER JOIN and check for NULLS

但它对数据库的性能来说是更糟还是更好？

我正在辩论是否更好地PRIMARY KEY使用Identity Columns，我们使用显式生成唯一 ID 的 UDF。

• 我正在为身份列争论。
• 他声称，我的伙伴正在争论手动生成值
  • 通过将 UDF 放在另一个可以有 UDF 的表上
    • 锁定资源
    • 增加一个 ID 表，其中一个字段ID_Value由1
    • 将此用作全局唯一标识符
  • 或者让表格id+1在插入时执行
  • 在没有识别约束的服务器和/或环境之间移动数据更简单；从一个有数据的数据库移动到另一个类似的数据库，比如说暂存或虚拟数据。对于非生产环境中的测试，我们可能希望将昨天的所有记录拉下来进行测试。

哪种实现更有意义？

我有适当的工作来修剪用于历史记录和日志记录的表，以保持它们的修剪。我想确保我不会忽略日志文件。

我如何以及应该做什么来检查日志文件？

我每晚运行的 sql 脚本是：

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

（恢复模式很简单）

我在 SQL Server 中有一个生产数据库，想在功能完成后进行最后的润色。在发货之前，我想确保我在 SQL Server 数据库中进行了一些清理并截断和收缩日志文件？

我可以每晚运行一个作业来截断日志和收缩文件吗？

这是我目前所拥有的： 我的恢复模型很简单

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog] 作为

-- exec sp_helpfile 使用 TRUNCATE_ONLY 备份日志 PMIS

DBCC 收缩文件 (PMIS, 1)

DBCC 收缩文件 (PMIS, 1)