Does SQL Server support table partitioning?

Question

I have a table with over 40 million rows.  I would like to partition the table base on the creation date.  Does SQL Server support table partitioning?

Answer

Even with the fastest hardware and optimally-written queries, there comes a point in the size of a table where there are just too many rows to handle quickly. In this user's case, it looks like 40 million rows is that point. For you, it might be more or less, depending on your situation.

Fortunately, in SQL Server 2000 and 2005, there is what is called "partitioned views." Essentially, what a partitioned view does is to allow you to divide your data into multiple tables (instead of one very large table), and to place these tables one one or more SQL Servers. What a partitioned view does is to join the data in all of the tables so that it appears that there is only a single table.

For example, you might divide a large table by year, or month (and year), or using some method that makes logical sense. This way, each table will be much more manageable. These multiple tables might be located on a single physical SQL Server (local partitioned view), or on multiple SQL Servers (distributed partitioned view). A distributed partitioned view is implemented on what is called a SQL Server federation of servers.

Both options allow you to more easily manage data and can contribute to faster performance.