CODEWITHSUNDEEP
viewsql-server-2008-r2
Rémy Esmery
Rémy Esmery

Reputation: 768

Partitioned View : using the CHECK CONSTRAINT

In order to store a huge amount of data in a SQL Server 2008 R2 Express database, I am splitting the data to several databases, creating a new "partition" for each month.

This post : http://msdn.microsoft.com/en-us/library/ms190019.aspx shows how to help the query optimizer to find data faster among my partitions.

However, when querying data on my View between dates that only match an empty partition, the query plan shows that it's seeking among every partitions. I can't figure out why though.

My view is very simple :

SELECT A.Gohan, B.Goku
FROM [Feb2011].[dbo].[A]
LEFT OUTER JOIN [Feb2011].[dbo].[B]
UNION ALL
SELECT A.Gohan, B.Goku
FROM [Mar2011].[dbo].[A]
LEFT OUTER JOIN [Mar2011].[dbo].[B]
UNION ALL
...

Any clue ?

Upvotes: 1

Views: 675

Answers (2)

gonsalu
gonsalu

Reputation: 3194

Partitioned views are not supported in the SQL Server Express edition.

Check SQL Server Features Not Supported in SQL Server Express section of the SQL Server Express Features page on MSDN.

Upvotes: 1

Eric
Eric

Reputation: 95143

This is because these aren't real partitions. If you build an index on each database table based on date, then it will check that index and quickly return 0 rows. If it were truly partitioned, then you would get the behavior you expect. But it's not, so it will check every table in the union statement to bring back an empty resultset.

So long as the query plan is showing a seek, and not a scan, I wouldn't worry one bit about it, since seeks are rather quick. Depending on the scan, though, you may also be okay (it's not a black and white rule).

Upvotes: 1

Related Questions