Problems with partitioned views

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Comments posted to this topic are about the item Problems with partitioned views

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Good question.

    Small addition to the explanation: the second option (UNION instead of UNION ALL) will actually reduce performance, because in addition to reading data from both tables, SQL Server now also has to do the extra work to check for duplicates. We may know that they cannot exist, but SQL Server doesn't.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post,very good one. I went with eliminating the odds so remove two of them and you have the remaining one as the answer. Luckly the remaining one was the right one. 🙂 .

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • call.copse

    SSCoach

    Points: 16884

    Great question and very relevant to situations I have actually encountered - good food for thought, thanks.

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    Great question! Like the previous poster, I also have the potential to use this scenario, as we are keeping way too many records in our "live" tables, but nobody wants to delete anything!

    I have a follow-up, though. If archiving to the "old" table is done on a sliding "two years or older" window, can the CHECK CONSTRAINT be made dynamic, so that it constrains data in the archive table to be older than DATEADD(year, -2, getdate()), for example?

    btw - we are using 2008 R2 Standard Ed. if that makes any difference.

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    Bob Cullen-434885 (1/6/2015)


    Great question! Like the previous poster, I also have the potential to use this scenario, as we are keeping way too many records in our "live" tables, but nobody wants to delete anything!

    I have a follow-up, though. If archiving to the "old" table is done on a sliding "two years or older" window, can the CHECK CONSTRAINT be made dynamic, so that it constrains data in the archive table to be older than DATEADD(year, -2, getdate()), for example?

    btw - we are using 2008 R2 Standard Ed. if that makes any difference.

    You can add a dynamic check, but it may be a time bomb. In addition the check is ignored in this case.

    alter TABLE dbo.ActualOrders

    add constraint o check(OrderDate >= DATEADD(year, -2, getdate()))

    alter TABLE dbo.HistoryOrders

    add constraint oo check(OrderDate < DATEADD(year, -2, getdate()))

    The query plan uses the concatenation.

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Bob Cullen-434885 (1/6/2015)


    Great question! Like the previous poster, I also have the potential to use this scenario, as we are keeping way too many records in our "live" tables, but nobody wants to delete anything!

    I have a follow-up, though. If archiving to the "old" table is done on a sliding "two years or older" window, can the CHECK CONSTRAINT be made dynamic, so that it constrains data in the archive table to be older than DATEADD(year, -2, getdate()), for example?

    btw - we are using 2008 R2 Standard Ed. if that makes any difference.

    Hi Bob,

    NO - that's not feasible. If you plan a "switch" of data I would recommend a dedicated stored procedures with start and end time which handles it as follows (simple example!)

    -- Drop contraints FROM dbo.Orders and dbo.HistoryOrders

    ALTER TABLE dbo.ActualOrders DROP CONSTRAINT CHK_MIN_ORDERDATE

    ALTER TABLE dbo.HistoryOrders DROP CONSTRAINT CHK_MAX_ORDERDATE;

    GO

    -- Move all data from 2013 into the history table

    BEGIN TRANSACTION

    -- Transfer data from ActualOrders to HistoryOrders

    INSERT INTO dbo.HistoryOrders

    SELECT * FROM dbo.ActualOrders

    WHERE OrderDate < '20140101';

    -- Remove data from ActualOrders

    DELETE dbo.ActualOrders

    WHERE OrderDate < '20140101';

    COMMIT TRANSACTION

    GO

    -- Add the constraint to the tables again with new date constraints

    ALTER TABLE dbo.HistoryOrders ADD CONSTRAINT CHK_MAX_ORDERDATE CHECK (OrderDate < '20140101');

    ALTER TABLE dbo.ActualOrders ADD CONSTRAINT CHK_MIN_ORDERDATE CHECK (OrderDate >= '20140101');

    GO

    An alternative would be partitioning but that's an ENTERPRISE feature only 🙁

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

  • Carlo Romagnano

    SSC-Insane

    Points: 21807

    alter TABLE dbo.ActualOrders

    with nocheck

    add constraint o check(OrderDate between '20140101' and '20141231')

    alter TABLE dbo.HistoryOrders

    with nocheck

    add constraint oo check(OrderDate between '20100101' and '20131231')

    In this case the check is added, the optimizer ignores it until you run the following command:

    ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

    From BOL (http://msdn.microsoft.com/en-us/library/ms190273.aspx):

    WITH CHECK | WITH NOCHECK

    Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

    If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

    The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    Thanks, Uwe. I think this is something I'll need to play with some time. We have tried splitting tables before, but suffered from poor performance when querying a union. It got to the point where the sproc that retrieved the data made the decision which table(s) to query! Very messy.

  • Hany Helmy

    SSChampion

    Points: 13321

    Since the beginning, I just don`t like too long questions/stories 🙂

  • TomThomson

    SSC Guru

    Points: 104772

    Good question.

    Maybe it will teach some of the barbarians that including domain constraints can improve performance as well as help to protect data from corruption. Too often one hears "we do that check in the application so a constraint in the db will just make it run slower".

    Tom

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    I love a question that makes me think about a real world scenario with practical application of the concepts.

    Bravo!

  • twin.devil

    SSC-Insane

    Points: 22208

    excellent question and very nicely explained. thanks for sharing

  • SQLRNNR

    SSC Guru

    Points: 281210

    I like that there was a good setup for this question to illustrate the question. I think the correct answer itself should be fairly evident when looking at all of the options.

    Thanks Uwe.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply