we can have logical and physical Table Partitioning in SQL server.
Can we create both?
one partition to store the historic data and other for parallel insert.
CREATE TABLE SalesOrders
(
SaleTime datetime PRIMARY KEY,
ItemName varchar(50),
SalesDept varchar(50)
)
In above table, I would like to partitioning based on SaleTime
GO
CREATE PARTITION FUNCTION datetime_partitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2010-01-01', '2010-05-30', '2010-12-31')
GO
At teh same time, loical partition on SalesDept so that I can parallelly insert the data in table.
CREATE PARTITION FUNCTION SalesDept_partitions (varchar(50))
AS RANGE RIGHT FOR VALUES ( 'DeptA', 'DeptB', 'DeptC')
Please advise.
If you are trying to turn a linear division along the time line into a matrix of time and SalesDept then I don't see how, at least not directly. It would be possible, I suppose, to have a column that was the SalesDept combined with SaleTime CONVERTed to a string and partition on that string but that sounds like a bit of a kludge. The CONVERT would need to produce a string that was in sync with the chronology (e.g., YYYYMMDD HH:MM)
do you meant to have partitioning based on date and SalesDept considering both column as varchar.
how that wil l works.. it is logical?
I'm suggesting that it is a bad idea BUT you COULD create a varchar column that was a combination of the SalesDept and the SalesTime (after it is CONVERTed to a string). If you had this column, you could partition on it.
No, you can't partition on two different columns.
But you may not really need to. Have you tried clustering on:
( SaleTime, SalesDeptId ) /you should not use the dept name but a numeric value that represents the name/
instead?
That should give you good performance whether the table is partitioned or not.