As part of performance improvement techniques, partitioning the table or index may improve the query performance depending upon the query types and hardware configuration. SQL Server 2005 introduced the data partitioning solutions and from the beginning of SQL Server 2008, this feature has been enhanced with native table and index partitioning, which provides better scalability to the data platform.
As the data grows, tables grow larger and similarly, the data access time also tends to increase. By designing the data partitioning solutions with appropriate hardware, we will be able to build a scalable and high performance platform. In this recipe, we will look at designing data partitioning solutions for tables and indexes using SQL Server 2008 R2. This solution will essentially allow us to manage large tables and indexes at a lower level of granularity. The partitioning solution is differentiated as horizontal and vertical.