Create a unique non-clustered index typical implementation
Unique indexes can be implemented in the following ways:
PRIMARY KEY or UNIQUE constraint
When a PRIMARY KEY constraint is created, a unique clustered index is automatically created for one or more columns if no clustered index exists for the table and no unique non-clustered index is specified. Null values are not allowed for primary key columns.
When a UNIQUE constraint is created, a UNIQUE non-clustered index is created by default to enforce the UNIQUE constraint. If there is no clustered index for the table, you can specify a unique clustered index.
For more information, see Unique Constraints and Check Constraints and Primary and Foreign Key Constraints.
Constraint-independent indexes
Multiple unique non-clustered indexes can be defined for a table.
See CREATE INDEX (Transact-SQL) for more information.
The index view
To create indexed views, define a unique clustered index for one or more view columns. The view executes, and the result set is stored at the page level of the index in the same way that table data is stored in a clustered index. For more information, see Creating indexed Views.
Create unique non-clustered index restrictions and limitations
You cannot create a UNIQUE index, UNIQUE constraint, or PRIMARY KEY constraint if duplicate KEY values exist in the data.
A unique non-clustered index can include inclusive non-key columns. For more information, see Create Indexes with Included Columns.
Create unique non-clustered indexes using SSMS database management tools
Create a unique non-clustered index using table designer
1, connect database, select database, select data table – “right click -” select Design.
2, In the table Designer window – select the attribute column to which you want to add index – – right-click – – select index/key.
3, in the index/key popup box – “click Add, Add index -” in the general window type select index – “click column to select data column.
4, in the index column pop-up box – “click add index column -” click select index column sorting rule – “can build an index on multiple data columns -” click OK.
5, in the index/key pop-up box – “Select create unique index -” enter index name – “Enter index description -” select create as non-clustered index – “other options can be selected by default -” click close.
6, click Save (or CTRL + S)- “Close table Designer -” “Refresh table -” to view the result.
Create unique indexes using object Explorer
1, connect to the database, select the database, select the data table – “expand object Explorer -” right click index – “select New index -” select non-clustered index.
2, in the new index pop-up box – “Enter index name -” select Create as a unique index – “click Add, select index data column.
3, in the data table column – “select the index to add data column, can select more than -” click OK.
4. In the New Index window – click Inclusion column – – click Add.
5, in the data table column – “click to select the inclusion column, you can select more than one -” click OK.
6, in the new index window – “click options -” can set their own index properties.
7, in the new index pop-up box – “Click Storage -” set index and data storage rules.
8, in the new index pop-up box – “Click filter -” enter the filter rule.
9, in the new index popup box – “Click the Extended attribute -” enter the extended attribute name – “Enter the extended attribute name -” can enter multiple extended attributes – “click OK.
10. View the creation result.
Create a unique non-clustered index using a T-SQL script
Grammar:
Declare database references
Use database name;
go
Check whether the index exists
If exists(select * from sysindexes where name= index name)
Drop index Index name on table name with (online=off);
go
Add index to index
create
–[unique] — Specifies whether the clustered index is unique
[clustered | nonclustered] – appointed as clustered index
Index Indicates the index name
On table name – The table to which the index is added
(column names [asc | desc], column names [asc | desc],…). Which data column the index is added to
You can extend the functionality of a non-clustered index by adding non-key columns to the leaf level of a non-clustered index. By including non-key columns, you can create non-clustered indexes that cover more queries. This is because non-key columns have the following advantages:
They can be data types that are not allowed as index key columns.
The database engine does not consider key columns or key sizes when calculating them.
Indexes with inclusive non-key columns can significantly improve query performance when all columns in a query are included in the index as key or non-key columns.
— This improves performance because the query optimizer can find all column values in the index; Do not access tables or aggregate index data, thereby reducing disk I/O operations.
Include (column name,…)
Where (filter expression) — Filter expression
with(
–pad_index: specifies index padding
–pad_index= ON :FILLFACTOR specifies the percentage of free space to be applied to the middle-level pages of the index.
–pad_index=off or fillfactor not specified: Considering the key set on the middle-level page, you can almost fill the middle-level page, but at least leave enough space for the largest index row.
pad_index={ on | off },
— StatisticS_norecompute: Specifies whether to recalculate statistics.
— STATISticS_norecompute = ON: Outdated statistics are not automatically recalculated.
— STATISticS_norecompute =off: Automatic statistics update is enabled.
statistics_norecompute={ on | off },
— sort_in_tempDB: Specifies whether the sort result is stored in tempDB.
— sort_in_tempDB = ON: Stores the intermediate sort results used to generate the index in tempdb. If the TEMPDB is not on the same set of disks as the user database, the index creation time can be reduced. However, this increases the amount of disk space used during index generation.
— sort_in_tempDB =off: Intermediate sort results are stored in the same database as indexes.
sort_in_tempdb={ on | off },
–ignore_dup_key: Specifies the response type when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations that occur after an index is created or regenerated. This option is invalid when CREATE INDEX, ALTER INDEX, or UPDATE is executed. The default value is OFF.
–ignore_dup_key= ON: on, warning message when inserting duplicate key values into unique indexes. Only actions that violate uniqueness fail.
–ignore_dup_key=off: closes, error message when inserting duplicate key values into unique indexes. Roll back the entire INSERT operation. IGNORE_DUP_KEY cannot be set to ON for indexes created ON views, non-unique indexes, XML indexes, spatial indexes, and filtered indexes
ignore_dup_key={ on | off },
–drop_existing: drop the index if it is still in the table and create a new one. The default value is OFF.
–drop_existing= ON: Specifies that to drop and regenerate an existing index, it must have the same name as parameter index_name.
–drop_existing=off: Specifies not to drop and rebuild an existing index. If the specified index name already exists, SQL Server will display an error.
drop_existing={ on | off },
–online: Specifies whether the underlying table and associated indexes are available for query and data modification operations during an index operation. The default value is OFF. The REBUILD operation can be ONLINE.
–online= ON: no long-term table locks are held during index operations. During the primary phase of an index operation, only intended shared (IS) locks are used on the source table.
This allows you to continue querying or updating the underlying tables and indexes.
The operation begins by holding a shared (S) lock on the source object for a short period of time.
At the end of the operation, if a non-clustered index is created, the S (shared) lock on the source is acquired for a short period of time;
A SCH-M (schema modification) lock is acquired for a short period of time when a clustered index is created or dropped online, and when a clustered or non-clustered index is rebuilt. But online index locks are short metadata locks, especially sch-M locks that must wait for all blocking transactions on the table to complete.
While waiting, the SCH-m lock blocks all other transactions waiting behind this lock while accessing the same table. When creating indexes for local temporary tables, ONLINE cannot be set to ON.
–online=off: Table locks are applied during index operations. This prevents all users from accessing the underlying table during the operation.
An offline index operation that creates, rebuilds, or drops a clustered index or rebuilds or drops a non-clustered index acquires a schema change (SCH-M) lock on the table.
This prevents all users from accessing the underlying table during the operation. An offline index operation that creates a non-clustered index acquires a shared (S) lock on the table. This prevents updates to the underlying table, but allows read operations (such as SELECT statements).
online={ on | off },
— ALooW_ROW_LOCKS: Specifies whether row locks are allowed.
— ALLOW_ROW_LOCKS = ON: allows row locks when accessing indexes. The database engine determines when to use row locks.
— allow_ROW_LOCKS =off: no row locks are used.
allow_row_locks={ on | off },
— allow_PAGe_LOCKS: Specifies whether page locks are allowed.
— allow_PAGe_LOCKS = ON: allows page locks when accessing indexes. The database engine determines when page locks are used.
— allow_PAGe_LOCKS =off: no page locks are used.
allow_page_locks={ on | off },
–fillfactor=n: Specifies a percentage indicating how much leaf level of each index page should be filled as the database engine creates or modifies the index. The specified value must be an integer between 1 and 100. The default value is 0.
fillfactor=n
— MAXDOP = MAX_degreE_OF_PARALLELISM: Replaces the Max degree of Parallelism configuration option during indexing operations. For more information, see Configuring Max Degree of Parallelism Server configuration options. Using MAXDOP limits the number of processors that can be used during the execution of a parallel plan. The maximum number of processors is 64.
— Max_degree_OF_parallelism can be:
–1 – Cancel build parallel plan.
–>1 — Limits the maximum number of processors used in a parallel indexing operation to the specified number.
–0 (default) – Use the actual number of processors or less depending on the current system workload.
For more information, see Configuring parallel Index Operations.
–maxdop=max_degree_of_parallelism,
— datA_compression = ROW: Specifies data compression options for the specified table, partition number, or partition range. The options are as follows:
–none
— Does not compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–row
Use row compression to compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–page
Use page compression to compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–columnstore
— Applicable to SQL Server 2014 (12.x) to SQL Server 2017.
— Applies only to column storage tables. COLUMNSTORE Specifies that the COLUMNSTORE_ARCHIVE option is used to decompress the partition. When the data is restored, the COLUMNSTORE index continues to be compressed using column storage compression for all column storage tables.
–columnstore_archive
— Applicable to SQL Server 2014 (12.x) to SQL Server 2017.
Only for column store tables, which are stored using clustered column store indexes. COLUMNSTORE_ARCHIVE further compresses the specified partition to a smaller size. This can be used for archiving or other situations where less storage is required and more time can be spent on storage and retrieval
–data_compression={ none | row | page | columnstore | columnstore_archive }
– on partitions ({< partition_number_expression > |},… [n]) scope of application: SQL Server 2008 and SQL Server 2017.
— Specifies the partition to which the DATA_COMPRESSION setting will be applied. If the table is not partitioned, the ON PARTITIONS parameter generates an error. If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option is applied to all PARTITIONS of the partitioned table.
Partition_number_expression > can be specified as follows:
Provide a partition number, such as ON PARTITIONS (2).
Provide partition numbers for individual PARTITIONS separated by commas, e.g., ON PARTITIONS (1, 5).
Provide both scope and a single partition, e.g., ON PARTITIONS (2, 4, 6 TO 8).
You can specify PARTITIONS separated by the word TO, for example, ON PARTITIONS (6 TO 8).
— specify the DATA_COMPRESSION option multiple times
–on partitions(1-2)
) on [primary]; — Data space specification
go
Add a comment
Execute sp_addextendedProperty N’MS_Description’,N’ index description ‘,N’schema’,N’ dbO ‘,N’table’,N’ table name ‘,N’index’,N’ index name ‘;
go
Example:
Declare the database application
use testss;
go
— Determines whether an index exists
if exists(select * from sysindexes where name=’uniquenonclus1′)
drop index uniquenonclus1 on test1 with(online=off);
go
create
The unique, the only
Nonclustered — Nonclustered index
Index – Index key
Uniquenonclus1 — Index name
On test1 — Which table is indexed on
(name ASC) – Which data column the index is built on
You can extend the functionality of a non-clustered index by adding non-key columns to the leaf level of a non-clustered index. By including non-key columns, you can create non-clustered indexes that cover more queries. This is because non-key columns have the following advantages:
They can be data types that are not allowed as index key columns.
The database engine does not consider key columns or key sizes when calculating them.
Indexes with inclusive non-key columns can significantly improve query performance when all columns in a query are included in the index as key or non-key columns.
— This improves performance because the query optimizer can find all column values in the index; Do not access tables or aggregate index data, thereby reducing disk I/O operations.
include(sex)
Where (name is null) — Filter expression
with(
–pad_index: specifies index padding
–pad_index= ON :FILLFACTOR specifies the percentage of free space to be applied to the middle-level pages of the index.
–pad_index=off or fillfactor not specified: Considering the key set on the middle-level page, you can almost fill the middle-level page, but at least leave enough space for the largest index row.
pad_index=on,
— StatisticS_norecompute: Specifies whether to recalculate statistics.
— STATISticS_norecompute = ON: Outdated statistics are not automatically recalculated.
— STATISticS_norecompute =off: Automatic statistics update is enabled. statistics_norecompute=on,
— sort_in_tempDB: Specifies whether the sort result is stored in tempDB.
— sort_in_tempDB = ON: Stores the intermediate sort results used to generate the index in tempdb. If the TEMPDB is not on the same set of disks as the user database, the index creation time can be reduced. However, this increases the amount of disk space used during index generation.
— sort_in_tempDB =off: Intermediate sort results are stored in the same database as indexes.
sort_in_tempdb=off,
–ignore_dup_key: Specifies the response type when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations that occur after an index is created or regenerated. This option is invalid when CREATE INDEX, ALTER INDEX, or UPDATE is executed. The default value is OFF.
–ignore_dup_key= ON: on, warning message when inserting duplicate key values into unique indexes. Only actions that violate uniqueness fail.
–ignore_dup_key=off: closes, error message when inserting duplicate key values into unique indexes. Roll back the entire INSERT operation. IGNORE_DUP_KEY cannot be set to ON for indexes created ON views, non-unique indexes, XML indexes, spatial indexes, and filtered indexes
ignore_dup_key=off,
–drop_existing: drop the index if it is still in the table and create a new one. The default value is OFF.
–drop_existing= ON: Specifies that to drop and regenerate an existing index, it must have the same name as parameter index_name.
–drop_existing=off: Specifies not to drop and rebuild an existing index. If the specified index name already exists, SQL Server will display an error.
drop_existing=off,
–online: Specifies whether the underlying table and associated indexes are available for query and data modification operations during an index operation. The default value is OFF. The REBUILD operation can be ONLINE.
–online= ON: no long-term table locks are held during index operations. During the primary phase of an index operation, only intended shared (IS) locks are used on the source table.
This allows you to continue querying or updating the underlying tables and indexes.
The operation begins by holding a shared (S) lock on the source object for a short period of time.
At the end of the operation, if a non-clustered index is created, the S (shared) lock on the source is acquired for a short period of time;
A SCH-M (schema modification) lock is acquired for a short period of time when a clustered index is created or dropped online, and when a clustered or non-clustered index is rebuilt. But online index locks are short metadata locks, especially sch-M locks that must wait for all blocking transactions on the table to complete.
While waiting, the SCH-m lock blocks all other transactions waiting behind this lock while accessing the same table. When creating indexes for local temporary tables, ONLINE cannot be set to ON.
–online=off: Table locks are applied during index operations. This prevents all users from accessing the underlying table during the operation.
An offline index operation that creates, rebuilds, or drops a clustered index or rebuilds or drops a non-clustered index acquires a schema change (SCH-M) lock on the table.
This prevents all users from accessing the underlying table during the operation. An offline index operation that creates a non-clustered index acquires a shared (S) lock on the table. This prevents updates to the underlying table, but allows read operations (such as SELECT statements).
online=off,
— allow_ROW_LOCKS: Specifies whether row locks are allowed.
— ALLOW_ROW_LOCKS = ON: allows row locks when accessing indexes. The database engine determines when to use row locks.
— allow_ROW_LOCKS =off: no row locks are used.
allow_row_locks=on,
— allow_PAGe_LOCKS: Specifies whether page locks are allowed.
— allow_PAGe_LOCKS = ON: allows page locks when accessing indexes. The database engine determines when page locks are used.
— allow_PAGe_LOCKS =off: no page locks are used.
allow_page_locks=on,
–fillfactor=n: Specifies a percentage indicating how much leaf level of each index page should be filled as the database engine creates or modifies the index. The specified value must be an integer between 1 and 100. The default value is 0.
fillfactor=1,
— MAXDOP = MAX_degreE_OF_PARALLELISM: Replaces the Max degree of Parallelism configuration option during indexing operations. For more information, see Configuring Max Degree of Parallelism Server configuration options. Using MAXDOP limits the number of processors that can be used during the execution of a parallel plan. The maximum number of processors is 64.
— Max_degree_OF_parallelism can be:
–1 – Cancel build parallel plan.
–>1 — Limits the maximum number of processors used in a parallel indexing operation to the specified number.
–0 (default) – Use the actual number of processors or less depending on the current system workload.
For more information, see Configuring parallel Index Operations.
maxdop=1
— datA_compression = ROW: Specifies data compression options for the specified table, partition number, or partition range. The options are as follows:
–none
— Does not compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–row
Use row compression to compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–page
Use page compression to compress tables or specified partitions. Applies only to row storage tables; Does not apply to column store tables.
–columnstore
— Applicable to SQL Server 2014 (12.x) to SQL Server 2017.
— Applies only to column storage tables. COLUMNSTORE Specifies that the COLUMNSTORE_ARCHIVE option is used to decompress the partition. When the data is restored, the COLUMNSTORE index continues to be compressed using column storage compression for all column storage tables.
–columnstore_archive
— Applicable to SQL Server 2014 (12.x) to SQL Server 2017.
Only for column store tables, which are stored using clustered column store indexes. COLUMNSTORE_ARCHIVE further compresses the specified partition to a smaller size. This can be used for archiving or other situations where less storage is required and more time can be spent on storage and retrieval
–data_compression=none
– on partitions ({< partition_number_expression > |},… [n]) scope of application: SQL Server 2008 and SQL Server 2017.
— Specifies the partition to which the DATA_COMPRESSION setting will be applied. If the table is not partitioned, the ON PARTITIONS parameter generates an error. If the ON PARTITIONS clause is not provided, the DATA_COMPRESSION option is applied to all PARTITIONS of the partitioned table.
Partition_number_expression > can be specified as follows:
Provide a partition number, such as ON PARTITIONS (2).
Provide partition numbers for individual PARTITIONS separated by commas, e.g., ON PARTITIONS (1, 5).
Provide both scope and a single partition, e.g., ON PARTITIONS (2, 4, 6 TO 8).
You can specify PARTITIONS separated by the word TO, for example, ON PARTITIONS (6 TO 8).
— specify the DATA_COMPRESSION option multiple times
–on partitions(1-2)
)
on [primary];
go
Add unique clustered index description
execute sp_addextendedproperty N’MS_Description’,N’ first unique clustered index’,N’ schema’,N’dbo’,N’table’,N’test1′,N’index’,N’uniquenonclus1′;
go
Advantages and disadvantages of creating a unique non-clustered index
Advantages:
1. You can create multiple unique non-clustered indexes for the same table as long as the data in each column is unique.
Unique indexes provide additional information that helps the query optimizer generate more efficient execution plans.
3. Unique indexes ensure the data integrity of defined columns.
4. Multi-column unique indexes ensure that each combination of values in the index key is unique.
5. It can speed up joins between tables, especially in achieving referential integrity of data.
6. When using grouping and sorting clauses for data retrieval, it can also significantly reduce the grouping and sorting time in queries.
Disadvantages:
1. Creating and maintaining indexes takes time, which increases with the volume of data.
2, the index needs to occupy physical space, in addition to the data table occupies data space, each index also occupies a certain physical space.
3. When the data in the table is added, deleted or modified, the index must be maintained dynamically, which reduces the maintenance speed of the data.