I have a table with 7 columns in it. Table consist of 30 millon records. I already did range partition on date column. Now I want to further create sub-partition or partition by partition. I'm thinking to create list subpartition.
Is that possible to create sub-partition in SQL Server?
Is the below partition table is valid one.is this doing similar to multi-level partitioning
CREATE TABLE log
(
USER_ID INT,
R_ID INT ,
IP_ADDRESS VARCHAR(255),
ACCESS_TIME DATETIME,
H_ID INT --range 1-5
-- ... few more columns.
PRIMARY KEY(USER_ID,ACCESS_TIME)
)ON AccessTimePartitionScheme(ACCESS_TIME);
CREATE NONCLUSTERED INDEX IX_log_H_ID
ON log (H_ID)
ON SubPartitionSchemeH_D (H_ID);
CREATE PARTITION FUNCTION SubPartitionFunctionH_ID (INT) AS
RANGE LEFT FOR VALUES (1, 2, 3, 4, 5);
CREATE PARTITION SCHEME SubPartitionSchemeH_ID AS
PARTITION SubPartitionFunctionH_ID ALL
TO ([PRIMARY]);