CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 34034

Difference between Partition and Page in SQL Server?

What is the difference between partition and page in SQL Server? Is these are available by default or we need to create explicitly?

Upvotes: 3

Views: 1040

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

Page is the most basic element of storage in SQL Server.

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Partition:- Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions.

SQL Server 2012 supports up to 15,000 partitions by default. In earlier versions, the number of partitions was limited to 1,000 by default. On x86-based systems, creating a table or index with more than 1000 partitions is possible, but is not supported.

Upvotes: 4

Related Questions