Sql-server – Obtaining the next partition number

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation):

• drop all unaligned indexes
• create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition
• switch the last partition into the staging table. now the data has moved into the staging table, the last partition of the real table is empty
• split the partitioning function as needed, since the last partition is empty the operations will be instantaneous. Remember to leave an empty last partition
• create a temporary partitioning scheme for the staging table aligned with the partitions in the tail of the real table
• rebuild the staging table on the temporary partition scheme created above. Now the staging table is aligned with the empty partitions in the tail of the real table. If the rebuild has log space issues use an online rebuild and take frequent log backups. consider also enabling bulk_logged mode.
• switch each partition one by one from the staging table into the real table.
• add back any unaligned index you need (you shouldn't have unaligned indexes to start with if you ask me...)
• drop the staging table and temp partitioning function/scheme
• start maintaining the partitioning so you don't have to repeat this next time.