SQLTeam.com | Weblogs | Forums

Setup Table Partitioning but All Data is in One Partition


#1

This is SQl Server 2014. I configured partitioning for a new table based on a date column. But when I populated the table, all rows ( millions) went into a single partition, rather than being spread across several.
Any idea what may have gone wrong?
Thanks, Jack


#2

what was the partition criteria?Month?Year?
can you run this query and check the output.

SELECT
p.partition_number AS PartitionNumber,
f.name AS PartitionFilegroup,
p.rows AS NumberOfRows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'TableName'


#3

Solved the mystery. I had to reformat the values in the partition function to match the actual columns data format. That's all it was. Thanks