3

What is the purpose of WITH options (STATISTICS_NORECOMPUTE,PAD_INDEX, ALLOW_ROW_LOCKS,ALLOW_PAGE_LOCKS)?

I have the following constraint being applied to one of my tables, but I don't know what this all means.

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  )

Can someone enlighten me?

1
  • IDK why this question was closed, make perfect sense to me.
    – not2qubit
    Commented Oct 24, 2022 at 18:30

1 Answer 1

11

These are not constraints. These are table options. Microsoft documentation can enlighten you, there is a clear and concise explanation for each of these options:

PAD_INDEX = { ON | OFF } When ON, the percentage of free space specified by FILLFACTOR is applied to the intermediate level pages of the index. When OFF or a FILLFACTOR value it not specified, the intermediate level pages are filled to near capacity leaving enough space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages. The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF } When ON, out-of-date index statistics are not automatically recomputed. When OFF, automatic statistics updating are enabled. The default is OFF.

IGNORE_DUP_KEY = { ON | OFF } Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF } When ON, row locks are allowed when you access the index. The Database Engine determines when row locks are used. When OFF, row locks are not used. The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF } When ON, page locks are allowed when you access the index. The Database Engine determines when page locks are used. When OFF, page locks are not used. The default is ON.

Not the answer you're looking for? Browse other questions tagged or ask your own question.