Partition Insertion Issue while inserting data

Question

I have table ABC in Oracle which has partition b100 and b200 which holds data like 100 and 200 accordingly. When we try to insert data apart from these Oracle developer throws an error.

Now when we try to implement the same thing in SQL SERVER, the data apart from these partion values are also getting inserted in the table. So how to restrict the unpartitioned from Insertion

In SQL SERVER the unpartitioned data is getting into PRIMARY filegroup or the last filegroup. Which we are not expecting

Answer 1

SQL Server table partitioning is natively range partitioning and does not constrain data. There is always a partition for every possible partitioning column value.

Use a CHECK constraint (or alternatively a FOREIGN KEY) to limit values that can be inserted into the table. These can be the same as the partition boundaries to effectively implement partitioning on an exact list of values instead of range.

ALTER TABLE dbo.YourPartitionedTable
    ADD CONSTRAINT CK_YourPartitionedTable_YourPartitioningColumn
    CHECK (YourPartitioningColumn IN (100,200));

The last partition will always be empty in the case of a RANGE LEFT partition function. Conversely, the first partition will be empty with a RANGE RIGHT function.