Is Multi level partitioning possible in SQL Server

43 Views Asked by At

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]); 
0

There are 0 best solutions below