Pages

Wednesday, September 17, 2014

What is the purpose of PAD_INDEX in this SQL Server constraint?

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
        ^--------------^
         this part here

An index in SQL Server is a B-Tree
  • FILLFACTOR applies to the bottom layer This is the leaf node/data layer in the picture below
  • PAD_INDEX ON means "Apply FILLFACTOR to all layers" This is the root node and intermediate level in the picture below
This means that PAD_INDEX is only useful if FILLFACTOR is set. FILLFACTOR determines how much free space in an data page (roughly)
B-Tree structure

No comments:

Post a Comment