Creating partition on SQL Server 2016 standard edition
-
I have created a partition on a table which is close to 170 GB in size. Since I have never worked with partitioning before, I just have couple of questions. The create partition code is below:
Create Partition Function ActivityDate_Partition (datetime)
as Range Right For Values ('20170701', '20170801', '20170901', '20171001', '20171101')
Does it mean that I have created 5 partitions? The reason what confuses me is that let's say I would like to delete data from August. Do I run "delete from table where date between 08/01 and 08/31 or just drop the August partition and it will delete the whole August data? -
Hi,
You have partition function for the 6 values, so you have 7 ranges. In this case you need a partition scheme with 7 partitions.
Check the Microsoft example - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);3 values, 4 ranges, 4 partitions
- Syed Razi - Thursday, November 16, 2017 11:38 AM
The partition function will create 6 partitions (partition 1 <20170701, partition 2 >=20170701 & <20170801 etc). The SQL below will highlight partitions/ row counts on the clustered index for all partitioned tables in the DB. Try running some inserts/ deletes to see which partitions the data resides in. Depending on your version of SQL you can either truncate the partition or SWITCH it out into a secondary table. Remember to test before running anything in prod.
SELECT
DB_NAME() AS database_name,
OBJECT_NAME(p.OBJECT_ID) AS table_name,
p.index_id AS index_id,
CASE
WHEN p.index_id = 0 THEN 'HEAP'
ELSE i.name
END AS index_name,
CASE
WHEN p.index_id IN (0,1) THEN p.row_count
ELSE 0
END AS row_count,
p.partition_number AS partition_number,
prv_left.value AS lower_boundary,
prv_right.value AS upper_boundary,
ps.name AS partition_scheme,
pf.name AS partition_function,
CASE
WHEN fg.name IS NULL THEN ds.name
ELSE fg.name
END AS file_group_name,
CAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS used_pages_mb,
CAST(p.in_row_data_page_count * 0.0078125 AS NUMERIC(18,2)) AS in_row_pages_mb,
CAST(p.reserved_page_count * 0.0078125 AS NUMERIC(18,2)) AS reserved_pages_mb
FROM sys.dm_db_partition_stats p
JOIN sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.data_spaces ds
ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.partition_functions pf
ON ps.function_id = pf.function_id
LEFT JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups fg
ON fg.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values prv_right
ON prv_right.function_id = ps.function_id
AND prv_right.boundary_id = p.partition_number
LEFT JOIN sys.partition_range_values prv_left
ON prv_left.function_id = ps.function_id
AND prv_left.boundary_id = p.partition_number - 1
WHERE ps.name IS NOT NULL
AND i.index_id = 1
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply