Partition the fact tables based on 2 columns

himabindu_nvn,

I believe the answer for SQL 2005 and 2008 is you cannot. You must partition on one column.

Even if you could, just as a clarification, why would you want to? If the ArticleID is random within a given NewsDate range, are you sure you want to create a PARTITION by ArticleID | NewsDate combination? That would result in for each NewsDate range, a partition for each ArticleID range within it. Is that what you want? i.e., sub-partitioning?

I would think you would want to partition by NewsDate and using date range filter to the correct partition to find data which includes random ArticleIDs.

Anyway, it cannot be done, or at least I have not found any documentation stating it has been added to newer versions of SQL, including Denali. Therefore, I will have to stick with you cannot until someone educates me otherwise. Sorry.

Kevin

Thanks for the response. I have already partitioned another fact table based on NewsDate. Can I use NewsDate again to partition another fact table?

Now, what you could do is create a composite key that represents your date + article id, just ensure to put it together in a fashion that scales with your data. For example if your ArticleID is BIGINT, it can have 19 total digits; therefor you could in theory do something like this in a computed column:

DECLARE @ArticleID BIGINT, @NewsDate DATETIME;
SET @NewsDate = '2011-01-01'
SET @ArticleID = 5100000 

SELECT CONVERT(NUMERIC(38,0), 
               CONVERT(BIGINT, 
                       @NewsDate
               ) * POWER(10.0,19) + @ArticleID
       ) AS Option1
     , CONVERT(NUMERIC(38,0), 
	           CONVERT(BIGINT, 
			           CONVERT(CHAR(8), 
                               @NewsDate, 
                               112
                       )
               ) * POWER(10.0,19) + @ArticleID
       ) AS Option2
;

Select allOpen in new window

Option1 ==> 405420000000000005100000
Option2 ==> 201101010000000000005100000

Whichever you choose, you can use the resulting value as your partitioning column.
You will have to remember to build your searches based on this longer column, though, so that is the trade off.