2

I am attempting to write a test for already partitioned schemas.

The test should confirm that each schema was partitioned # number of times where # = sys.partition_functions.fanout.

I have found the following code: (according to my understanding gives the number of partitions per schema)

select distinct t.name
from sys.partitions p 
inner join sys.tables t on p.object_id = t.object_id
where p.partition_number <> 1

I am currently attempting to understand how to connect each table to the partition function 'fanout' value.

Ideally I would like to be able to create a table with the following columns: table name, number of partitions, sys.partition_functions.fanout

0

Not sure why you want to do this, but some of the dmv links are a bit tricky to figure out from BOL. I believe the following meets your requirements:

SELECT t.name, MAX(pf.fanout) fanout, COUNT(*) cnt_partitions
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.[type] IN (0, 1, 5) -- heap, CI, or CCI
GROUP BY t.name;

If I create a few partitioned tables:

CREATE PARTITION FUNCTION functioning_part_function
(BIGINT)
AS RANGE LEFT
FOR VALUES (
  0
, 5000000
, 10000000
, 15000000
, 20000000
, 25000000
, 30000000
, 35000000
, 40000000
, 45000000
, 50000000
); 

CREATE PARTITION SCHEME scheming_part_scheme
AS PARTITION functioning_part_function
ALL TO ( [PRIMARY] );

CREATE TABLE dbo.PART_TABLE (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

CREATE TABLE dbo.PART_TABLE_2 (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

I get the following results:

╔══════════════╦════════╦════════════════╗
║     name     ║ fanout ║ cnt_partitions ║
╠══════════════╬════════╬════════════════╣
║ PART_TABLE   ║     12 ║             12 ║
║ PART_TABLE_2 ║     12 ║             12 ║
╚══════════════╩════════╩════════════════╝

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.