数据库大神请点进来，有道SQL面试题求帮助

[zhizhi123]

应该不用倒叙那么复杂，一个复杂查询就行了。

查询audit表的条件：
- 记录时间小于等于函数参数指定的时间
- 每个主键值只取时间最新的那条记录
- 记录的状态不能是删除

主键在这里特指主表里面绝对不会被修改、而且唯一的字段。

嘿，你好像是回答的最靠谱的一个人。你说的对。
对于 记录 是 插入或者删除 状态 也是有 办法的。
比如 现在 要查询 2011-12-31 时的 数据。
对于删除， 约定对于主表不允许进行真正的删除，只是对应加一个 field 状态表示 isDeleted.
对于插入，主表加插入时间， 取的时候主表 只取插入时间之前的。
然后 主表 和 audit 表 join

你不需要解释什么是 主键，连主键是什么都不知道的人不适合讨论这个问题。

 

[cetaphil]

大神救命~~~~~~~~

 

[printf]

—— 因为数据库每时每刻都在改变和更新所以在不同时间抓出来的信息不一样。

这个设计思想，使得服务器负担比较重。

如果结合事务日程和日志的话好像没必要。

这题目有点象 Rtos 的设计思想？

 

[dimples]

嗯嗯我已经基本上写出query来了，就是不太会sql语法。我的function 设两个parameter @data_date smalldatetime, @as_of_date datetime 先检查在as_of_date时间点之后是否发生了insert delete update，如果没有改变就直接从主表里select，如果有改变就select离as_of_date最近的时间点然后return。大概pseudocode写出来了但是不知道怎么写成function。

CREATE FUNCTION [dbo].[data1_PIT](@data_date smalldatetime, @as_of_date datetime)
RETURNS TABLE
AS
RETURN
IF EXISTS (SELECT * FROM data_audit.data1 AS DA WHERE DA.date = @data_date AND DA.ChangeDate >= @as_of_date)
    WITH cte AS
        (
            SELECT DA.*, ROW_NUMBER() OVER(PARTITION BY DA.id_security ORDER BY DA.ChangeDate) AS rk
            FROM data_audit.data1 AS DA WHERE DA.date=@data_date AND DA.ChangeDate >= @as_of_date)
        )
        SELECT date,
        id_security,
        column6,
        column7,
        column10,
        column11,
        column12,
        column13,
        column14,
        column15,
        column16,
        column17,
        column18,
        column19,
        column20,
        column21,
        column22,
        column23,
        column24,
        column25,
        column26,
        column27,
        column28,
        column29,
        column30,
        column31,
        column32,
        column33,
        column34,
        column35,
        column36,
        column37,
        column38,
        column39,
        column40,
        column41,
        column42,
        column43,
        column44,
        column45,
        AddLoginID,
        AddDate,
        column48,
        column49,
        column50,
        column51,
        column52,
        column53,
        column54,
        column55,
        column56,
        column57,
        column58,
        column59,
        column60,
        column61,
        column62,
        column64,
        column65,
        column66,
        column67,
        column68,
        column69,
        column70,
        column71,
        column72
        FROM cte
        where rk = 1;
ELSE
    SELECT D.* FROM dbo.data1 AS D WHERE date = @data_date
END

没看明白Function要做什么。
两个tables没有 join 一下？ group by [date], [id_security] order by [change_date] ？
我也是菜鸟，

 

[fieldmarshal]

—— 因为数据库每时每刻都在改变和更新所以在不同时间抓出来的信息不一样。

这个设计思想，使得服务器负担比较重。

如果结合事务日程和日志的话好像没必要。

这题目有点象 Rtos 的设计思想？

你想太多了。面试官只是随便用个常用场景考一下而已。

 

[dimples]

https://en.m.wikipedia.org/wiki/Temporal_database

 

[cetaphil]

思路我都懂，可是因为不会sql语法写不出function，一时半会也学不会。只想先对付一下面试官，如果哪位好心人能告诉我怎么写这个function感激不尽。

 

[cetaphil]

没看明白Function要做什么。
两个tables没有 join 一下？ group by [date], [id_security] order by [change_date] ？
我也是菜鸟，

意思就是说每次update insert 和delete主table里的row就会触发trigger，把所做的改动记录在一个叫做audit table的辅table里面，现在要求的function就是给你一个时间点能够让你像穿越历史一样回到那个时间点从主table里面select出内容来。（因为主table里的条目会一直在改变所以不同时间select出的row都不一样）。我的思路是如果在这个时间点之后没有改动，那直接从主table里select，但如果有任何改动，就找离时间点最近的一条从audit里select出返回。现在的问题是我不知道怎么写出来。。看了好多tutorial讲inline table-valued function但是没搞懂到底该怎么写这个function。。

 

[梨花院落溶溶月]

在数据库里和一般编程不一样，不是一定要写function的。Trigger后面直接写要做的事情就可以。

 

[道白2]

兄弟啊，我觉得你的sql写的不错啊，怎么能写不出来？是不是把几个问题给mix一块了?
到底是trigger不会写，还是function不会写？还是sql不会写？


按照你的描述，你的trigger已经写好了，也就是所有的历史改变的数据已经放到审计表了，对吗？

如果基于这个前提，你就是对这个已经存在的审计表，写一个查询语句，所谓函数什么的，也就是这个查询语句的马甲。
给你个存储过程的例子，你把字段换成自己的就行了。
CREATE PROCEDURE getauditHistory(@id int, @begintime datatime, @endtime datatime)
begin
declare @str vachar(128)
set @str = 'select * from tablename where id='+ @id +'and' + changetime in ('+@begintime+', '+@endtime+')' order by changetime desc'
exec(@str)
end
-各个数据库系统的ddl和dml语言略有区别，不过你就是刚毕业面试的话， 没人会关心。

如果说你的trigger还没写好，那就是trigger怎么写的问题。
我看你的trigger基本语法都写的挺好的，比我写的强啊。你把trigger改改就行了。
三种操作，分别考虑一下，在做dml时候，把什么样的数据搬到审计表里就行了。
insert, 分为before insert, after insert
update, 分为before update, after update
delete, 这个实际上要考虑你原始表的结构。给个提示，有index字段的话，index被删除后，不能再添加同样的index数值。
（不是所有的db都支持before .., after ...）

至于最后的那个要求，按id加个聚簇索引，如果用id查，对付million，不是大问题。

 

[Mimi2007]

首先指出我和楼主对题目理解不同的几点：

1. In the future, even though records can be updated or deleted-所以我认为这里只是指Update 和Delete 而不包括insert。
2. I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')-这里的dbo.data1_PIT 是function的名字，不是指原始的table: data1；题目中并没有说history log要从原始table data1中选出. 我是这样理解的，用delete 做例子，既然delete已经发生，原始data1 table里就不会再存在那条信息，又如何能从原始table: data1里select那条信息呢？ 别的公司我不知道， 我们公司是原始table delete records, 然后只在audit table 中保留历史记录，用于万一需要revert的时候用。

帮楼主把Trigger 和Function写好了， 也在我电脑上简单测试了一下，运行没问题。 刚毕业都不容易，特别是女生做developer可能会更难一些。祝楼主好运 。

---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
for delete
as
begin
set nocount on
insert into data_audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),SUser_SName(),column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go

---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.

---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from data_audit_data1 where date=@OriginalDate and changedate=@ChangeDate
go

---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)

select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')

 

[cetaphil]

首先指出我和楼主对题目理解不同的几点：

1. In the future, even though records can be updated or deleted-所以我认为这里只是指Update 和Delete 而不包括insert。
2. I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')-这里的dbo.data1_PIT 是function的名字，不是指原始的table: data1；题目中并没有说history log要从原始table data1中选出. 我是这样理解的，用delete 做例子，既然delete已经发生，原始data1 table里就不会再存在那条信息，又如何能从原始table: data1里select那条信息呢？ 别的公司我不知道， 我们公司是原始table delete records, 然后只在audit table 中保留历史记录，用于万一需要revert的时候用。

帮楼主把Trigger 和Function写好了， 也在我电脑上简单测试了一下，运行没问题。 刚毕业都不容易，特别是女生做developer可能会更难一些。祝楼主好运 。

---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
for delete
as
begin
set nocount on
insert into data_audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),SUser_SName(),column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go

---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.

---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from data_audit_data1 where date=@OriginalDate and changedate=@ChangeDate
go

---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)

select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')

谢谢美女姐姐！我看了一下您的function的参数是originalDate和changeDate，但题目要求是dbo.data1_PIT 可以重现过去某时刻的select并return出exact same result，我们是不知道ChangeDate的，只知道我们想重现过去某个时间点主table select出来的数据。三个trigger已经写好了，现在问题就只剩这个function dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime).
@data_date就是primary key, @as_of_date 是指回到过去某个时间点。

我想的是如果as_of_date之后没有任何变化就直接从主table里面select, 如果有变化就找距离as_of_date最近的ChangeDate，再从audit table里面select。
还有个情况就是同一个data_date有好几个id_security所以对每一个id_security都要找最近的as_of_date。您能否看看怎么实现这个function？

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Delete_all_fields]    Script Date: 4/21/2018 4:51:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]

    ON [dbo].[data1]

FOR DELETE

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    -- [ChangeAction]=D means delete
    INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Insert_all_fields]    Script Date: 4/21/2018 4:52:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_data1_Insert_all_fields]

    ON [dbo].[data1]




FOR INSERT

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()
 
    --inserts only - we just need to update user information
    UPDATE u
    SET
        u.[AddLoginID]=@uid,
        u.[AddDate]=getdate()
    FROM [dbo].[data1] u
    INNER JOIN inserted i ON
     i.[date] = u. [date] AND  i.[id_security] = u. [id_security]
    WHERE u.[AddLoginID] IS NULL AND u.[AddDate] IS NULL

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Update_all_fields]    Script Date: 4/21/2018 4:52:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_data1_Update_all_fields]

    ON [dbo].[data1]

FOR UPDATE
AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    IF ( SELECT trigger_nestlevel()
       ) = 1   --don't do anything if triggered from insert trigger
        BEGIN 
        --[ChangeAction]=U means update
        INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'U' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]FROM
        (
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted
        EXCEPT
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM inserted
        ) t
     
        END

 

[Mimi2007]

谢谢美女姐姐！我看了一下您的function的参数是originalDate和changeDate，但题目要求是dbo.data1_PIT 可以重现过去某时刻的select并return出exact same result，我们是不知道ChangeDate的，只知道我们想重现过去某个时间点主table select出来的数据。三个trigger已经写好了，现在问题就只剩这个function dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime).
@data_date就是primary key, @as_of_date 是指回到过去某个时间点。

我想的是如果as_of_date之后没有任何变化就直接从主table里面select, 如果有变化就找距离as_of_date最近的ChangeDate，再从audit table里面select。
还有个情况就是同一个data_date有好几个id_security所以对每一个id_security都要找最近的as_of_date。您能否看看怎么实现这个function？
浏览附件493410

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Delete_all_fields]    Script Date: 4/21/2018 4:51:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]

    ON [dbo].[data1]

FOR DELETE

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    -- [ChangeAction]=D means delete
    INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Insert_all_fields]    Script Date: 4/21/2018 4:52:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_data1_Insert_all_fields]

    ON [dbo].[data1]




FOR INSERT

AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    --inserts only - we just need to update user information
    UPDATE u
    SET
        u.[AddLoginID]=@uid,
        u.[AddDate]=getdate()
    FROM [dbo].[data1] u
    INNER JOIN inserted i ON
     i.[date] = u. [date] AND  i.[id_security] = u. [id_security]
    WHERE u.[AddLoginID] IS NULL AND u.[AddDate] IS NULL

USE [store]
GO
/****** Object:  Trigger [dbo].[trg_data1_Update_all_fields]    Script Date: 4/21/2018 4:52:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trg_data1_Update_all_fields]

    ON [dbo].[data1]

FOR UPDATE
AS

    SET NOCOUNT ON
    DECLARE @uid INT
    SET @uid = SUSER_ID()

    IF ( SELECT trigger_nestlevel()
       ) = 1   --don't do anything if triggered from insert trigger
        BEGIN
        --[ChangeAction]=U means update
        INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
        SELECT 'U' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]FROM
        (
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM deleted
        EXCEPT
        SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
        FROM inserted
        ) t

        END

我不大明白你的意思。题目要求是有提供changedate 的。

你的题目：
In the future, even though records can be updated or deleted, if

• I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
• I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')

你的题目的第一点 “dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')”提供了2个时间点， 第二个应该是 changedate, 所以面试官应该是是提供了changedate 的。这个是原题吗？还是你自己的理解？如果是你自己的理解而不是原题的话，建议你把原本的题目贴出来看一下。

 

[cetaphil]

我不大明白你的意思。题目要求是有提供changedate 的。

你的题目：
In the future, even though records can be updated or deleted, if

• I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
• I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')

你的题目的第一点提供了2个时间点。这个是原题吗？还是你自己的理解？如果是你自己的理解而不是原题的话，建议你把原本的题目贴出来看一下。

我之前附的这张图就是原题，trigger面试官已经写好了，现在只剩implement function。
他的意思是，假设在2018-02-22 14:42:22这个时间，我去主table select出一些东西，设这些东西叫做A。这些东西会update/delete并记录在audit table里面。那么今天是2018-04-21，我输入SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')，我希望还能return出A。第二个参数不是changeDate，而是我想重现的那个时间点。

 

[xiaoa]

我不大明白你的意思。题目要求是有提供changedate 的。

你的题目：
In the future, even though records can be updated or deleted, if

• I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
• I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')

你的题目的第一点 “dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')”提供了2个时间点， 第二个应该是 changedate, 所以面试官应该是是提供了changedate 的。这个是原题吗？还是你自己的理解？如果是你自己的理解而不是原题的话，建议你把原本的题目贴出来看一下。

你理解非常错误，楼主理解是正确的，亏你是dba .
是这样的，假设当前时刻 是 2018-02-22 14:42:22 ，
我执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') 得到了一些数据，
之后 这些数据 有改变， 具体改变 以及改变的时间在 audit table 里， audit table 里的changedate 就是 改变发生的时间。
那么假设过了几天， 当前时刻变成 2018-04-21 14:42:22 ， 如果同样 执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') ， 得到的结果肯定 和前几天得到的结果不一样。
要求是 写个 function data1_PIT , 使得 SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22') ( 第二个parameter 是上次执行sql 的时间)，
使得返回的结果和上次 时刻 (2018-02-22 14:42:22 ) 时 得到的结果一致。
这种 database 设计需要 符合 6NF , https://en.wikipedia.org/wiki/Sixth_normal_form

 

[Mimi2007]

你理解非常错误，楼主理解是正确的，亏你是dba .
是这样的，假设当前时刻 是 2018-02-22 14:42:22 ，
我执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') 得到了一些数据，
之后 这些数据 有改变， 具体改变 以及改变的时间在 audit table 里， audit table 里的changedate 就是 改变发生的时间。
那么假设过了几天， 当前时刻变成 2018-04-21 14:42:22 ， 如果同样 执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') ， 得到的结果肯定 和前几天得到的结果不一样。
要求是 写个 function data1_PIT , 使得 SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22') ( 第二个parameter 是上次执行sql 的时间)，
使得返回的结果和上次 时刻 (2018-02-22 14:42:22 ) 时 得到的结果一致。
这种 database 设计需要 符合 6NF , https://en.wikipedia.org/wiki/Sixth_normal_form

我是按英文这部分来理解给的建议。我的理解可能是错的，不过我也只是好心给出我的看法而已。我们做project 的话都有详细的project doc, 所以不存在理解有分歧的情况。因为如果什么理解有歧义，在写code 之前就会找 stakeholder 问明白了。呵呵。我
不是很理解楼主的题目，所以不好意思帮不到楼主。之前楼主说不会写function的 syntax, 我的例子中至少syntax 是对的，楼主可以按你的理解改一下来用吧。

面试题是：Create SQL objects to return Point-in-time data that generate past results

1. Create SQL object to show data in the same structure as dbo.data1 but as of certain date/time.

To be more clear

1. Now time is 2018-02-22 14:42:22.
   • I do SELECT * FROM dbo.data1 WHERE date = '2011-12-31'
   • and get some records.
2. In the future, even though records can be updated or deleted, if
   • I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
   • I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')
3. Based on the code in triggers, I would like you to elaborate on:
   • Our approach to data audits. Triggers and PIT functions are generated automatically with stored procedure, so maintenance is not a problem.
   • Ideas for improvement. There is certainly room for improvement.
   • Is SQL 2008/12/16 built in audit better solution? We audit just selected tables.
   • Any suggestions for audit table compression and/or partitioning.

 

[Mimi2007]

你理解非常错误，楼主理解是正确的，亏你是dba .
是这样的，假设当前时刻 是 2018-02-22 14:42:22 ，
我执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') 得到了一些数据，
之后 这些数据 有改变， 具体改变 以及改变的时间在 audit table 里， audit table 里的changedate 就是 改变发生的时间。
那么假设过了几天， 当前时刻变成 2018-04-21 14:42:22 ， 如果同样 执行 SELECT * FROM dbo.data1 WHERE date = '2011-12-31') ， 得到的结果肯定 和前几天得到的结果不一样。
要求是 写个 function data1_PIT , 使得 SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22') ( 第二个parameter 是上次执行sql 的时间)，
使得返回的结果和上次 时刻 (2018-02-22 14:42:22 ) 时 得到的结果一致。
这种 database 设计需要 符合 6NF , https://en.wikipedia.org/wiki/Sixth_normal_form

顺便说一下，我可从来没说过我是DBA。我离DBA还差老远了呢。

 

[cetaphil]

我是按英文这部分来理解给的建议。我的理解可能是错的，不过我也只是好心给出我的看法而已。我们做project 的话都有详细的project doc, 所以不存在理解有分歧的情况。因为如果什么理解有歧义，在写code 之前就会找 stakeholder 文明白了，呵呵。我是不是很理解楼主的题目，所以不好意思帮不到楼主。之前楼主说不会写function的 syntax, 我的例子中至少syntax 是对的，楼主可以按你的理解改一下来用吧。

面试题是：Create SQL objects to return Point-in-time data that generate past results

1. Create SQL object to show data in the same structure as dbo.data1 but as of certain date/time.

To be more clear

1. Now time is 2018-02-22 14:42:22.
   • I do SELECT * FROM dbo.data1 WHERE date = '2011-12-31'
   • and get some records.
2. In the future, even though records can be updated or deleted, if
   • I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')
   • I will have to get identical output as I see now with the query under a.a (SELECT * FROM dbo.data1 WHERE date = '2011-12-31')
3. Based on the code in triggers, I would like you to elaborate on:
   • Our approach to data audits. Triggers and PIT functions are generated automatically with stored procedure, so maintenance is not a problem.
   • Ideas for improvement. There is certainly room for improvement.
   • Is SQL 2008/12/16 built in audit better solution? We audit just selected tables.
   • Any suggestions for audit table compression and/or partitioning.

美女姐姐别生气，我说的有些绕，xiaoa表达的比我清楚。您的思路是对的，只是跟面试官的要求不太一致。面试官后来还加了一句hint：
I also wanted to give you few hints about my own solution:

I decided to use Table-Valued User-Defined Function

I created function: dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime).

The function returns data for one date, in structure identical to the structure of dbo.data1

According to my simple measurements, overhead of SELECT * statement over our live table is just 10%.
我再看看tutorial怎么写function吧。这个操作的话inline table-valued function可以实现吗？我要从两个table里select的话是不是要写multi statement的那种？

 

[xiaoa]

顺便说一下，我可从来没说过我是DBA。我离DBA还差老远了呢。

你长得一定非常漂亮.

 

[Mimi2007]

你长得一定非常漂亮.

是因为长得漂亮的人写Code都很糟糕吗？呵呵。我写Code也只有两年，初级水平，还在努力进步中，见笑了。