Partitioning Question
-
I have a right partition function that uses a date field as the partition column.
After incorporating the partition function into my table by creating a clustered index, I inserted 1000 rows into the table.
When I look at the partition columns using the sql below, I see that the 1000s rows got correctly added to the correct partition (Partition Number 2)
but I also see that Partition Number 1 also has the 1000 rows. Is that supposed to happen? Why would both partitions have 1000 rows?
Any help is much appreciated.
select o.name, p.partition_number, p.rows
from sys.objects o
join sys.partitions p on (o.object_id=p.object_id)
where o.type='U'
order by o.name
-
They wouldn't. Is that second entry perhaps a non-partition aligned nonclustered index (you can't have a nonclustered index on a single partition and a partition-aligned one would have the same row distribution as the table). If an index is not partition-aligned, it'll only have one partition - partition 1.
Add index_id to your query. The table will be 1 (clustered index), above that is nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply