We have are SQL 2014 Standard edition, I have a situation where-in I plan to partition a table now since table partition is not supported in standard version I thought about Partitioned views however now I am stuck where I cant make the view writable because of the identity column in the base table.

Do I have any other option in this case ?

Let me know your thoughts

Regards

Identity columns are problematic for a partitioned view. If any of the tables include an identity column, you can delete but not insert or update via the view.  You could instead use a sequence to generate the surrogate key values, but that may require significant application changes.

The real question is why you want to partition.  There are specialized use cases where partitioning may improve performance (e.g. parallel scans) but you will generally get more benefit from index and query tuning.  Partitioning (view or table) can actually degrade performance depending on the queries and workload.  That being said, partitioning always improves manageability.

Be aware that there is additional overhead of partitioned views compared to partitioned tables.  Each table within the view may be indexed differently so there is considerable more work for the optimizer to do to generate an efficient plan.  This increases compilation time and can result in suboptimal plans, particularly if the view contains many tables.  If your table is so large that partitioning is compelling, you might start making the business case for Enterprise Edition in order to meet performance and availability SLAs.

I think the Database Engine forum is a better fit for this question so I'll move this thread there.

just my thoughts - if you think partition really helps/helping your queries- at least read only.

May be you can do the updates straight on the tables itself and use the partitioned view to retrieve the data.

I think, out of all us, you would know better whether the partition view is really helping you based on the workload and type of queries you execute against.

So, i am thinking more like having a procedure that can dynamically, figure out which table the data/update will go into and build the sql statement and do the update/insert/delete. in short, use dynamic sql - generate sql string to do updates/inserts/deletes on the tables itself.

whether this is worth it or should you upgrade to enterprise , i guess you will know as you monitor the application. 

You could write an INSTEAD OF trigger that directs the row the appropriate partition. But it is a little iffy, since the code that invokes the trigger cannot retrieve the IDENTITY value with scope_identity(). @@IDENTITY will work - as long as there are no AFTER triggers that insert into tables that themselves have an IDENTITY column.

Yes, views in general are not always updatable.

When I've used partitioned views, the insert code has always worked directly against individual tables.

For that matter, partitioned views (and partitioned tables) are infamous for not automatically returning the efficiency you might expect even for selects.

They are both techniques good for managing space, but disappointing for managing DML.

Josh