Unique Indexes and Constraints in SQL Server

Overview

• Lecture Series: SQL Server (Part 37)
• Topics: Unique Indexes, Unique Constraints

Key Concepts

Unique Index

• Used to enforce the uniqueness of key values within an index.
• Created automatically for primary key columns unless another clustered index exists.
• Can be both clustered and non-clustered.
• Example: Creating a unique clustered index for the primary key column.

Primary Key Constraint

• Automatically creates a unique clustered index for the specified column.
• Example: ID column marked as a primary key.
• Use system stored procedure sp_helpindex 'TableName' to check available indexes.
• Object Explorer can also be used to verify index properties.
• Attempts to explicitly drop an index will fail if the index is used for enforcing a primary key constraint.

Practical Examples

• Creating a Table with Primary Key

  CREATE TABLE TBL_Employee (
      ID INT PRIMARY KEY,
      FirstName NVARCHAR(50),
      LastName NVARCHAR(50)
  );
  

• Checking indexes:

  EXEC sp_helpindex 'TBL_Employee';
  

• Deleting index via Object Explorer removes both index and associated key.
  • Duplicates will be allowed if primary key constraint is removed.

Non-Clustered Unique Index

• Can enforce uniqueness across multiple columns
• Example: Ensuring no duplicate first and last names

  CREATE UNIQUE NONCLUSTERED INDEX uix_TBL_Employee_FirstLastName
  ON TBL_Employee (FirstName, LastName);
  

Unique Constraint vs Unique Index

• Unique Constraint

  • Enforces uniqueness across columns.
  • Automatically creates a unique index when added.
  • Example: ensure unique cities within a table using a unique constraint.

  ALTER TABLE TBL_Employee ADD CONSTRAINT uq_TBL_Employee_City UNIQUE (City);
  

  • By default creates a non-clustered unique index, but can be changed to clustered.

• Unique Index

  • Explicit creation via CREATE INDEX statement.
  • Useful for reinforcing database integrity.
  • Example: Directly creating a unique index

  CREATE UNIQUE INDEX idx_UniqueCity
  ON TBL_Employee (City);
  

When to Use Unique Constraint vs Unique Index

• Use unique constraint when enforcing data integrity as the primary objective.
• Both methods result in the creation of a unique index.
• Query optimizer treats both equally.

Tips and Points

• Default behaviors:
  • Primary key constraint creates a unique clustered index.
  • Unique constraint creates a unique non-clustered index, if not specified otherwise.
• Cannot create unique index or constraint on columns with existing duplicate values. Must remove duplicates first or modify columns.
• Unique indexes and constraints support integrity and performance of database queries.

Additional Resources

• ASP.NET, C# and SQL Server Interview Questions.

Conclusion

• Clear understanding of how unique indexes and constraints work helps in efficient database design and management.
• Useful in maintaining the integrity and performance of SQL Server databases.