Today I want to review the concept of the INSTEAD OF trigger, and the original reason it was added to SQL Server.
When earlier versions of SQL Server were continuing to be adopted by bigger and bigger data sets it would sometimes degrade in performance as the number of records increased. We started getting millions, billions, and even trillions of records in a single table because the data was important, and the cost to maintain it had dropped enough to retain that much data.
What many DBAs starting doing was rolling their own Partitioned Table implementation. Access to the data was through stored procedures completely, and the partitioning logic was built directly into the stored procedure. In an effort to provide a common framework for doing this sort of thing, SQL Server was enhanced to implement Federated Views.
A Federated View was a view that knew all of the different tables that hosted a Partition for the consolidated results. It was a series of SELECT statements with a UNION ALL operation between each. As a result, any consumer of the view
could retrieve data from any of the partitions through the view, without knowing how the partitions are maintained, etc.
The only problem with the view was that it could not write to the tables because it did not have the logic to resolve what table contained what data specifically. It was ignorant of the partitioning logic. To solve this problem, Microsoft added INSTEAD OF triggers. Now the developer of the view could create an INSTEAD OF trigger, implementing the partition logic, allowing the correct table instance to experience the desired Insert, Update, or Delete operation.
Now you can see the power of Partitioned Tables. All you have to do today is create a partition function, and then apply it to your partitioned table, and add new instances of your table into the cluster utilizing that function. Using the partition function, SQL Server is able to access the desired table from the appropriate physical instance of your partitioned table.
The old school Federated Views are still supported in SQL Server today, and may have a place in your database if there is a reason to not use Partitioned Tables.
Cheers,
Ben