The following example creates a table and defines a primary key on the column TransactionID in the AdventureWorks database.
CREATE TABLE Production.TransactionHistoryArchive1
(
TransactionID int IDENTITY (1,1) NOT NULL
, CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
)
;CONSTRAINT [PK_tbTable01_Col01] PRIMARY KEY CLUSTERED
(
[Col01] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgGroup01]PAD_INDEX
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified: The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.
The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.
STATISTICS_NORECOMPUTE
STATISTICS_NORECOMPUTE = { ON | OFF}
Specifies whether distribution statistics are recomputed. The default is OFF.
ON
Out-of-date statistics are not automatically recomputed.
OFF
Automatic statistics updating are enabled.
To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.
Important
Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.
IGNORE_DUP_KEY
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.
ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.
OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.
IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
ALLOW_ROW_LOCKS
ALLOW_ROW_LOCKS = { ON | OFF }
Applies to: SQL Server (SQL Server 2008 and later) and Azure SQL Database
Specifies whether row locks are allowed. The default is ON.
ON
Row locks are allowed when accessing the index. The Database Engine determines when row locks are used.
OFF
Row locks are not used.
ALLOW_PAGE_LOCKS
ALLOW_PAGE_LOCKS = { ON | OFF }
Applies to: SQL Server (SQL Server 2008 and later) and Azure SQL Database
Specifies whether page locks are allowed. The default is ON.
ON
Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.
OFF
Page locks are not used.
Comments