January 5, 2015 at 9:26 pm
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!)
January 6, 2015 at 12:48 am
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.
January 6, 2015 at 1:34 am
This was removed by the editor as SPAM
January 6, 2015 at 2:22 am
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.
January 6, 2015 at 2:24 am
Great question and very relevant to situations I have actually encountered - good food for thought, thanks.
January 6, 2015 at 2:38 am
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.
January 6, 2015 at 2:47 am
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.
January 6, 2015 at 2:50 am
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
WHEREOrderDate < '20140101';
-- Remove data from ActualOrders
DELETEdbo.ActualOrders
WHEREOrderDate < '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!)
January 6, 2015 at 2:52 am
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.
January 6, 2015 at 2:57 am
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.
January 6, 2015 at 4:05 am
Since the beginning, I just don`t like too long questions/stories 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 6, 2015 at 5:05 am
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
January 6, 2015 at 6:02 am
I love a question that makes me think about a real world scenario with practical application of the concepts.
Bravo!
January 6, 2015 at 6:15 am
excellent question and very nicely explained. thanks for sharing
January 6, 2015 at 9:20 am
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 34 total)
You must be logged in to reply to this topic. Login to reply