SQLTeam.com | Weblogs | Forums

Table Partitioning


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.

SaleTime datetime PRIMARY KEY,
ItemName varchar(50),
SalesDept varchar(50)


In above table, I would like to partitioning based on SaleTime
CREATE PARTITION FUNCTION datetime_partitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2010-01-01', '2010-05-30', '2010-12-31')
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.


Thanks you.


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/

That should give you good performance whether the table is partitioned or not.