I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:
USE [Test]
GO
CREATE TABLE [IDRanges](
[ID] [int] NOT NULL
)
GO
CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;
You should see one record in each partition before the update, and both records in the first partition afterwards.
Assuming that you have the primary key on a clustered index then the partitioning key needs to be part of the primary key.
You will not loose the benefit of partitioning by joining to non-partitioned tables, providing that the queries are designed to make use of the partitioned table, for example the following query WILL benefit from partitioning
SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE F.Col1 = 5
But the following query WILL NOT benefit from partition elimination
SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE D.Col1 = 5
It is a subtle difference, but in the first query, the join key is filtered in the partitioned table, taking advantage of elimination and then joined to the dimension. In the second query, the key is filtered in the dimension and then joined against the whole of the fact table, rather than just required partitions.
It goes without saying that the partitioning key needs to be in the WHERE
clause for elimination to work, otherwise SQL Server does not know which partition(s) the data is in.
Adding a filter criteria on the JOIN
clause will not help you. It needs to be in the WHERE
clause to benefit from elimination.
The Partition Key does not need to be part of a non-clustered index (NCI) but if the NCI is unique, then it needs to contain the partitioning key in order to align the index. This is where the NCI is built on the same partition scheme as the table. NCIs should also be partition aligned unless there is an exceedingly good reason not to. I have never come across a good enough reason!
Best Answer
Not at all.
One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.
For instance, if you have a table
Orders
with the fieldOrderDate
you would most likely partition based on the month and year ofOrderDate
.When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.
Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).
EDIT
For part 2, I think the answer is no as well. The partition key is used to determine which partition to put the row in, but I don't think an index is maintained. There may be stats in the back end on it though.