We already know how to create a primary key on a table. Here is an example:
USE Exercise; GO CREATE TABLE Students ( StudentID int PRIMARY KEY, FirstName nvarchar(50) NOT NULL, LastName nvarchar(50)); GO
When you create a primary key, the database engine automatically creates an index on the table and chooses the primary key column as its key. You have the option of indicating the type of index you want created. To do this, on the right side of the name of the column, enter CLUSTERED or NONCLUSTERED. If you don't specify the type of index, the CLUSTERED option is applied.
In our introduction, we saw that an index can make it possible to take some action during data entry, such as making sure that a column have unique values for each record or making sure that the combination of values of a group of columns on the same record produces a unique value. Besides this characteristic of indexes, they are actually very valuable when it comes to data analysis.
As mentioned for a book, the primary goal of an index is to make it easy to locate the records of a table or view.
An index is made valuable in two ways. On one hand, the records should be sorted. A clustered index itself takes care of this aspect because it automatically and internally sorts its records. What if the records are not unique? For example, in a bad data entry on a list of employees, you may have two or more employees with the same employee's records. If you create an index for such a table, the database engine would create duplicate records on the index. This is usually not good because when it comes time to select records, you may have too many records and take a wrong action.
When creating a table, you can create index for it and let the index apply a rule that states that each record would be unique. To take care of this, you can apply a uniqueness rule on the index.
If you are visually creating an index, in the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to Yes. On the other hand, if you want to remove this rule, set the Is Unique field to No.
To create a uniqueness index in SQL, apply the UNIQUE keyword in the formula:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON Table/View(Column(s))
Start with the CREATE UNIQUE expression, then specify whether it would be clustered or not. The rest follows the descriptions we saw previously. Here is an example:
-- ============================================= -- Database: Exercise -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'Exercise' ) DROP DATABASE Exercise GO CREATE DATABASE Exercise GO USE Exercise; GO -- ============================================= -- Database: Exercise -- Table; Employees -- ============================================= CREATE TABLE Employees ( EmployeeNumber int NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20), Username nchar(8) NOT NULL, DateHired date NULL, HourlySalary money ); GO CREATE UNIQUE CLUSTERED INDEX IX_Employees ON Employees(EmployeeNumber); GO
Once you have specified the uniqueness of an index on a table, during data entry, if the user enters a value that exists in the table already, an error would be produced. Here is an example:
USE Exercise; GO INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary) VALUES(92935, N'Joan', N'Hamilton', 22.50) GO INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary) VALUES(22940, N'Peter', N'Malley', 14.25) GO INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary) VALUES(27495, N'Christine', N'Fink', 32.05) GO INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary) VALUES(22940, N'Gertrude', N'Monay', 15.55) GO INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary) VALUES(20285, N'Helene', N'Mukoko', 26.65) GO
This would produce:
(1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.Employees' with unique index 'IX_Employees'. The statement has been terminated. (1 row(s) affected)