Site icon IT Tutorial

SQL Server Check Table Partitioned

Hi,

Partitioning is to create our very large tables or indexes in separate segments.

In other words, when we convert the large table or indexes that appear logically as a whole into a Partitioned structure, we can divide them into smaller physical parts.

SQL Server table Partitioning has a great contribution to performance. So If there are tables and columns which are appropriate for Partitioning, they should make partitioned.
You can check if table is partitioned in SQL Server with following query.
select distinct
pp.[object_id],
TbName = OBJECT_NAME(pp.[object_id]), 
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions pp
inner join sys.indexes i 
on pp.[object_id] = i.[object_id] 
and pp.index_id = i.index_id
inner join sys.data_spaces ds 
on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps 
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf 
on ps.function_id = pf.function_id
order by TbName, index_name ;

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/

Exit mobile version