Problems with partitioned views

  • ZZartin

    SSC-Dedicated

    Points: 30379

    Wouldn't the best answer just be to modify the function?

  • san1901

    SSC Veteran

    Points: 249

    Just a minor limitation about the CHECK Constraint. The date columns must not have NULLs in them. Otherwise, SQL will go and check both tables.

    CHECK constraints reject values = FALSE. NULL values evaluate to UNKNOWN.

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

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

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    san1901 (1/6/2015)


    Just a minor limitation about the CHECK Constraint. The date columns must not have NULLs in them. Otherwise, SQL will go and check both tables.

    CHECK constraints reject values = FALSE. NULL values evaluate to UNKNOWN.

    Nope - that's not true as the following code will demonstrate (SQL 2008 R2 and SQL 2012 tested)

    USE tempdb;

    GO

    CREATE TABLE dbo.foo

    (

    Id INT NOT NULL IDENTITY (1, 1),

    c1 DATE NULL CHECK (c1 >= '20140101'),

    CONSTRAINT pk_foo_id PRIMARY KEY CLUSTERED (Id)

    );

    GO

    CREATE TABLE dbo.foo2

    (

    Id INT NOT NULL IDENTITY (1, 1),

    c1 DATE NULL CHECK (c1 < '20140101'),

    CONSTRAINT pk_foo2_id PRIMARY KEY CLUSTERED (Id)

    );

    GO

    INSERT INTO dbo.foo2 (c1) VALUES ('20131231');

    INSERT INTO dbo.foo2 (c1) VALUES (NULL);

    GO

    CREATE VIEW dbo.vfoo

    AS

    SELECT * FROM dbo.foo

    UNION

    SELECT * FROM dbo.foo2;

    GO

    Now I run a query against this view with a value which can only exist in dbo.foo2

    SET STATISTICS PROFILE ON;

    GO

    SELECT * FROM dbo.vfoo WHERE c1 = '20131231';

    The execution plan will look as followed...

    SELECT * FROM [dbo].[vfoo] WHERE [c1]=@1

    |--Compute Scalar(DEFINE:([Union1006]=[tempdb].[dbo].[foo2].[Id], [Union1007]=[tempdb].[dbo].[foo2].[c1]))

    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[foo2].[pk_foo2_id]), WHERE:([tempdb].[dbo].[foo2].[c1]='2013-12-31'))

    As you can see the query optimizer will always scan one table - in this case dbo.foo2!

    This behaviour is expected from my point of view because by usage of the predicate a NULL will automatically be sorted out from it. The profile shows a WHERE statement in the SCAN operator which is used as a FILTER operator (hidden).

    So independent of NULL or NOT NULL the optimizer checks the CHECK constraints first for contradiction and eliminates useless access operations.

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

  • ZZartin

    SSC-Dedicated

    Points: 30379

    Uwe Ricken (1/6/2015)


    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

    The point of the question was good, but i got lost on the practical aspect i guess.

    Why would i want to add multiple constraints to tables to support 1 function when that function could just be modified to have the date range in it instead of the union all? Especially when those constraints should already be enforced by business rules?

  • TomThomson

    SSC Guru

    Points: 104772

    ZZartin (1/6/2015)


    Uwe Ricken (1/6/2015)


    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

    The point of the question was good, but i got lost on the practical aspect i guess.

    Why would i want to add multiple constraints to tables to support 1 function when that function could just be modified to have the date range in it instead of the union all? Especially when those constraints should already be enforced by business rules?

    Because business rules that enforce data integrity should always be enforced by the schemata where possible; as the rules here are a pair of domain constraints (one on each table) the can be enforced in teh schemata and therefore should be.

    It is often considered that a schema with missing domain constraints is not normalised - because it is not even in first normal form, as the domains for its columns are not properly defined.

    Tom

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    ZZartin (1/6/2015)


    Uwe Ricken (1/6/2015)


    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

    The point of the question was good, but i got lost on the practical aspect i guess.

    Why would i want to add multiple constraints to tables to support 1 function when that function could just be modified to have the date range in it instead of the union all? Especially when those constraints should already be enforced by business rules?

    I don't understand your recommendation either - how will you return results from two different tables without the union all?

  • ZZartin

    SSC-Dedicated

    Points: 30379

    batgirl (1/6/2015)


    ZZartin (1/6/2015)


    Uwe Ricken (1/6/2015)


    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

    The point of the question was good, but i got lost on the practical aspect i guess.

    Why would i want to add multiple constraints to tables to support 1 function when that function could just be modified to have the date range in it instead of the union all? Especially when those constraints should already be enforced by business rules?

    I don't understand your recommendation either - how will you return results from two different tables without the union all?

    Sorry, I didn't mean to derail this discussion and the very good QotD of SQL Server constraints with other debates.

    I saw the business related question and my first response was just tell the user to run two queries and limit the function accordingly.

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    ZZartin (1/6/2015)


    batgirl (1/6/2015)


    ZZartin (1/6/2015)


    Uwe Ricken (1/6/2015)


    Hallo ZZartin,

    ZZartin (1/6/2015)


    Wouldn't the best answer just be to modify the function?

    sorry but I don't understand your "recommendation". What modification do you want to do to the function?

    The point of the question was good, but i got lost on the practical aspect i guess.

    Why would i want to add multiple constraints to tables to support 1 function when that function could just be modified to have the date range in it instead of the union all? Especially when those constraints should already be enforced by business rules?

    I don't understand your recommendation either - how will you return results from two different tables without the union all?

    Sorry, I didn't mean to derail this discussion and the very good QotD of SQL Server constraints with other debates.

    I saw the business related question and my first response was just tell the user to run two queries and limit the function accordingly.

    I guess that's the difference then - we don't tell users to run queries here.

  • cdesmarais 49673

    SSCommitted

    Points: 1863

    I got it wrong because I read the two year sliding window as a requirement, and picked the new index as the less wrong answer. But still a really excellent question covering a little understood feature.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    TL;DR 😀

    No seriously, great question. Thanks Uwe.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • MohamadHamedi

    Grasshopper

    Points: 16

    i have a question.

    we know that we stored old data from 20120101 to past.

    then when we want to search data,check the order date.

    if it is older than 20120101 we can search only history table and if it is newer than 20120101 we can search only actualtable.:cool:

    plz help me...

  • Ed Wagner

    SSC Guru

    Points: 286958

    Really good question. For those of us who don't get to play with Enterprise Edition, partitioned views are the only option we have. Thanks very much.

  • Uwe Ricken

    Hall of Fame

    Points: 3098

    MohamadHamedi (1/7/2015)


    i have a question.

    we know that we stored old data from 20120101 to past.

    then when we want to search data,check the order date.

    if it is older than 20120101 we can search only history table and if it is newer than 20120101 we can search only actualtable.:cool:

    plz help me...

    Hi Mohamad,

    separate the old data from the actual data by usage of two tables (as demonstrated in my demo).

    Than you add CHECK constraints on the [order date] attribute on each of the two tables.

    Next step is the creation of a view or TVF (table valued function) in the same was as in my demo!

    I think that my QotD is a good starting point for separated data with same structure.

    If you follow these demos your problem should be no more problem 🙂

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question.

Viewing 15 posts - 16 through 30 (of 35 total)

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