October 19, 2022 at 3:45 pm
Hello,
I recently performed partitioning on a table and the column I partitioned by was a postcode column and these were the steps:
1. Create a file and a filegroup for each postcode
2. Create the partition function that will divide the data into postcodes
3. Create the partition scheme
4. Create a clustered index on the partitioned column
It is working now fine. Currently, my big issue is that the data for this table is now on the PRIMARY filegroup and also distributed on the new filegroups.
So, 1- Is there a way to remove the data on the PRIMARY filegroup and have the table read its data from the partitioning files?
2- When new data is entered to this table, how do I have it added to its respective filegroup?
This issue I've been working on for a while and would really appreciate any assistance.
October 19, 2022 at 3:56 pm
The PARTITION SCHEME tells SQL which filegroups to use for the partitions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 19, 2022 at 5:26 pm
What makes you think the data is also in the primary file group?
I borrowed this code from somewhere and I use it to look at partitions. It should show you the partition details for the table including the file groups.
Did you create a partition for every post code? or make ranges? Every post code sounds like it would be thousands. If you included a handful of post codes in the partition function you may have inadvertently created ranges and when a new post code is inserted it will fall somewhere in an existing range.
SELECT OBJECT_NAME(o.object_id) AS TableName,
s.[name] AS SchemaName,
i.index_id AS IndexID,
i.[name] AS IndexName,
ds.[name] AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.[name] AS FileGroupName,
prv_left.[value] AS LowerBoundaryValue,
prv_right.[value] AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN 'RIGHT'
ELSE 'LEFT'
END AS RangeType,
p.[rows] AS CountRows
FROM sys.objects AS o
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
JOIN sys.partitions AS p ON o.OBJECT_ID = p.OBJECT_ID
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
WHERE OBJECT_NAME(o.object_id)='TableName'
--AND s.[name] = 'SchemaName'
ORDER BY IndexID, PartitionNumber
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply