Description of unique mssql indexes and constraints

The unique index is essentially no different from other indexes. The only difference is that the unique index does not allow the same value in the index key. Because each entry in the index corresponds to the row in the table.

The unique index is essentially no different from other indexes. The only difference is that the unique index does not allow the same value in the index key. Because each entry in the index corresponds to the row in the table.

Unique Indexes and constraints
If the unique index does not allow duplicate values to be inserted into the index, the corresponding row cannot be inserted into the table where the index is located. This is why the unique index can implement primary keys and candidate keys.

When a primary key or unique constraint is declared for a table, SQL Server automatically creates a unique index corresponding to it. You can create a unique index without a unique constraint, but not vice versa. When defining a constraint, SQL Server automatically creates an index with the same name as the constraint, and you cannot delete the index before deleting the constraint. However, you can delete the constraint. deleting the constraint also deletes the associated index.

Each table can contain multiple unique indexes. For example, the Product table of AdventureWorks contains four Unique Indexes: ProductID, ProductNumber, rowguid, and ProductNameColumn. the person who sets the Product Table uses ProductID as the primary key, and the other three as the candidate keys.

You can use the Create INDEX statement to Create a unique INDEX, for example:

The code is as follows:
Create unique nonclustered index [AK_Product_Name] ON Production. Product ( [Name]);

You can also create a unique index by directly defining the constraints:

The code is as follows:
Alter table Production. Product add constraint PK_Product_ProductID PRIMARY KEY CLUSTERED (ProductID );

In the first method, your Prodcut table cannot contain the same ProductName. in the second case, the same ProductID is not allowed in the table.

Because defining a primary key or defining constraints will lead to index creation, you must provide necessary index information when defining the constraints. Therefore, the alter table statement above contains the "CLUSTERED" keyword.

If the column with the unique index or constraint already contains duplicate values in the current table, the index creation will fail.

When a unique index is created successfully, all DML statements that violate this constraint will fail. for example, if we want to add a ProductName statement that exists in the current table, the statement is as follows:

The code is as follows:

INSERT Production. Product (Name, ProductNumber, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, [Weight], DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate ) VALUES ('Full-Finger Gloves, m ', 'A unique product number ', 'Black ', 4, 3, 20.00, 40.00, 'M ', NULL, NULL, NULL, 0, 'M ', NULL, 'U ', 20, 3, GETDATE (), GETDATE (), NULL );

Code 1. the inserted row and table have the same ProductName

After the above code is executed, we can see the following error message:

Message 2601, level 14, status 1, and 1st rows cannot be in an object with a unique index 'AK _ Product_Name'
Insert duplicate key rows in 'production. product. The statement has been terminated.

The above message tells us that the AK_Product_Name index does not allow us to insert data that contains the same ProductName as the current table.

Primary key, unique constraint and no constraint
The primary key constraints and unique constraints have the following small differences.

• The primary key constraint does not allow NULL values. The index key of any index cannot contain null values. However, the unique constraint can contain NULL values, but the unique constraint treats two NULL values as repeated values. Therefore, each column with a unique constraint can only contain one NULL value.
• A clustered index is automatically created when a primary key is created, unless the current table already contains a clustered index or the NONCLUSTERED keyword is specified when a primary key is created.
• A non-CLUSTERED index is automatically created when a unique constraint is created, unless you specify the CLUSTERED keyword and the current table does not have a CLUSTERED index.
• Each table can have only one primary key, but it can be subject to multiple unique constraints.

For the selection of unique constraints and Unique Indexes, refer to the instructions on MSDN, as follows:

The unique constraint is not significantly different from the unique index. There is no difference between creating an independent unique index and using unique constraints for data verification. The query optimizer does not distinguish whether a unique index is created by constraints or manually. However, if we aim to ensure data integrity, we recommend that you create constraints so that the target of the corresponding index is clear at a glance.

Hybrid unique index and filter index
As mentioned above, only one NULL value is allowed for a unique index, but this is in conflict with common business requirements. In many cases, duplicate values are not allowed for existing values, but multiple columns without values are allowed.

For example, you are a supplier and all your products come from third-party vendors. You store all your product information in a table named ProductDemo. You have your own ProductID and also track the UPC (Universal Product Code) value of the Product. However, not all manufacturers' products exist in UPC. some data in your table is as follows.

ProductID UPCode Other Columns
Unique primary key index

14AJ-W 036000291452

23CZ-M
23CZ-L
18MM-J 044000865867

Table 1. part of the ProductDemo table

In the second column of the preceding table, you must ensure the uniqueness of UPCode and allow NULL values. The best way to achieve this is to mix unique indexes and filter indexes (the index is actually introduced in SQL Server 2008 ).

As a demonstration, we created the table shown in Table 1.

The code is as follows:
Create table ProductDemo ( ProductID NCHAR (6) NOT NULL Primary key, UPCode NCHAR (12) NULL );

Next we insert the data shown above.

The code is as follows:
INSERT ProductDemo ( ProductID, UPCode) VALUES ('14aj-W', '123 ') , ('23cz-M', NULL) , ('23cz-L', NULL) , ('1970-J', '123 ');

When we insert duplicate values

The code is as follows:
INSERT ProductDemo (ProductID, UPCode) VALUES ('14aj-K', '123 ');

The following error is returned.

Message 2601, level 14, status 1, and 1st rows cannot insert duplicate keys in the 'dbo. productdemo' object with the unique index 'xx.
The statement has been terminated.

(Note: The author of the original text here should be negligent. he is slightly confused because he has not created a unique index for filtering, so no error will be reported according to the original text. I have added the code here: create unique nonclustered index xx on ProductDemo (UPCode) where UPCode! = Null)

Select the appropriate IGNORE_DUP_KEY option

When creating a unique index, you can specify the IGNORE_DUP_KEY option. Therefore, the options for creating a unique index in this article can be:

The code is as follows:
Create unique nonclustered index AK_Product_Name ON Production. Product ([Name] ) WITH (IGNORE_DUP_KEY = OFF );

The name IGNORE_DUP_KEY is easy to misunderstand. Duplicate values are never ignored when a unique index exists. More accurately, duplicate keys are never allowed in a unique index. This option is only useful when multiple columns are inserted.

For example, you have two tables, table A and Table B, with the same structure. You may submit the following statement to SQL Server.

The code is as follows:
Insert into TableA SELECT * FROM TableB;

SQL Server will try to insert data from all tables B into Table. However, what if the unique index rejects the insertion of data in Table B that is the same as that in table? Do you want to INSERT duplicate data successfully or the entire INSERT statement fails?

This depends on the IGNORE_DUP_KEY parameter you set. when creating a unique index, you can set a parameter to set what to do if insertion fails. The two parameters for setting IGNORE_DUP_KEY are described as follows:

IGNORE_DUP_KEY = OFF

The entire INSERT statement will not succeed and an error prompt is displayed, which is also the default setting.

IGNORE_DUP_KEY = OFF

Only rows with duplicate keys fail, and all other rows succeed. A warning message is displayed.

The IGNORE_DUP_KEY option only affects the insert statement. Instead of being affected by UPDATE, create index, and alter index. This option can also be set when the primary key and unique constraints are set.

Why does a unique index provide additional performance improvement?
The unique index can provide unexpected performance improvements. This is because the unique index provides SQL Server with information to ensure that a column has no duplicate values. The two unique indexes ProductID and ProductName in the Product Table of adventureWork provide a good example.

Join, a buddy of your company's data warehouse wants you to provide him with some information about the Product table, as follows:

• Product name
• Quantity of products sold
• Total Sales
Therefore, you have written the following query statement:

The code is as follows:
SELECT [Name], COUNT (*) 'Rowcount', SUM (LineTotal) 'Totalvalue' FROM Production. Product P JOIN Sales. SalesOrderDetail D ON D. ProductID = P. ProductID group by p. Name

(Note: the original author has a problem with the code. replace ProductID with P. Name)

The Data Warehouse's friends are very satisfied with your query statement. each row contains the product name, sales quantity, and total sales. The results are as follows:

However, you are worried about the cost of this query. SalesOrderDetail is a large table in the two tables in the preceding query and is also grouped by ProductName. this ProductName is from the Product table rather than the SalesOrderDetail table.

Through SQL Server Management Studio, you notice that the SalesOrderDetail table has a primary key, and the primary key is also the clustered index key, that is, SalesOrderID and SalesOrderDetailID. this primary key does not improve the performance of ProductName groups.

If you run the code of Article 5 containing columns, you have created the following non-clustered indexes.

The code is as follows:
Create nonclustered index FK_ProductID_ModifiedDate ON Sales. SalesOrderDetail (ProductID, ModifiedDate) INCLUDE (OrderQty, UnitPrice, LineTotal );

You think this index can be helpful for your query because it contains all the information required for queries except the ProductName column. This index is sorted by ProductID, but you are still worried that the grouped ProductID comes from one table, and the Select information comes from another table.

You can view the execution plan through SQL Server Management Studio, and see the execution plan 1 to be queried by the data warehouse.

. Execution plan for grouping by Product. Name

First, you can be surprised by the Product name index of the Product table, Product. AK_Product_Name is not used. then you realize that in the Product. name column and Product. the ProductID has a unique index, which makes SQL Server know that these two columns are unique. Therefore, Group By Name is equivalent to Group By ProductID. This makes a product a group.

Therefore, the query optimizer realizes that your query is equivalent to the following query. The two ProductID indexes support Join and group operations on the query.

The code is as follows:
SELECT [Name], COUNT (*) 'Rowcount', SUM (LineTotal) 'Totalvalue' FROM Production. Product P JOIN Sales. SalesOrderDetail D ON D. ProductID = P. ProductID group by ProductID

SQL Server simultaneously scans overwrite and clustered indexes on SalesOrderDetail, both of which are sorted by ProductID. Therefore, the merge join function removes the need for sorting or hashing. In short, SQL Server generates the most effective query plan.

If you Drop the Product. AK_Product_Name index, for example:

The code is as follows:
If exists (SELECT * FROM sys. indexes WHERE OBJECT_ID = OBJECT_ID (N 'production. product') AND name = n' AK _ Product_Name ') DROP INDEX AK_Product_Name ON Production. Product;

The generated new execution plan is less effective and requires additional sorting and merge join operations.

. When the index is dropped, the execution plan for grouping queries by Product Name

As you can see, although the primary function of the unique index is to ensure data integrity, it can also help the query optimizer to generate a better query plan, even if the index itself is not used to access data.

Summary
Unique Indexes provide constraints for primary keys and candidate keys. A unique index can exist without a unique constraint, but not vice versa.

A unique index can also be a filter index, which allows a unique index to have multiple NULL values in a column.

The IGNORE_DUP_KEY keyword can affect batch insert statements.

The unique index can also provide better performance. since the unique index itself is not used for data access