The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error
to something like print 'HELLO!'
; in that case, you will NOT see 'HELLO!' printed; you will see the error instead. Books Online has a list of cases of errors which you cannot catch.
An alternative here is to SET XACT_ABORT ON
before you begin your transactions. Then, you can rollback your changes upon getting your first error.
Here are two best practices for partitioning that pertain to the question:
- Keep an empty staging partition at the leftmost and rightmost ends
of the partition range to ensure that the partitions split when
loading in new data, and merge, after unloading old data, do not
cause data movement.
- Do not split or merge a partition already populated with data
because this can cause severe locking and explosive log growth.
http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5
If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.
To check if the leftmost partition is empty, use a query like the following:
DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';
SELECT
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;
If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.
Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.
Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.
Best Answer
Unfortunately this is not possible in SQL Server - you can of course create a backup, or create snapshots on the server (HyperV for example) to accomplish this. Other than that, it's not possible to only do it for a table - the closest I'd consider is differential back/restore.