how to get the number of records per partition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to get the number of records per partition

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-28 : 08:52:37

How can I get the number of rows per parition for a database without having to do a count?

This gets the row count for all partitions per table, but not each individual partition:

SELECT OBJECT_NAME(object_id), [rows]
FROM sys.partitions p
WHERE index_id = 0

Thanks

Hearty head pats

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-28 : 10:46:44

[code]
SELECT t.name [table], p.rows
FROM sys.tables t
JOIN sys.partitions p
On p.object_id = t.object_id
WHERE is_ms_shipped = 0
order by [table][/code]

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-28 : 11:23:03

Thanks Russell

Hearty head pats

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-28 : 11:29:34

You're welcome

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-28 : 11:52:44

hey russell

I don't suppose you know how to return the boundary value for the partitions as well?

I have to leave now, but I'll thank you later

Bex

Hearty head pats

parody
Posting Yak Master

111 Posts

Posted - 2010-07-29 : 05:01:16

sys.dm_db_partition_stats also contains this info.

Have a look at sys.partition_range_values for boundry values of the function.

I think these are all the system views available:

sys.partition_range_values
sys.partition_functions
sys.partitions
sys.partition_parameters
sys.partition_schemes
sys.dm_db_partition_stats

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-29 : 09:05:50

Hi Parody

Thanks for getting back to me.

I've played around with the various system views, and have developed the following query. I have made an assumption, however, that the partition_number in sys.partition is the same as the boundary_id in sys.partition_range_values.

Is this a correct assumption?

SELECT	t.name [table], p.rows, p.partition_number, v.boundary_id, v.value
FROM	sys.tables t
JOIN	sys.partitions p
On	p.object_id = t.object_id
INNER JOIN	sys.partition_range_values v 
ON	v.boundary_id = p.partition_number 
WHERE	is_ms_shipped = 0
order by [table]

Bex

Hearty head pats

parody
Posting Yak Master

111 Posts

Posted - 2010-07-29 : 09:47:35

Hmm almost, you would always have a 6th partition to the right of the 5th boundry ID (i.e. you cut an apple 5 times you hev 6 pieces).

so you would need to left join. Also remember your boundry definition of right and left defining which partition an on boundry value will fall. This is the boundry_value_on_right column in sys.partition_functions (a 1/0 - so 1 is right 0 is left)

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-07-29 : 10:04:19

Cool, that makes sense.

Thanks for your help!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources