Partitioning Protheus tables in the DBMS

Overview

In SQL Server, a partitioned table remains one logical entity with rows spread across partitions in the same database. Partition elimination depends on predicates and the chosen column; more partitions do not guarantee improvement. Aligned indexes and uniqueness have constraints that can conflict with ERP standard keys. This is not branch-sharing configuration or separate company tables. The query below inventories heaps/clustered indexes and approximate row counts only; it performs no DDL and does not certify a Protheus design. (Sources: refs. 1–3.)

Syntax

SQL Server: sys.tables + sys.indexes + sys.partitions

Commented example

-- SQL Server: user-visible metadata only; no DDL.
SELECT s.name AS schema_name,t.name AS table_name,i.name AS index_name,
       p.partition_number,p.rows AS approximate_rows
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
JOIN sys.indexes AS i ON i.object_id=t.object_id
JOIN sys.partitions AS p ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE t.is_ms_shipped=0 AND i.index_id IN (0,1)
ORDER BY s.name,t.name,p.partition_number;

Related content