Execution Plan on a View looking at Partitioned Tables

[Matthew Nguyen]

I currently have tables that are partitioned out by year & month for our sales transactions. This was set in place at our company before I even started. For example, we have sales tables that would look something like this:

factdailysales_201501
factdailysales_201502
factdailysales_201503 etc ...


Generally, I've always performed dynamic SQL to capture a Start Date, End Date, find out what partitions those are, and then loop through each of those partitions ... but its starting to become such a hassle and I've learned that this is probably not the best way to do it in terms of just maintenance, trouble shooting, and performance.

I decided to build a view that would UNION ALL of my sales partitions together. However, I don't want selecting from the view to have to scan all of the partitions on execution, it would take away the whole purpose of partitioning tables out. Because of this, I added check constraints on date to each of my sales tables. This way when I selected from the view, it would know which tables to access from instead of scanning every table.

Here are the following examples below:

SELECT SUM([retail])
FROM Sales_Orig
WHERE [Date] >= '2015-03-01'

This query has the execution plan of only pulling from the partitions that I needed.

My problem that i'm facing right now is that most of the time when my team will be writing stored procedures, they would more than likely write their queries where a date variable is passed into the where statement.


DECLARE @SD DATE = '2015-03-01'

SELECT SUM([retail])
FROM Sales_Orig
WHERE [Date] >= @SD


However, when a variable is being passed in, the execution plan now scans ALL of the partitions in the view, causing the performance to take wayyy longer than when I hard coded in the date

I suppose I could do dynamic SQL again and insert the date string into the SELECT statement, but it would bring me back to the beginning of trying to get rid of dynamic SQL in the first place for this simple sales query.

So my question is, am I setting this up wrong? Am I on the right track? It seems that the view can't take in a variable for the check constraint and ends up scanning every table. Is there another approach anyone would recommend? Maybe my original solution of just looping through partitions via dynamic SQL is the best way to do it?

[Erland Sommarskog]

Post by Matthew Nguyen
So my question is, am I setting this up wrong? Am I on the right track?
It seems that the view can't take in a variable for the check constraint
and ends up scanning every table. Is there another approach anyone would
recommend? Maybe my original solution of just looping through partitions
via dynamic SQL is the best way to do it?

No, you are on the right track. Apparently, there is something which is
not perfect in the setup, because in a correctly configured partitioned
view a query like:

DECLARE @SD DATE = '2015-03-01'

SELECT SUM([retail])
FROM Sales_Orig
WHERE [Date] >= @SD

should only read from the partitions qualified by the variable. The query
plan will include all tables, because the query plan itself is static.
However, there is a filter with a startup expression that at run-time
make sure that only the relevant partitions are accessed.

I have a quick introduction to partitioned views here:
http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

Stefan Delmarco has a lot more details here:
http://www.fotia.co.uk/FA.02.Sql2KPartitionedViews.01.aspx

[Matthew Nguyen]

Post by Erland Sommarskog

Post by Matthew Nguyen
So my question is, am I setting this up wrong? Am I on the right track?
It seems that the view can't take in a variable for the check constraint
and ends up scanning every table. Is there another approach anyone would
recommend? Maybe my original solution of just looping through partitions
via dynamic SQL is the best way to do it?

No, you are on the right track. Apparently, there is something which is
not perfect in the setup, because in a correctly configured partitioned
SELECT SUM([retail])
FROM Sales_Orig
should only read from the partitions qualified by the variable. The query
plan will include all tables, because the query plan itself is static.
However, there is a filter with a startup expression that at run-time
make sure that only the relevant partitions are accessed.
http://www.sommarskog.se/dynamic_sql.html#Sales_yymm
http://www.fotia.co.uk/FA.02.Sql2KPartitionedViews.01.aspx
--

Thank you for your response! I still cannot seem to get the variable method to work. I've even found another article as well, who proposes the same solution.

http://sqlsunday.com/2014/08/31/partitioned-views/

However, in this EXACT same scenario, when I change the date to a variable for the WHERE clause & run the execution plan, it is still continuing to scan through all tables instead of the necessary ones.

Would his specific set up example also be configured in a way that would cause the partition view to not work with the variable in the WHERE clause?

[Erland Sommarskog]

Post by Matthew Nguyen
However, in this EXACT same scenario, when I change the date to a
variable for the WHERE clause & run the execution plan, it is still
continuing to scan through all tables instead of the necessary ones.
Would his specific set up example also be configured in a way that would
cause the partition view to not work with the variable in the WHERE
clause?

It should work. But I am uncertain how you conclude that all tables are
scanned. Did you run with SET STATISTICS IO ON and saw a lots of reads
on all tables? Beware that only a casual look at the query plan will fool
you. As I said, all tables will be there, but if you check the partitions
filtered out by the variable, they should have "Number of executions" = 0.

[Erland Sommarskog]

By the way, here is a working demo which assumes that you have the Northwind
database around. If not, you can get it here:
http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

DROP TABLE Orders96,Orders97,Orders98
go
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders96 FROM Northwind..Orders WHERE year(OrderDate) = 1996
ALTER TABLE Orders96 ALTER COLUMN OrderID int NOT NULL

SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders97 FROM Northwind..Orders WHERE year(OrderDate) = 1997
ALTER TABLE Orders97 ALTER COLUMN OrderID int NOT NULL

SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders98 FROM Northwind..Orders WHERE year(OrderDate) = 1998
ALTER TABLE Orders98 ALTER COLUMN OrderID int NOT NULL
go
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (OrderID)
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (OrderID)
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (OrderID)
go
ALTER TABLE Orders96 ADD Year char(4) NOT NULL
CONSTRAINT def96 DEFAULT '1996'
CONSTRAINT check96 CHECK (Year = '1996')

ALTER TABLE Orders97 ADD Year char(4) NOT NULL
CONSTRAINT def97 DEFAULT '1997'
CONSTRAINT check97 CHECK (Year = '1997')

ALTER TABLE Orders98 ADD Year char(4) NOT NULL
CONSTRAINT def98 DEFAULT '1998'
CONSTRAINT check98 CHECK (Year = '1998')
go
ALTER TABLE Orders96 DROP CONSTRAINT pk96
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (Year, OrderID)

ALTER TABLE Orders97 DROP CONSTRAINT pk97
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (Year, OrderID)

ALTER TABLE Orders98 DROP CONSTRAINT pk98
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (Year, OrderID)
go
CREATE VIEW Orders AS
SELECT * FROM dbo.Orders96
UNION ALL
SELECT * FROM dbo.Orders97
UNION ALL
SELECT * FROM dbo.Orders98
go
SET STATISTICS IO ON
go
DECLARE @year char(4) = '1997'
SELECT OrderID, OrderDate, EmployeeID
FROM Orders
WHERE Year > @year
AND CustomerID = N'BERGS'

[Matthew Nguyen]

Post by Erland Sommarskog
By the way, here is a working demo which assumes that you have the Northwind
http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en
DROP TABLE Orders96,Orders97,Orders98
go
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders96 FROM Northwind..Orders WHERE year(OrderDate) = 1996
ALTER TABLE Orders96 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders97 FROM Northwind..Orders WHERE year(OrderDate) = 1997
ALTER TABLE Orders97 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders98 FROM Northwind..Orders WHERE year(OrderDate) = 1998
ALTER TABLE Orders98 ALTER COLUMN OrderID int NOT NULL
go
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (OrderID)
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (OrderID)
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (OrderID)
go
ALTER TABLE Orders96 ADD Year char(4) NOT NULL
CONSTRAINT def96 DEFAULT '1996'
CONSTRAINT check96 CHECK (Year = '1996')
ALTER TABLE Orders97 ADD Year char(4) NOT NULL
CONSTRAINT def97 DEFAULT '1997'
CONSTRAINT check97 CHECK (Year = '1997')
ALTER TABLE Orders98 ADD Year char(4) NOT NULL
CONSTRAINT def98 DEFAULT '1998'
CONSTRAINT check98 CHECK (Year = '1998')
go
ALTER TABLE Orders96 DROP CONSTRAINT pk96
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders97 DROP CONSTRAINT pk97
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders98 DROP CONSTRAINT pk98
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (Year, OrderID)
go
CREATE VIEW Orders AS
SELECT * FROM dbo.Orders96
UNION ALL
SELECT * FROM dbo.Orders97
UNION ALL
SELECT * FROM dbo.Orders98
go
SET STATISTICS IO ON
go
SELECT OrderID, OrderDate, EmployeeID
FROM Orders
AND CustomerID = N'BERGS'
--

Thanks for this sample data! I've loaded it into my test environment and ran the queries. You are indeed right about the execution plan visually displaying all the tables, but not actually performing the executions on the tables.

For the life of me however, I cannot get this same replication to work on my tables. Like I mentioned previously, I have tables named factdailysales_201501, 201502, 201503... etc... However, when I examine the execution plan and actually look at number of executions, there are multiple executions for each and every partition. Each partition would have "Number of executions" = 4.

Here are the structures of my table.

CREATE TABLE [dbo].[FactDailySales_201205](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]

I've created some dynamic sql to loop through each partition to add in the following dates for the constraints.

ALTER TABLE FactDailySales_201205
ADD CONSTRAINT Check_201205 CHECK (OrderDate >= '2012-01-01'
AND OrderDate <= '2012-01-31')

My View looks something like:

SELECT *
FROM factdailysales_201205
UNION ALL
SELECT *
FROM FactDailySales_201206
SELECT *
UNION ALL FactDailySales_201207

... etc


Everytime I've gone in and built this, and I run my query, say:

SELECT *
FROM SALES

It's executing on every single partition and drastically kills the performance. When I pass a variable date through, it can take 30 seconds. When I hard code the date, it takes only several.

At first I thought maybe it was because I didnt have those Primary Keys, but even with Northwind, having no primary keys, and just the very basic constraint, it works unlike my sales tables. Is there something you could recommend me to take a look at?

[Matthew Nguyen]

Post by Matthew Nguyen

Post by Erland Sommarskog
By the way, here is a working demo which assumes that you have the Northwind
http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en
DROP TABLE Orders96,Orders97,Orders98
go
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders96 FROM Northwind..Orders WHERE year(OrderDate) = 1996
ALTER TABLE Orders96 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders97 FROM Northwind..Orders WHERE year(OrderDate) = 1997
ALTER TABLE Orders97 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders98 FROM Northwind..Orders WHERE year(OrderDate) = 1998
ALTER TABLE Orders98 ALTER COLUMN OrderID int NOT NULL
go
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (OrderID)
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (OrderID)
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (OrderID)
go
ALTER TABLE Orders96 ADD Year char(4) NOT NULL
CONSTRAINT def96 DEFAULT '1996'
CONSTRAINT check96 CHECK (Year = '1996')
ALTER TABLE Orders97 ADD Year char(4) NOT NULL
CONSTRAINT def97 DEFAULT '1997'
CONSTRAINT check97 CHECK (Year = '1997')
ALTER TABLE Orders98 ADD Year char(4) NOT NULL
CONSTRAINT def98 DEFAULT '1998'
CONSTRAINT check98 CHECK (Year = '1998')
go
ALTER TABLE Orders96 DROP CONSTRAINT pk96
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders97 DROP CONSTRAINT pk97
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders98 DROP CONSTRAINT pk98
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (Year, OrderID)
go
CREATE VIEW Orders AS
SELECT * FROM dbo.Orders96
UNION ALL
SELECT * FROM dbo.Orders97
UNION ALL
SELECT * FROM dbo.Orders98
go
SET STATISTICS IO ON
go
SELECT OrderID, OrderDate, EmployeeID
FROM Orders
AND CustomerID = N'BERGS'
--

Thanks for this sample data! I've loaded it into my test environment and ran the queries. You are indeed right about the execution plan visually displaying all the tables, but not actually performing the executions on the tables.
For the life of me however, I cannot get this same replication to work on my tables. Like I mentioned previously, I have tables named factdailysales_201501, 201502, 201503... etc... However, when I examine the execution plan and actually look at number of executions, there are multiple executions for each and every partition. Each partition would have "Number of executions" = 4.
Here are the structures of my table.
CREATE TABLE [dbo].[FactDailySales_201205](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
I've created some dynamic sql to loop through each partition to add in the following dates for the constraints.
ALTER TABLE FactDailySales_201205
ADD CONSTRAINT Check_201205 CHECK (OrderDate >= '2012-01-01'
AND OrderDate <= '2012-01-31')
SELECT *
FROM factdailysales_201205
UNION ALL
SELECT *
FROM FactDailySales_201206
SELECT *
UNION ALL FactDailySales_201207
... etc
SELECT *
FROM SALES
It's executing on every single partition and drastically kills the performance. When I pass a variable date through, it can take 30 seconds. When I hard code the date, it takes only several.
At first I thought maybe it was because I didnt have those Primary Keys, but even with Northwind, having no primary keys, and just the very basic constraint, it works unlike my sales tables. Is there something you could recommend me to take a look at?

Sorry a little more clarification on the last part of my SQL statement.

/** EXAMPLE WITH MY SALES TABLES **/

DECLARE @SD DATE = '2012-01-01'
DECLARE @ED DATE = '2012-01-31'

SCENARIO 1 (passing in variable): Scans all tables and actually performs executions on each.
SELECT *
FROM Sales
WHERE Date >= @SD
and Date <= @ED

SCENARIO 2 (hard coded date): Efficiently only executes on necessary tables.
SELECT *
FROM Sales
WHERE Date >= '2012-01-01'
and Date <= '2012-01-31'

[Matthew Nguyen]

Post by Erland Sommarskog
By the way, here is a working demo which assumes that you have the Northwind
http://www.microsoft.com/downloads/info.aspx?na=22&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en
DROP TABLE Orders96,Orders97,Orders98
go
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders96 FROM Northwind..Orders WHERE year(OrderDate) = 1996
ALTER TABLE Orders96 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders97 FROM Northwind..Orders WHERE year(OrderDate) = 1997
ALTER TABLE Orders97 ALTER COLUMN OrderID int NOT NULL
SELECT OrderID + 0 AS OrderID, OrderDate, CustomerID, EmployeeID
INTO Orders98 FROM Northwind..Orders WHERE year(OrderDate) = 1998
ALTER TABLE Orders98 ALTER COLUMN OrderID int NOT NULL
go
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (OrderID)
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (OrderID)
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (OrderID)
go
ALTER TABLE Orders96 ADD Year char(4) NOT NULL
CONSTRAINT def96 DEFAULT '1996'
CONSTRAINT check96 CHECK (Year = '1996')
ALTER TABLE Orders97 ADD Year char(4) NOT NULL
CONSTRAINT def97 DEFAULT '1997'
CONSTRAINT check97 CHECK (Year = '1997')
ALTER TABLE Orders98 ADD Year char(4) NOT NULL
CONSTRAINT def98 DEFAULT '1998'
CONSTRAINT check98 CHECK (Year = '1998')
go
ALTER TABLE Orders96 DROP CONSTRAINT pk96
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders97 DROP CONSTRAINT pk97
ALTER TABLE Orders97 ADD CONSTRAINT pk97 PRIMARY KEY (Year, OrderID)
ALTER TABLE Orders98 DROP CONSTRAINT pk98
ALTER TABLE Orders98 ADD CONSTRAINT pk98 PRIMARY KEY (Year, OrderID)
go
CREATE VIEW Orders AS
SELECT * FROM dbo.Orders96
UNION ALL
SELECT * FROM dbo.Orders97
UNION ALL
SELECT * FROM dbo.Orders98
go
SET STATISTICS IO ON
go
SELECT OrderID, OrderDate, EmployeeID
FROM Orders
AND CustomerID = N'BERGS'
--

Sorry for posting so many repeated responses. I just came across another realization. Doing a SELECT * will display my number of executions = 0 for the unrelated tables that are not a part of the WHERE statement. However, when I do an aggregate function like SELECT SUM(retail), this will now change the number of executions higher than 0 for each and every table scan.

This is making me believe that using an aggregate function will then disregard the date constraint that is in place?

[Erland Sommarskog]

Post by Matthew Nguyen
Sorry for posting so many repeated responses. I just came across another
realization. Doing a SELECT * will display my number of executions = 0
for the unrelated tables that are not a part of the WHERE statement.
However, when I do an aggregate function like SELECT SUM(retail), this
will now change the number of executions higher than 0 for each and
every table scan.
This is making me believe that using an aggregate function will then
disregard the date constraint that is in place?

It's difficult to follow, since I don't see all details. But in this
script, modelled from your posts, only the addressed partitions are
visited at run-time.

Note that I don't have any data in the tables.

CREATE TABLE [dbo].[FactDailySales_201205](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[FactDailySales_201204](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[FactDailySales_201203](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[FactDailySales_201202](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[FactDailySales_201201](
[SKU] [varchar](14) NULL,
[Date] [date] NULL,
[Store] [varchar](3) NULL,
[Salesman] [varchar](6) NULL,
[Time] [varchar](258) NULL,
[Invoice] [varchar](268) NULL,
[Cashier] [varchar](6) NULL,
[Register] [varchar](3) NULL,
[Retail] [numeric](12, 2) NULL,
[Units] [int] NULL,
[Cost] [numeric](12, 3) NULL,
[LocationID] [int] NULL
) ON [PRIMARY]
go
ALTER TABLE FactDailySales_201205
ADD CONSTRAINT Check_201205 CHECK (Date >= '2012-05-01' AND Date <= '2012-
05-31')
ALTER TABLE FactDailySales_201204
ADD CONSTRAINT Check_201204 CHECK (Date >= '2012-04-01' AND Date <= '2012-
04-30')
ALTER TABLE FactDailySales_201203
ADD CONSTRAINT Check_201203 CHECK (Date >= '2012-03-01' AND Date <= '2012-
03-31')
ALTER TABLE FactDailySales_201202
ADD CONSTRAINT Check_201202 CHECK (Date >= '2012-02-01' AND Date <= '2012-
02-29')
ALTER TABLE FactDailySales_201201
ADD CONSTRAINT Check_201201 CHECK (Date >= '2012-01-01' AND Date <= '2012-
01-31')
go
CREATE VIEW vueling AS
SELECT * FROM FactDailySales_201205
UNION ALL
SELECT * FROM FactDailySales_201204
UNION ALL
SELECT * FROM FactDailySales_201203
UNION ALL
SELECT * FROM FactDailySales_201202
UNION ALL
SELECT * FROM FactDailySales_201201
go
DECLARE @d date = '20120430'
SELECT SUM(Retail) FROM vueling WHERE Date >= @d
go
DROP VIEW vueling
DROP TABLE FactDailySales_201205
DROP TABLE FactDailySales_201204
DROP TABLE FactDailySales_201203
DROP TABLE FactDailySales_201202
DROP TABLE FactDailySales_201201