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

yhyhGZS 2018-04-21 08:20:36

应届生求职，面试full stack developer，第二面不停地问数据库问题，本人是数据库小白，就会写写sql query什么的。请问有人知道这题怎么做吗？

大概意思就是说如何写一个sql object，使你在任何时间都可以exactly reproduce past results（因为数据库每时每刻都在改变和更新所以在不同时间抓出来的信息不一样）

总共只有两个table：
1. Dbo.data1 (main data table, primary key is on date,id_security)
2. Data_audit.data1 (audit table that contains history of changes. Changedate is date-time of the changes)
主table叫dbo.data1,有[date], [id_security], 和其他fields。
辅table叫data_audit.data1，有[id_data_audit_data1], [date], [id_security], [ChangeDate], [ChangeUser], [ChangeAction] 和其他fields。

每一次主table发生了变化，辅table就会记录下变化的时间，id_security，ChangeAction等等
面试题是：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.
FYI, our live data table have 17 million records and 8 times more in audit table. Therefore, additional indexing is important part of the solution.

To be more clear
a. Now time is 2018-02-22 14:42:22. I do SELECT * FROM dbo.data1 WHERE date = '2011-12-31' and get some records.
b. 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')

Based on the code in triggers, I would like you to elaborate on:
2. Our approach to data audits. Triggers and PIT functions are generated automatically with stored procedure, so maintenance is not a problem.
3. Ideas for improvement. There is certainly room for improvement.
4. Is SQL 2008/12/16 built in audit better solution? We audit just selected tables.
5. Any suggestions for audit table compression and/or partitioning.

请各位大侠指点迷津，小女子感激不尽。
trigger 代码贴上：

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

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

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