PAD_INDEX in SQL Server

PAD_INDEX is the option we specify while index creation/Rebuild as below.

Pad_Index {ON|OFF}

Pad_Index is only useful when fill factor is specified in index definition, if PAD_Index is ON, it means fill factor value will be applied to intermediate nodes of balance tree formed by index.

If PAD_Index is OFF, it means fill factor value will not be applied to intermediate nodes of balance tree.

/* PRACTICAL SCENARIOS

 CREATE TEST TABLE EMPLOYEE_PADINDEX  */

CREATE TABLE Employee_PadIndex

(Id INT NOT NULL,

Name VARCHAR(20)

)

/*INSERT SOME TEST RECORDS*/

INSERT INTO Employee_PadIndex

SELECT 1,'Ram'

UNION ALL

SELECT 2,'Milan'

UNION ALL

SELECT 3,'Sandy'

UNION ALL

SELECT 4,'Babu'

UNION ALL

SELECT 5,'Allen'

/* HERE FILL FACTOR WILL NOT BE APPLICABLE FOR CLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = OFF */

CREATE CLUSTERED INDEX  Idx_Employees_ID on Employee_PadIndex(id)

WITH (FILLFACTOR = 80, PAD_INDEX  = OFF) ON [PRIMARY]

/* HERE FILL FACTOR WILL BE APPLICABLE FOR NONCLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = ON */

CREATE NONCLUSTERED INDEX  Idx_Employees_NAME on Employee_PadIndex(NAME)

WITH (FILLFACTOR = 80, PAD_INDEX  = ON) ON [PRIMARY]

/* HERE FILL FACTOR WILL BE APPLICABLE FOR CLUSTERED INDEX INTERMEDIATE NODES AS PAD_INDEX  = ON */

ALTER INDEX  Idx_Employees_ID on Employee_PadIndex

REBUILD

WITH (FILLFACTOR = 80, PAD_INDEX  = ON ) 

/* DROP ABOVE CREATED CLUSTERED INDEX */

DROP INDEX  Idx_Employees_ID on Employee_PadIndex

/* CREATE PRIMARY KEY WITH CLUSTERED INDEX WITH OPTIONS

HERE FILL FACTOR WILL NOT BE APPLICABLE FOR INTERMEDIATE NODES AS PAD_INDEX  = OFF */

ALTER TABLE Employee_PadIndex

ADD CONSTRAINT [PK_Employees_Id] PRIMARY KEY CLUSTERED 

(

    [Id] ASC

) WITH (FILLFACTOR = 80,PAD_INDEX  = OFF) ON [PRIMARY]

    

/* DROP TEST TABLE EMPLOYEE_PADINDEX */

DROP TABLE Employee_PadIndex

Note:

Please try above scenarios in Test/Development Environment only.