Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.
Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different drive NCINDEX_FG5
USE [TestDB]
GO
--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}
--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])
--Step 3. CREATE CLUSTERED INDEX 1
-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)
-- Step 4. CREATE NONCLUSTERED INDEX 2 on Separate file group
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO
--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO