Menu
I have the following constraint being applied to one of my tables, but I don't know what PAD_INDEX means.
Can someone enlighten me?
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ^--------------^ this part here 
228
PAD_INDEX: Used to apply the free space percentage specified by FillFactor to the index intermediate level pages during index creation. FILLFACTOR: Used to set the percentage of free space that the SQL Server Engine will leave in the leaf level of each index page during the index creation.
Every time that you rebuild an Index in SQL Server, you can use the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options to specify that SQL Server should acquire Row and Page Level Locks when accessing your data for reading and writing.
Oracle enforces a UNIQUE key or PRIMARY KEY integrity constraint by creating a unique index on the unique key or primary key. This index is automatically created by Oracle when the constraint is enabled; no action is required by the issuer of the CREATE TABLE or ALTER TABLE statement to create the index.
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects.
Basically, you set PAD_INDEX = ON if you expect a lot of random changes to the index regularly.
That helps avoiding index page splits.
I set it on when I expect 30%+ of random records included in the index to be deleted on a regular basis.
189
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 intermediate levels in the picture below (between root and data)
This means that PAD_INDEX is only useful if FILLFACTOR is set. FILLFACTOR determines how much free space in an data page (roughly)
A picture from MSDN:
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
