将 MSSQL 表传输到不带时间戳列的内存中 OLTP 技术

Question

>由于MS SQL Server Express 2016免费提供新的内存中OLTP，我想将我的表转移到新技术。这样做，我遇到了一个问题，因为我正在使用时间戳列来查找一个人的最新数据集 - 例如最新的电子邮件地址。

为了演示问题，您需要生成 3 个表。第一个为人：

CREATE TABLE [dbo].[persons](
[Pers_ID] [int] IDENTITY(1,1) NOT NULL,
[Pers_surname] [nvarchar](100) NULL,
[Pers_forename] [nvarchar](100) NULL,
[Pers_birthdate] [datetime] NULL,
[Pers_last_change] [datetime] NULL,
[Pers_changed_by] [nvarchar](100) NULL,
[Pers_Timestamp] [timestamp] NULL,
CONSTRAINT [PK_persons] PRIMARY KEY CLUSTERED 
([Pers_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,     
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

第二个用于电子邮件地址：

CREATE TABLE [dbo].[emailadress](
[Email_ID] [int] IDENTITY(1,1) NOT NULL,
[Email_adress] [nvarchar](255) NULL,
[Email_up_to_date] [bit] NOT NULL,
[Email_last_change] [datetime] NULL,
[Email_changed_by] [nvarchar](100) NULL,
[Email_Timestamp] [timestamp] NULL,
CONSTRAINT [PK_emailadress] PRIMARY KEY CLUSTERED 
([Email_ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[emailadress] ADD  CONSTRAINT 
[DF_emailadress_up_to_date]  DEFAULT ((1)) FOR [Email_up_to_date]
GO

最后一个用于前两个表之间的链接：

CREATE TABLE [dbo].[PersEmail](
[PersEmail_ID] [int] IDENTITY(1,1) NOT NULL,
[Pers_ID] [int] NULL,
[Email_ID] [int] NULL,
[PersEmail_out_of_date] [bit] NOT NULL,
[PersEmail_last_change] [datetime] NULL,
[PersEmail_changed_by] [nvarchar](255) NULL,
[PersEmail_Timestamp] [timestamp] NULL,
CONSTRAINT [PK_PersEmail] PRIMARY KEY CLUSTERED 
([PersEmail_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] 
GO

每个人都可以有多个电子邮件地址，一个电子邮件地址可以链接到几个人。如果电子邮件地址已过期，则有一个触发器会将连接设置为过期。

对于很多报告，我只需要一个人的电子邮件地址。这应该是最新的电子邮件地址。为了找出哪个是最新的，我使用时间戳列：

SELECT P.Pers_ID, Pers_surname, Pers_forename, Pers_birthdate, 
PersEmail_ID, Email_adress
FROM persons P
LEFT OUTER JOIN
(SELECT * FROM dbo.PersEmail WHERE PersEmail_out_of_date = 0) AS PE 
ON PE.Pers_ID = P.Pers_ID 
LEFT OUTER JOIN
(SELECT * FROM dbo.emailadress WHERE Email_up_to_date = 1) AS email 
ON email.Email_ID = PE.Email_ID
WHERE P.Pers_ID = @intID AND
((PE.PersEmail_Timestamp IS NULL) OR
(PE.PersEmail_Timestamp = ( SELECT MAX(PersEmail_Timestamp) AS Expr1
FROM (SELECT * FROM dbo.PersEmail WHERE PersEmail_out_of_date = 0) AS 
persemail2
WHERE       Pers_ID = P.Pers_ID)))
RETURN

我尝试使用 datetime2(7) 列而不是时间戳列。但是，此人可能同时使用"更新"语句更改了两个电子邮件地址。datetime2 列现在将包含两个相同的日期时间。然后我的查询将显示两行而不是一行。相比之下，时间戳列提供两种不同的内容，而我的查询仅按预期提供一行。

有谁知道如何替换所描述目标的时间戳列？

如果有独立于内存中问题的建议来改进查询以找出最新的电子邮件地址，也请告诉我。

注意：我希望上面引用的代码是正确的。我从德语翻译了列名，并尝试使用英文版本。原始代码可以正常工作...如果我忽略了一个错误，请告诉我。

最后，这段代码似乎对我有用，但我不确定，它是最好的：

SELECT      P.Pers_ID, Pers_surname, Pers_forename, Pers_birthdate, 
PE.PersEmail_ID, Email_adress
FROM        persons P
LEFT OUTER JOIN
(SELECT * FROM PersEmail WHERE PersEmail_out_of_date = 0) AS PE 
ON  PE.Pers_ID = P.Pers_ID 
LEFT OUTER JOIN
(SELECT * FROM emailadress WHERE Email_up_to_date = 1) AS email ON 
email.Email_ID = PE.Email_ID
WHERE       P.Pers_ID = @intID AND
((PE.PersEmail_TS IS NULL) OR
(PE.PersEmail_ID =  (   SELECT TOP 1    PersEmail_ID
FROM        (SELECT * FROM dbo.PersEmail WHERE PersEmail_out_of_date = 0) 
AS persemail3       
WHERE       Pers_ID = @intID
AND PersEmail_TS = (    SELECT      MAX(PersEmail_TS) AS Expr1
FROM        (SELECT * FROM dbo.PersEmail WHERE PersEmail_out_of_date = 0)  
AS persemail2
WHERE       Pers_ID = @intID))))

Answer 1

如果您在一个 UPDATE 语句中更新同一个人的两个电子邮件地址，则无法保证这些电子邮件地址中的哪一个将收到最高的时间戳。 @Jeroen Mostert在评论中也提到了这一点。

当您移动到内存中 OLTP(不支持时间戳数据类型)时，使用 datetime2(7) 列应该没问题。您必须对查询进行细微更改，以确保不会收到多行。

查询重写的一个建议是：

SELECT TOP 1
P.Pers_ID, Pers_surname, Pers_forename, Pers_birthdate, PersEmail_ID, Email_adress
FROM persons P
LEFT OUTER JOIN dbo.PersEmail PE 
ON PE.Pers_ID = P.Pers_ID 
AND PE.PersEmail_inaktuell = 0
LEFT OUTER JOIN dbo.emailadress email 
ON email.Email_ID = PE.Email_ID 
AND email.Email_up_to_date = 1
WHERE P.Pers_ID = @intID 
ORDER BY PE.PersEmail_Timestamp DESC, PE.Email_ID DESC

此查询仍可确保您只收到 1 条记录，并且收到的记录是最新更新的记录之一。 即，如果 PersEmail 表中的两条或更多条记录在同一 UPDATE 语句中更新，那么您将收到Email_ID最高的记录。

Answer 2

这段代码终于对我有用了。感谢您使用"TOP 1"的提示：

SELECT      P.Pers_ID, PE.PersEmail_ID, PE.Email_ID, Email_Adress
FROM        persons P
INNER JOIN
(SELECT * FROM PersEmail WHERE PersEmail_out_of_date = 0) AS PE ON  PE.Pers_ID = P.Pers_ID 
INNER JOIN
(   SELECT      * 
FROM        emailadress
WHERE       Email_up_to_date = 1
)   AS E ON E.Email_ID = PE.Email_ID
WHERE     (P.Pers_ID = @intID OR @intID=0)
AND
((PE.PersEmail_TS IS NULL) OR
(PE.PersEmail_ID =  (   SELECT TOP 1    PersEmail_ID
FROM        (SELECT * FROM dbo.PersEmail WHERE PersEmail_inaktuell = 0) AS persemail3       
WHERE       Pers_ID = P.Pers_ID
AND PersEmail_TS = (    SELECT      MAX(PersEmail_TS) AS Expr1
FROM        (SELECT * FROM dbo.PersEmail WHERE PersEmail_out_of_date = 0) AS persemail2
WHERE       Pers_ID = P.Pers_ID))))