Partition tables based on month and year in SQL Server

Question

I have a sales table which I need to partition based on month and year of sales

table structure

S_ID           numeric(18,0)
S_CustID       numeric(18,0)
S_TotalCash    numeric(18,0)
S_Paid         numeric(18,0)
S_Date         datetimeoffset(0)

I want to enter sales records between 01/01/2014 to 31/05/2014 to the first sales tables and from 01/06/2014 to 31/12/2014 to the second table and use a partitioned view to insert and select records from both the table so I add a constraint as

ALTER TABLE [dbo].[sales_1]  WITH CHECK ADD  CONSTRAINT [CK_sales_1_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

for the first table and

ALTER TABLE [dbo].[sales_2]  WITH CHECK ADD  CONSTRAINT [CK_sales_2_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

AND used a partitioned view

Select * from Sales_1
union all 
Select * from Sales_2

to insert and update records but when I try to insert data ssms shows error as

"UNION ALL view 'Test.dbo.Sales_View' is not updatable because a partitioning column was not found."

How to solve this?

Answer 1

Firstly, it must be said that using SQL's native table partitioning support is preferred. But that requires SQL Enterprise, which is a lot of money, so, presumably, here we are.

A few things for you to check:

1. Partition column must be part of primary key
2. Partition column must have one and only one CHECK constraint
3. Partitions must not overlap. Perhaps you have a typo, or my eyes fail me, but your constraints on sales_1 and sales_2 overlap.
4. Make sure the constraints are checked (WITH CHECK CHECK CONSTRAINT). Given your definition in the question, it probably isn't the issue, but it is in general a necessary troubleshooting step (take a look at SELECT name, is_not_trusted FROM sys.check_constraints)

EDIT

Apparently DATEPART doesn't work. Probably you should just specify the dates directly (s_date >= '2014-01-01' AND s_date < '2014-06-01'), etc. You could also use persisted custom columns.

See: https://stackoverflow.com/questions/11155758/partitioned-view-not-working

Some other things to note about partitioned views, most of which I ran into quickly:

1. IDENTITY columns will prevent inserts. What I did to get around this was create a "staging" table with the same structure (and an identity column), and add a trigger that just inserts into the partitioned view and deletes from the staging table. At some point I will look into using SQL 2012's SEQUENCEs
2. You can't run an update/delete query on the PV if it has a self-join on the PV or any of its tables
3. All columns have to be specified in an INSERT statement. DEFAULT doesn't work or help. You can't omit nullable columns or columns with a default value.
4. If you use replication, it tends to disable the constraints (NOT FOR REPLICATION), so you'll have to make sure that doesn't happen

You can see here at the bottom: http://technet.microsoft.com/en-us/library/aa933141(v=sql.80).aspx