Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ingest strategy when we switched to a partitioned table. Of course the queries were changed, but nothing for the actual ingest. By strategy, I am simply referring to the actual insert statements. I can't imagine that there is any deviation, but wanted to double check.
I realize that the partition strategy itself can cause contention. For example, if partitioned by date and all of the data being loaded is for the current time period you will be loading into the same partition.
Any suggestions / comments are welcome. I have searched and ready Brent Ozar's articles, but haven't seen anything that would lead me to believe I need to do anything differently.
Below are examples of our scheme and function that we use to partition the data based on a date field that exists within the table. The structures below allow for us to dynamically update the function and schemes to add more date ranges. As you can see from the function, all current rights will contend with each other because they are all hitting the same partition. However, it really helps with our queries.
CREATE PARTITION SCHEME [Datetime2_Scheme] AS PARTITION [Datetime2_Function] TO ( [Partition1] , [Partition1] );
CREATE PARTITION FUNCTION [Datetime2_Function](datetime2(7)) AS RANGE RIGHT FOR VALUES ( N'2015-10-01T00:00:00.000' , N'2015-11-01T00:00:00.000' );
As always greatly appreciate the feedback from Stack Overflow folks.