CODEWITHSUNDEEP
sqlsql-serversql-server-2008partitioning
viko
viko

Reputation: 79

Difference between constraint and where in partitioned view

On my new work I found partitioned view in SQL Server 2008 Enterprise like this:

...
SELECT     *
FROM [SERVER1].DATA_2012_7_1.dbo.DATA WITH (nolock)
WHERE     DateTime >= '2012-07-01' AND DateTime <= '2012-07-15 23:59:59.997'
UNION ALL
SELECT     *
FROM [SERVER2].DATA_2012_7_16.dbo.DATA WITH (nolock)
WHERE     DateTime >= '2012-07-16' AND DateTime <= '2012-07-31 23:59:59.997'
UNION ALL
...

Before, when I created partitioned views, always I added CONSTRAINT in tables by date columns. But in my example all tables which use in this view don't have CONSTRAINT. What are you think about difference? Will be both views work right and equally quickly?

Upvotes: 2

Views: 667

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

From Using Partitioned Views:

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

So, it may not perform equally well (for queries where the CHECK constraints could have eliminated some partitions).

The query optimizer may be smart enough to effectively perform partition elimination based on the intersection between the WHERE clauses internal to the view and whatever conditions are applied in the query in which the view is accessed - but in choosing between a documented way to achieve elimination (CHECK constraints) and an undocumented one (optimizer combining WHERE clauses and other conditions), I know which one I'd choose.

Upvotes: 1

Related Questions