'How to create composite partition in SQL Server?

I want to create a sample database using composite partition. I know about Range Partition and List Partition. But, I don't have enough knowledge about Hash Values and how to create Hash Partition in my database?. So, I have decided that I should make a sample database using Composite Partition and I want to use Range Partition and Hash Partition in it. Can anybody describe it more and in easy word so, i can understand well about Hash Partition as well as Composite Partition.

I have also read some documents on internet. But, I could not understand how to create Hash Partition and How to create Composite Partition in my database. Actually I don't have enough knowledge about Hash Value and Hash Functoin. I have read about it but, I could not understand very well. I need a simple definition.

  1. Definition of Horizontal Partition & Vertical Partition
  2. Partition (database)
  3. Hash Functions
sql-serversql-server-2008database-partitioning


Solution 1:[1]

Composite Partitioning feature is not available in SQL Server 2008. Only Range Partitioning is available in SQL Server.

Solution 2:[2]

Although the partitioning column must be a single column, it does not need to be numeric and it can be calculated so that the range can include multiple columns.

For instance it is common to partition on datetime data by month. This will work well, because that data is usually in a single column, but what do you do if you have data for multiple companies and you also want to partition by company? For this you could use a computed column for the partitioning column. This will create a computed column using the ‘company id’ and ‘order month’ which is then used for the partitions. It will partition three companies for the first three months of 2007.

the computed column must be persisted to form the partitioning column.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES    (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) 

CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) 

CREATE TABLE CompanyOrders   
 (    Company_id   INT ,    
OrderDate    datetime ,    
Item_id     INT ,    
Quantity    INT ,    
OrderValue   decimal(19,5) ,    
PartCol AS Company_id * 10000 + CONVERT(VARCHAR(4),OrderDate,112) persisted   
 ) ON MyPartitionScheme (PartCol)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vivek Periaswamy
Solution 2 Sayadian

Related Questions