June 17, 2010 at 6:08 pm
I have a table that I would like to create two partitions out of.
The design is inherited and I cannot change it at this time.
In the example below I would like to have date_id of table DATA_ABC be the partition key. I would most always have to select by joining to the DATA_DATES table by date_id. My question is is I do a select such as .
SELECT *
FROM DATA_ABC a
JOIN DATA_DATES d
ON a.date_id = d.date_id
WHERE d.date = '1/1/2010'
Would there be a benefit of having partitions where the partition range in a select statement is determined by a joined value?
TABLE DATA_ABC
(
abc_id INT Identity
, date_id INT (Would like to have as partition key)
, col2 varchar
, col3 decimal(18,2))
)
TABLE DATA_DATES
(
date_id INT Identity
, date
)
June 18, 2010 at 9:11 am
Well, as usual it depends. 😉
The best way to speed up your query would be to create a clustered index on DATA_ABC with date_id as the first column.
This would mean that the SQL server could immediately find the rows that match the condition.
If this is not possible you could add partitioning.
In this case, partitioning would make it possible for the SQL server to only search the relevat partition instead of searching the wole table.
For this to be any benefit the searched partition must contain much fewer rows than the full table
June 18, 2010 at 9:40 am
Thanks for your reply
Just to verify -
Would there be a benefit of having partitions where the partition range in a select statement is determined by a joined value to the partition key as shown in the example?
Thanks,
June 19, 2010 at 5:14 am
yes, but as I said before, a good index is more important.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply