CODEWITHSUNDEEP
sqlsql-serversql-server-2008
vietean
vietean

Reputation: 3033

How to partition a SQL Server table with a modulus function

I have an Orders table with some basic info like below:

+---------+---------+------------+-----------+
| OrderId | StoreId | StoreName  | ProductId |
+---------+---------+------------+-----------+
|       1 |    1001 | Store 1001 |       123 |
|       2 |    1002 | Store 1002 |       124 |
|       3 |    1003 | Store 1003 |       125 |
+---------+---------+------------+-----------+

I would like to partition this table by expression: StoreId MOD 10. I think that it can divide this table to 10 smaller tables.

How can I solve it using SQL Server?

Upvotes: 0

Views: 1822

Answers (2)

EzLo
EzLo

Reputation: 14199

The MOD operator is % in SQL Server. Keep in mind that if you want non-zero numbers you should add 1 to the result.

SELECT
    StoreOrderGroup = (StoreId % 10) + 1,
    O.*
FROM
    Orders AS O

If you want to create 10 different tables with the same structure, you can use the resulting StoreOrderGroup to SELECT INTO and create them. This won't replicate indexes or constraints.

DECLARE @StoreOrderGroup INT
DECLARE @StoreOrderGroupTable VARCHAR(100)

DECLARE StoreOrderGroupCursor CURSOR FOR
    SELECT DISTINCT
        StoreOrderGroup = (StoreId % 10) + 1,
        StoreOrderGroupTable = QUOTENAME('OrdersStore' + CONVERT(VARCHAR(200), (StoreId % 10) + 1))
    FROM
        Orders
    ORDER BY
        StoreOrderGroup ASC

OPEN StoreOrderGroupCursor
FETCH NEXT FROM StoreOrderGroupCursor INTO @StoreOrderGroup, @StoreOrderGroupTable

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQLCreate VARCHAR(MAX) = '
        SELECT 
            * 
        INTO 
            ' + @StoreOrderGroupTable + ' 
        FROM 
            Orders
        WHERE
            (StoreId % 10) + 1 = ' + CONVERT(VARCHAR(200), @StoreOrderGroup)

    EXEC (@DynamicSQLCreate)

    FETCH NEXT FROM StoreOrderGroupCursor INTO @StoreOrderGroup, @StoreOrderGroupTable

END

CLOSE StoreOrderGroupCursor
DEALLOCATE StoreOrderGroupCursor

However, if you want to keep the same table but with different partitions, then you need to:

  1. Create different filegroups to store each partition, with at least 1 file each and preferably on different hard drives. This is recommended but not mandatory.
  2. Create a PARTITION FUNCTION which will recieve the store mod result as input parameter and return which partition it should be assigned to. The boundary limit values would be the integer numbers (careful with LEFT or RIGHT ranges).
  3. Create a PARTITION SCHEME to link the partition function of point 2 with different filegroups from point 1. This will tell the engine in which filegroup should it store each partition.
  4. Create a new table with your partition scheme and store all your orders.

A quick example:

ALTER DATABASE YourDatabase ADD FILEGROUP SuperFastFileGroup;  
GO  

ALTER DATABASE YourDatabase ADD FILEGROUP SuperSlowFileGroup;  
GO  

ALTER DATABASE YourDatabase   
ADD FILE   
(  
    NAME = FastFile,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FastFile.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP SuperFastFileGroup;  
GO

ALTER DATABASE YourDatabase   
ADD FILE   
(  
    NAME = SlowFile,  
    FILENAME = 'D:\SQL Server\SlowFile.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP SuperSlowFileGroup;  
GO  


CREATE PARTITION FUNCTION OrderStoreMod10 (int)
    AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);  
GO  


CREATE PARTITION SCHEME OrderStoreScheme  
    AS PARTITION OrderStoreMod10  
    TO (
        SuperFastFileGroup, -- 1 or lower
        SuperFastFileGroup, -- 2
        SuperFastFileGroup, -- 3
        SuperFastFileGroup, -- 4
        SuperFastFileGroup, -- 5
        SuperFastFileGroup, -- 6
        SuperFastFileGroup, -- 7

        SuperSlowFileGroup, -- 8
        SuperSlowFileGroup, -- 9
        SuperSlowFileGroup); -- higher than 9
GO  


CREATE TABLE NewOrders (
    -- Your columns...
    StoreID INT,
    StoreOrderGroup AS StoreID % 10) -- ComputedColumn
    ON OrderStoreScheme (StoreOrderGroup);  
GO  

-- Insert your data into the new table

Please read this post before using partition schemes on a table split by your StoreID, it might not be a good idea.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37430

Try this:

SELECT StoreId%10 [StoreId mod 10]
      ,StoreId 
      ,OrderId 
      ,StoreName  
      ,ProductId
FROM Orders
order by StoreId%10, OrderId

Upvotes: 1

Related Questions