Microsoft SQL Server - Lesson 30: Indexes

	Indexes

Indexes Fundamentals

Introduction

An index is a mechanism that makes it easy to locate records and take a specific action based on some rule reinforced on that (those) column(s).

The database engine is already equipped with a default mechanism to automatically make up an index on a table depending on how the table is created. For example, if you create a primary key on a table, the database engine automatically creates an index for the column(s) used as the primary key.

Practical Learning: Introducing Indexes

Launch Microsoft SQL Server and connect
On the Standard toolbar, click the New Query button

To create a new database and a table, type the following:

CREATE DATABASE InformationTechnologyJournal1;
GO
USE InformationTechnologyJournal1;
GO

CREATE SCHEMA Publishing;
GO
CREATE SCHEMA Authorship;
GO
CREATE TABLE Authorship.Reviewers
(
	ReviewerNumber nchar(6) not null,
	FirstName nvarchar(24),
	MiddleName nvarchar(24),
	LastName nvarchar(24),
	Citizenship nvarchar(40)
);
GO

To execute, press F5

Visually Creating an Index

As mentioned alreeady, if you create a primary key on a table, or if you add a primary key to an existing table, the database engine would add an index to that table. The index would be based on the column(s) used in the primary key. Otherwise, you can still explicitly create an index. You can create an index visually or using SQL code.

To visually create an index, you can use the Indexes/Keys dialog box. To display it:

The table that has the column(s) that will be used for the index must be closed. In the Object Explorer, expand the database. Expand the Tables node. Right-click Indexes, position the mouse on New Index, and click the desired option (we will see what those options are):

Visually Creating an Index

In the Object Explorer, expand the database that holds the table or view and expand the Tables node. Right-click the table for which you want to create the index and click Design. In the window, right-click Indexes/Keys...

This would open the Indexes/Keys dialog box:

Indexes/Keys

To create an index, click the Add button. The first piece of information you should provide is the name. Normally, the database engine provides a default name. If you want to change it, click (Name) and type the desired name. The other very important piece of information you should provide is at least one column. By default, the database engine selects the first column of the table. If this is not the column you want to use, click Columns and click its ellipsis button . This would open the Index dialog box:

Index Columns

From the boxes under Column Name, you can select each column. Once you are ready, click OK.

Practical Learning: Visually Creating an Index

Many techniques used in our lessons are meant to illustrate various concepts available in Microsoft SQL Server. Some things would be done differently in a commercial or productive environments. This lesson is typical.

Many approaches used in this lesson to create indexes are for illustration purposes. This is not a science.

In the Object Explorer, expand the Databases node and expand InformationTechnologyJournal1 (if InformationTechnologyJournal1 does not appear, refresh the Databases node)
Expand the Tables node
Right-click Authorship.Reviewers and click Design
Right-click anywhere in the white area and click Indexes/Keys...
Click Add.
Notice that a default name has been provided and the first columns selected
To create another index, click Add
Click Columns and click its ellipsis button
Click the arrow of the combo box under Column Name and select LastName
Click the box under LastName
Click the arrow of its combo box and select FirstName
Click OK
In the right list, click (Name) and change its string to IX_ReviewersNames
Click Close
Close the table
When asked whether you want to save, click Yes

Creating an Index Using SQL

To create an index in SQL, the basic formula to follow is:

CREATE INDEX IndexName ON Table/View(Column(s))

Alternatively, open a Query Editor. Then, in the Templates Explorer, expand the Index node. Drag Create Index Basis (or another sub-node) and drop it in the window. Skeleton code would be generated for you:

-- =============================================
-- Create index basic template
-- =============================================
USE <database_name, sysname, AdventureWorks>
GO

CREATE INDEX <index_name, sysname, ind_test>
ON <schema_name, sysname, Person>.<table_name, sysname, Address> 
(
	<column_name1, sysname, PostalCode>
)
GO

The creation of an index starts with the CREATE INDEX expression, followed by a name for the index, followed by the ON keyword. In the Table/View placeholder, enter the name of the table or view for which you want to create the index, followed by parentheses in which you enter at least one column. If the index will include more than one column, list them separated by commas. Here is an example:

CREATE INDEX IX_Employees
ON Employees(LastName, Username);
GO

Practical Learning: Creating an Index

Click inside the Query Editor and press Ctrl + A

To create an index, type the following:

USE InformationTechnologyJournal1;
GO

CREATE INDEX IX_ReviewersCitizenships
ON Authorship.Reviewers(Citizenship);
GO

To execute, press F5

Index Maintenance

Deleting an Index

In Microsoft SQL Server (and most database systems), an index is treated as an object. That is, an index can be checked or deleted at will. You can delete an index visually or programmatically.

To visually delete an index, open its table in Design view. Right-click somewhere in the table window and click Indexes/Views. In the left frame, click the name of the index to select it, and click the Delete button. You will not receive a warning. Then click Close. If you want to change your mind and keep the index, don't save the table.

The basic syntax to delete an index in Transact-SQL is:

DROP INDEX IndexName ON TableName;

In this formula, replace the TableName with the name of the table that contains the index. Replace the IndexName with the name of the index you want to get rid of.

Practical Learning: Deleting an Indexes

Click inside the Query Editor and press Ctrl + A

To delete the indexes and their table, type the following:

DROP INDEX IX_Reviewers ON Authorship.Reviewers;
GO
DROP INDEX IX_ReviewersCitizenships
ON Authorship.Reviewers;
GO
DROP INDEX IX_ReviewersNames ON Authorship.Reviewers;
GO
DROP TABLE Authorship.Reviewers;
GO

To execute, press F5

Checking the Existence of an Index

Before performing an operation on an index, you may want to check first that it exists. For example, if you try creating an index and giving it a name that exists already, you would receive an error. The following code:

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 INDEX IX_Employees
ON Employees(LastName, Username);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO

would produce:

Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics 
with name 'IX_Employees' already exists on table 'Employees'.

To visually check the existence of an index, open the table in design view, right-click the middle of the window and click Indexes/Keys. The list of indexes should appear on the left side. Here is an example:

To delete an index, click its name in the left list and click the Delete button.

Modifying an Index

If a certain detail of an index is not right, you can modify the index as opposed to deleting and re-creating it. Modifying an index is referred to as altering it. You can do this visually of with code.

To visually modify an index:

In the Object Explorer, expand the database, expand its Tables node, and expand Indexes. Right-click the index and click Peoperties. The Index Properties would open and you can make the desired modifications
Open the table in Design view. Right-click somewhere in the table window and click Indexes/Views. In the left frame, click the name of the index to select it. On the right side, make the modifications you want. When finished, click Close

The basic syntax to modify an index in Transact-SQL is:

ALTER INDEX IndexName
ON TableName
Modifications;

In the Modifications section, do what you judge necessary.

The Types of Indexes

Introduction

Microsoft SQL Server supports various types of indexes. The two broadest categories are clustered and non-clustered. Remember that if you create a primary key, the database engine automatically creates an index based on the column(s) on that primary key and adds that index to the table.

Clustered Indexes

In our introduction, we saw that an index is primarily created using one or more columns from a designated table. This means that, when it comes to using the index, we would use the values stored in the column(s) that was (were) selected for the index. Such an index is referred to as clustered. The columns that were made part of an index are referred to as keys.

To visually create a clustered index:

In the Object Explorer, expand the database and expand its Tables node. Right-click Indexes, position the mouse on New Index, and click Clustered Index. This would open the New Index dialog box:

To specify the column(s) for the index, click the Add button. This would open a window titled Select Columns

To select a column, click its check box. Once you have made your selections, click OK. When you click OK, a message box will let you know that the index is going to be created:

If you still want to create the index, click OK. If you have changed your mind, click Cancel
Open the table in design and display its Indexes/Keys dialog box. In the dialog box, when creating a new indexed or after clicking the name of an existing index, in the right list, click Create As Clustered and select Yes:

Clustered Index

You can then add the column(s) you want. Once you are ready, click Close. To create a clustered index in SQL, use the following formula:

CREATE CLUSTERED INDEX IndexName ON Table/View(Column(s))

From the description we gave previously, the only new keyword here is CLUSTERED. Based on this, here is an example:

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

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE CLUSTERED INDEX IX_Employees
ON Employees(LastName);
GO

A table that contains a clustered index is called a clustered table. There are various aspects to a clustered index:

To make it easy to search the records, they (the records) are sorted. This makes it possible for the database engine to proceed in a top-down approach and quickly get to the desired record
Without this being a requirement, each record should be unique
There must be only one clustered index per table. This means that, if you (decide to) create a clustered index on a table, the table becomes equipped with one. If you create another clustered index, the previous one (clustered index) is deleted

Indexed Primary Keys

We already know how to create a primary key on a table. Here is an example:

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, you can enter CLUSTERED.

Practical Learning: Creating Indexed Primary Keys

Click inside the Query Editor and press Ctrl + A

To use many technique of creating clustered indexed primary keys, type the following code:

USE InformationTechnologyJournal1;
GO

CREATE TABLE Authorship.Reviewers
(
	ReviewerNumber nchar(6) PRIMARY KEY CLUSTERED not null,
	FirstName nvarchar(24),
	MiddleName nvarchar(24),
	LastName nvarchar(24),
	Citizenship nvarchar(40)
);
GO
CREATE TABLE Publishing.Affiliations
(
	AffiliationCode nchar(5) not null primary key CLUSTERED,
	AffiliationName nvarchar(60)
);
GO
CREATE TABLE Authorship.ReviewersAffiliations
(
	ReviewerNumber nchar(6) not null,
	AffiliationCode nchar(5) not null,
	Constraint PK_ReviewersAffiliations
		Primary Key clustered(ReviewerNumber, AffiliationCode),
	Constraint FK_ReviewersAffiliations Foreign Key(ReviewerNumber)
		References Authorship.Reviewers(ReviewerNumber),
	Constraint FK_OrganizationsAffiliated Foreign Key(AffiliationCode)
		References Publishing.Affiliations(AffiliationCode)
);
GO
CREATE TABLE Publishing.AreasOfInsterest		
(
	AreaOfInsterestID int identity(1001, 1) not null primary key clustered,
	AreaOfInterest nvarchar(50) not null,
	[Description] nvarchar(max)
);
GO
CREATE TABLE Authorship.ReviewersAreasOfInsterest
(
	ReviewerNumber nchar(6) not null,
	AreaOfInsterestID int not null,
	Constraint PK_ReviewersAreasOfInsterest Primary Key clustered(ReviewerNumber, AreaOfInsterestID),
	Constraint FK_ReviewersInsterests Foreign Key(ReviewerNumber)
		References Authorship.Reviewers(ReviewerNumber),
	Constraint FK_ReviewersAreasOfInsterest Foreign Key(AreaOfInsterestID)
		References Publishing.AreasOfInsterest(AreaOfInsterestID)
);
GO
CREATE TABLE Authorship.Authors
(
	AuthorNumber nchar(6) not null,
	FirstName nvarchar(24),
	MiddleName nvarchar(24),
	LastName nvarchar(24),
	Citizenship nvarchar(40),
	DateOfBirth date,
	DateOfDeath date,
	Constraint PK_Authors Primary Key CLUSTERED(AuthorNumber)
);
GO
CREATE TABLE Authorship.Manuscripts		
(
	ManuscriptCode nchar(9) not null,
	Title nvarchar(80) not null,
	Notes nvarchar(max),
	Constraint PK_Manuscripts Primary Key CLUSTERED(ManuscriptCode)
);
GO
CREATE TABLE Authorship.ManuscriptsStatus
(
	ManuscriptStatus nvarchar(20) not null,
	[Description] nvarchar(max),
	Constraint PK_ManuscriptsStatus Primary Key CLUSTERED(ManuscriptStatus)
);
GO
CREATE TABLE Authorship.ManuscriptsAuthors
(
	AuthorNumber nchar(6) not null Primary Key CLUSTERED(AuthorNumber, ManuscriptCode),
	ManuscriptCode nchar(9) not null,
	Constraint FK_AuthorsOfManuscripts Foreign Key(AuthorNumber)
		References Authorship.Authors(AuthorNumber),
	Constraint FK_ManuscriptsCodes Foreign Key(ManuscriptCode)
		References Authorship.Manuscripts(ManuscriptCode)
);
GO
CREATE TABLE Authorship.ManuscriptsRatings		
(
	ManuscriptRating nvarchar(20) not null,
	Explanation nvarchar(max),
	Constraint PK_ManuscriptsRatings Primary Key(ManuscriptRating)
);
GO
CREATE TABLE Authorship.ManuscriptsReviewUpdates
(
	ManuscriptReviewUpdateID int identity(1, 1) not null,
	DateUpdated date not null,
	ReviewerNumber nchar(6) not null,
	ManuscriptCode nchar(9) not null,
	ManuscriptRating nvarchar(20),
	Constraint FK_ManuscriptsReviewers Foreign Key(ReviewerNumber)
		References Authorship.Reviewers(ReviewerNumber),
	Constraint FK_ManuscriptsCodesUpdates Foreign Key(ManuscriptCode)
		References Authorship.Manuscripts(ManuscriptCode),
	Constraint FK_ManuscriptRatings Foreign Key(ManuscriptRating)
		References Authorship.ManuscriptsRatings(ManuscriptRating)
);
GO
CREATE TABLE Authorship.ManuscriptsStatusChanges
(
	ManuscriptStatusChangeID int identity(1, 1) not null,
	DateChanged date not null,
	ManuscriptCode nchar(9) not null,
	ManuscriptStatus nvarchar(20) not null,
	AssignedTo nchar(6),
	Notes nvarchar(max),
	Constraint PK_ManuscriptsStatusChange Primary Key(ManuscriptStatusChangeID),
	Constraint FK_CodesOfManuscriptsChanged Foreign Key(ManuscriptCode)
		References Authorship.Manuscripts(ManuscriptCode),
	Constraint FK_StatusOfManuscriptsChanged Foreign Key(ManuscriptStatus)
		References Authorship.ManuscriptsStatus(ManuscriptStatus),
	Constraint FK_ReviewersChangingStatus Foreign Key(AssignedTo)
		References Authorship.Reviewers(ReviewerNumber)
);
GO

To execute, press F5
In the Object Explorer, right-click InformationTechnologyJournal1 and click Refresh
Under InformationTechnologyJournal, right-click Tables and click Refresh, then expand Tables
Under the Tables node, expand Authorship.ManuscriptsReviewUpdates
Right-click Indexes, position the mouse on New Index, and click Clustered Index
Change the name to IX_ManuscriptsReviews
Click the Add button
Click the check boxes of ReviewerNumber and ManuscriptCode
Click OK

Non-Clustered Indexes

While a clustered index uses a sorted list of records of a table, another type of index can use a mechanism not based on the sorted records but on a bookmark. This is called a non-clustered index. As opposed to a clustered table that can contain only one clustered index, you can create not only one, but as many as 249 non-clustered indexes.

To visually create a non-clustered index:

In the Object Explorer, expand the database and expand its Tables node. Right-click Indexes, position the mouse on New Index, and click Non-Clustered Index. This also would open the New Index dialog box
Open the table in design and display its Indexes/Keys dialog box. To create a new index, click the Add button. If an index was always created or set as clustered and you want to change it, you can change its Create As Clustered property from Yes to No

To create a non-clustered index in SQL, use the following formula:

CREATE NONCLUSTERED INDEX IndexName ON Table/View(Column(s))

The new keyword in this formula is NONCLUSTERED. Everything is the same as previously described. Based on this, here is an example:

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

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE NONCLUSTERED INDEX IX_Employees
ON Employees(LastName, FirstName);
GO

If you create an index without specifying CLUSTERED or NONCLUSTERED, the database engine automatically makes it non-clustered.

When you create a primary key, we saw that you can make it clustered. In the same way, you can make it non clustered. To do that, 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.

Practical Learning: Creating Non-Clustered Indexes

Click in the top section of the Query Editor, press Ctrl + A to select the whole text

To create non-clustered indexed using Transact-SQL, type the following:

USE InformationTechnologyJournal1;
GO

CREATE NONCLUSTERED INDEX IX_ReviewersNames
ON Authorship.Reviewers(FirstName, LastName);
GO
CREATE NONCLUSTERED INDEX IX_ReviewersCitizenships
ON Authorship.Reviewers(Citizenship);
GO

Press F5 to execute

Indexes and Data Entry

Introduction

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 has 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, indexes are actually very valuable when it comes to data analysis.

Practical Learning: Entering Data

Click inside the Query Editor and press Ctrl + A

To create records, type the following:

USE InformationTechnologyJournal1;
GO

INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'297948', N'Eddy', N'Donald', N'Vasquez', N'USA');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, LastName, Citizenship)
VALUES(N'282947', N'Edmond', N'Plane', N'USA'),
      (N'294859', N'David', N'Kearney', N'Australia');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'284058', N'Robert', N'Peter', N'Shear', N'UK');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, LastName, Citizenship)
VALUES(N'206486', N'Anthony', N'Goodie', N'South Africa');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'280485', N'Elisabeth', N'Laureen', N'Kirchner', N'USA'),
      (N'248374', N'Gérard', N'Laurent', N'Benardin', N'France'),
	  (N'282504', N'Joseph', N'Emmett', N'Buyck', N'Holland');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, LastName, Citizenship)
VALUES(N'280486', N'Lucie', N'Kearns', N'USA');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'284905', N'Evelyne', N'Laurie', N'Lawson', N'USA'),
	  (N'240486', N'Frank', N'Jeffrey', N'Avery', N'Canada'),
	  (N'200468', N'Pamela', N'Amelia', N'Lotts', N'UK');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'240858', N'Cindy', N'Anne', N'Angel', N'Mexico'),
      (N'260850', N'Joan', N'Daniela', N'Kean', N'USA');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, LastName, Citizenship)
VALUES(N'204068', N'Andrew', N'Post', N'New Zealand');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'280680', N'Deborah', N'Christine', N'Chisohlm', N'Brasil'),
      (N'280684', N'Kyle', N'Robert', N'Greenwood', N'USA');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, LastName, Citizenship)
VALUES(N'249595', N'Larry', N'Metzer', N'Canada');
GO
INSERT INTO Authorship.Reviewers(ReviewerNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'249696', N'Maureen', N'Holly', N'Dorryan', N'USA');
GO

INSERT INTO Publishing.Affiliations(AffiliationCode, AffiliationName)
VALUES(N'MDUNV', N'Maryland University'),
      (N'MSCOL', N'Maryland State College'),
      (N'AIRAT', N'Australia Institute of Research and Technologies'),
      (N'INPNT', N'Institute or Paranormal Technologies'),
      (N'VASTU', N'Virginia State University'),
      (N'IFTEM', N'Institut Français des Technologies Modernes'),
      (N'ASSIS', N'Association of Information Systems'),
      (N'ITNFD', N'International Technocrate Foundation');
GO

INSERT INTO  Authorship.ReviewersAffiliations(ReviewerNumber, AffiliationCode)
VALUES(N'297948', N'INPNT'), (N'282947', N'AIRAT'), (N'294859', N'ASSIS'),
      (N'284058', N'MDUNV'), (N'206486', N'ITNFD'), (N'280485', N'VASTU'),
      (N'248374', N'IFTEM'), (N'282504', N'MSCOL'), (N'280486', N'INPNT'),
      (N'240858', N'VASTU'), (N'260850', N'ITNFD'), (N'204068', N'MDUNV'),
      (N'280680', N'ITNFD'), (N'280684', N'ASSIS'), (N'249595', N'MSCOL'),
      (N'249696', N'AIRAT');
GO

INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship, DateOfBirth)
VALUES(N'924935', N'Margareth', N'Anne', N'Gundlehach', N'USA', N'19520612');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship)
VALUES(N'180047', N'Edward', N'Pomerantz', N'Canada');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship, DateOfBirth, DateOfDeath)
VALUES(N'637444', N'Laura', N'Jeannine', N'Wayne', N'USA', N'19180805', N'19861230');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship, DateOfBirth)
VALUES(N'386079', N'Jonas', N'Mensah', N'Ghana', N'19751004');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship)
VALUES(N'862947', N'Danielle', N'Justine', N'Wilder', N'Australia');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship, DateOfBirth)
VALUES(N'685948', N'Christopher', N'James', N'Dowdy', N'USA', N'19600510'),
      (N'629479', N'Françoise', N'Hélène', N'Guignon', N'France', N'19700618');
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship, DateOfBirth)
VALUES(N'374704', N'Maheen', N'Haana', N'Brunei', N'19850904');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship, DateOfBirth, DateOfDeath)
VALUES(N'462804', N'Erin', N'Spears', N'UK', N'19221114', N'20000502');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship)
VALUES(N'830418', N'Johann', N'York', N'USA');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship, DateOfBirth)
VALUES(N'480507', N'Daniela', N'Palau', N'Mexico', N'19780415');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship, DateOfBirth)
VALUES(N'183958', N'Bertrand', N'Alain', N'Prison', N'Canada', N'19821008');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, LastName, Citizenship, DateOfBirth)
VALUES(N'528484', N'Peter', N'Walden', N'New Zealand', N'19560622');
GO
INSERT INTO Authorship.Authors(AuthorNumber, FirstName, MiddleName, LastName, Citizenship, DateOfBirth)
VALUES(N'940759', N'Meghan', N'Danielle', N'Brawer', N'USA', N'19810422');
GO

INSERT INTO  Publishing.AreasOfInsterest(AreaOfInterest, [Description])
VALUES(N'Operating Systems', N'This includes all types of operating systems, whether on desktops or servers. This also covers Oss in cell phones and other portable devices. This also includes operating systems in industrial devices used in health care, cars, or robotics, etc.'),
      (N'Application Programming', N'This includes all computer languages such as C-based (C, C++, Java, and C#), web-based languages (JavaScript, VBScript, PHP, etc), and others.'),
      (N'Network Design, Setup, and Administration', N'This area deals with techniques to design, create, and manage a computer network'),
      (N'Network/Information Security', N'All types of computer security issues are dealt with, including availability or lack of, accessibility or lack of, etc.'),
      (N'Database Administration', N'Database administration deals with both desktop and enterprise ways to create and manage databases, regardless of their sizes.'),
      (N'Database Developement', N'Database development includes desktop and enterprise database developement as well as means to provide access to all types of devices, including tablet PCs (portable computers) and others.'),
      (N'Web Design and Development', N'This includes different topics on the arts of web design as well as general views of web development, mainly the programmatic functionalities or dynamic web sites or web services. This area (or the articles) may or may not include actual programming).'),
      (N'Robotics', N'Robotics include industrial machines as well as small devices such as printers.');
GO

INSERT INTO  Authorship.ReviewersAreasOfInsterest(ReviewerNumber, AreaOfInsterestID)
VALUES(N'297948', 1001), (N'297948', 1008), (N'297948', 1003), (N'282947', 1002),
      (N'294859', 1005), (N'248374', 1001), (N'248374', 1008), (N'284058', 1006),
	  (N'284058', 1007), (N'206486', 1001), (N'280485', 1003), (N'280485', 1008),
	  (N'280485', 1004), (N'282504', 1008), (N'280486', 1007), (N'280486', 1006),
	  (N'240858', 1007), (N'260850', 1008), (N'260850', 1002), (N'260850', 1007),
	  (N'260850', 1006), (N'204068', 1003), (N'204068', 1006), (N'280680', 1004),
	  (N'280684', 1001), (N'280684', 1006), (N'249595', 1005), (N'249696', 1004);
GO

INSERT INTO Authorship.Manuscripts(ManuscriptCode, Title)
VALUES(N'2683-0475', N'Transitioning From Desktop to Enterprise Database'),
      (N'4005-0618', N'Information Transmission in  Health Care Management'),
      (N'6028-3058', N'General Data Analysis on Electronic Spreadsheets'),
      (N'4293-7405', N'Etude Méthodique des Sites Web Intéractifs'),
      (N'8060-0568', N'Comparative Study of Microsoft Windows and Linux'),
      (N'2947-5085', N'Approaches to Exception Handling in Microsoft SQL Server'),
      (N'9270-4957', N'Overview of Printing Functionality'),
      (N'2848-6158', N'Introduction of Biometric Authentication');
GO

INSERT INTO Authorship.Manuscripts(ManuscriptCode, Title, Notes)
VALUES(N'5020-4860', N'Quelle Language Informatique Est Approprié Pour Votre Site Web? ', N'Choosing the Appropriate Computer Language for Your Website'),
      (N'6040-5968', N'Fundamentos de Certificados de Aplicaciones para la Web', N'Foundations of Application Certificates for the Web');
GO
INSERT INTO Authorship.Manuscripts(ManuscriptCode, Title)
VALUES(N'9285-4068', N'The Evolution of C-Based Languages'),
      (N'9713-0475', N'Information Assurance and the Current Cultural Environment'),
	  (N'2845-0684', N'Error and Exception Handling: Is There a Difference?'),
	  (N'9203-9472', N'What Happened to OS/2?'),
	  (N'7024-7058', N'Context-Based Security in Oracle Databases');
GO

INSERT INTO Authorship.ManuscriptsStatus(ManuscriptStatus, [Description])
VALUES(N'Approved', N'This implies that the manuscript has been approved and is ready for publication or it has been published.'),
      (N'Assigned', N'This means that the manuscript has been assigned to a reviewer'),
      (N'In Transition', N'The manuscript has been received but it is "traveling" somehow. This means that the manuscript is not currently under one particular person or institution care. This status should be as temporary and short as possible.'),
      (N'Received', N'This indicates that a manuscript has been received but no other action has been taken towards it'),
      (N'Rejected', N'The manuscript has been checked and reviewed but was deemed unworthy of publication'),
      (N'Under Review', N'This option is for a manuscript that has been received, assigned to a reviewer, and the reviewer wants to indicate that he/she is still working on it. In other cases, a reviewer has received the script a second time and reviewing it once more.');
GO

INSERT INTO Authorship.ManuscriptsAuthors(AuthorNumber, ManuscriptCode)
VALUES(N'685948', N'4005-0618'), (N'940759', N'2848-6158'), (N'637444', N'2845-0684'),
      (N'180047', N'8060-0568'), (N'386079', N'2683-0475'), (N'685948', N'7024-7058'),
      (N'462804', N'9203-9472'), (N'940759', N'6028-3058'), (N'629479', N'5020-4860'),
      (N'374704', N'2947-5085'), (N'629479', N'4293-7405'), (N'830418', N'7024-7058'),
      (N'924935', N'9285-4068'), (N'862947', N'2848-6158'), (N'830418', N'2848-6158'),
      (N'180047', N'9713-0475'), (N'183958', N'4293-7405'), (N'480507', N'6040-5968'),
      (N'528484', N'9270-4957');
GO

INSERT INTO Authorship.ManuscriptsRatings(ManuscriptRating, Explanation)
VALUES(N'1 - Poor', N'The work was poorly done and the manuscript should not be further considered. The manuscript should be wholly rejected.'),
      (N'2 - Insufficient', N'The manuscript lacks work. It may not be rejected if more work is done on it.'),
      (N'3 - Acceptable', N'If no rigorous work is required, the manuscript can be published (acceptable) but could also be improved.'),
      (N'4 - Good', N'The manuscript is well written and can seriously be considered for publication.'),
      (N'5 - Excellent', N'The work done is without flaws and meets the highest standards in litterature and field.');
GO

INSERT INTO Authorship.ManuscriptsReviewUpdates(DateUpdated, ReviewerNumber, ManuscriptCode, ManuscriptRating)
VALUES(N'20121122', N'280485', N'4005-0618', N'4 - Good'),
      (N'20121127', N'297948', N'4293-7405', N'5 - Excellent'),
      (N'20121127', N'280680', N'4005-0618', N'4 - Good'),
      (N'20121128', N'280485', N'2848-6158', N'2 - Insufficient'),
      (N'20121130', N'280485', N'2683-0475', N'1 - Poor'),
      (N'20121203', N'280680', N'2848-6158', N'1 - Poor'),
      (N'20121206', N'284058', N'4293-7405', N'4 - Good'),
      (N'20121212', N'280680', N'9713-0475', N'3 - Acceptable'),
      (N'20121212', N'297948', N'6040-5968', N'5 - Excellent'),
      (N'20121212', N'248374', N'9203-9472', N'5 - Excellent'),
      (N'20121212', N'282947', N'9285-4068', N'5 - Excellent'),
      (N'20121213', N'248374', N'9270-4957', N'1 - Poor'),
      (N'20121214', N'248374', N'6028-3058', N'4 - Good'),
      (N'20121214', N'284058', N'6028-3058', N'3 - Acceptable'),
      (N'20121215', N'280485', N'7024-7058', N'4 - Good'),
      (N'20121215', N'248374', N'2683-0475', N'2 - Insufficient'),
      (N'20121215', N'260850', N'9285-4068', N'5 - Excellent'),
      (N'20121216', N'280485', N'2848-6158', N'3 - Acceptable'),
      (N'20121220', N'280680', N'6028-3058', N'4 - Good'),
      (N'20121220', N'284058', N'5020-4860', N'4 - Good'),
      (N'20121222', N'249696', N'6028-3058', N'4 - Good'),
      (N'20121228', N'248374', N'8060-0568', N'5 - Excellent'),
      (N'20121230', N'206486', N'9285-4068', N'3 - Acceptable'),
      (N'20130103', N'282947', N'2845-0684', N'4 - Good'),
      (N'20130105', N'280680', N'7024-7058', N'5 - Excellent'),
      (N'20130108', N'204068', N'9713-0475', N'4 - Good'),
      (N'20130110', N'280684', N'9285-4068', N'4 - Good'),
      (N'20130111', N'260850', N'5020-4860', N'5 - Excellent'),
      (N'20130115', N'280680', N'2947-5085', N'4 - Good');
GO

INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121116', N'2683-0475', N'Received'), (N'20121116', N'4005-0618', N'Received'),
      (N'20121116', N'6040-5968', N'Received'), (N'20121120', N'6028-3058', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121120', N'6040-5968', N'Assigned', N'297948'),
      (N'20121121', N'4005-0618', N'Assigned', N'280485'),
      (N'20121122', N'6040-5968', N'Under Review', N'297948');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121124', N'4293-7405', N'Received'), (N'20121124', N'7024-7058', N'Received'),
      (N'20121126', N'2848-6158', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121126', N'4005-0618', N'Assigned', N'280680'), (N'20121126', N'4293-7405', N'Assigned', N'297948'),
      (N'20121127', N'2683-0475', N'Assigned', N'280485'), (N'20121128', N'2848-6158', N'Assigned', N'280485');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121128', N'4005-0618', N'Approved'), (N'20121128', N'9203-9472', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121130', N'6040-5968', N'Under Review', N'297948'), (N'20121130', N'7024-7058', N'Assigned', N'280680');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121201', N'8060-0568', N'Received'), (N'20121201', N'9270-4957', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121201', N'2848-6158', N'Assigned', N'280680');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121201', N'9285-4068', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121202', N'7024-7058', N'Under Review', N'280680'), (N'20121202', N'4293-7405', N'Assigned', N'284058');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121204', N'5020-4860', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121205', N'4293-7405', N'Under Review', N'284058'), (N'20121206', N'6040-5968', N'Under Review', N'297948');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121208', N'9713-0475', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121210', N'8060-0568', N'Assigned', N'248374'), (N'20121210', N'7024-7058', N'Under Review', N'280680'),
	  (N'20121210', N'9285-4068', N'Assigned', N'282947');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121212', N'9270-4957', N'Assigned', N'248374'), (N'20121214', N'2683-0475', N'Assigned', N'248374'),
	  (N'20121215', N'6028-3058', N'Assigned', N'248374'), (N'20121215', N'6028-3058', N'Assigned', N'284058'),
	  (N'20121215', N'9713-0475', N'Assigned', N'204068');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121215', N'2683-0475', N'Rejected');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121215', N'9285-4068', N'Assigned', N'260850'),
      (N'20121215', N'7024-7058', N'Under Review', N'280680'),
	  (N'20121216', N'2848-6158', N'Under Review', N'280485'),
	  (N'20121216', N'9713-0475', N'Assigned', N'280680'),
	  (N'20121218', N'5020-4860', N'Assigned', N'284058'),
	  (N'20121218', N'6028-3058', N'Assigned', N'280680'),
	  (N'20121220', N'9713-0475', N'Under Review', N'204068');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121220', N'2845-0684', N'Received'),
      (N'20121220', N'2845-0684', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121220', N'6028-3058', N'Assigned', N'249696'),
      (N'20121220', N'5020-4860', N'Assigned', N'260850'),
	  (N'20121222', N'2845-0684', N'Assigned', N'282947');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121226', N'2947-5085', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121226', N'2947-5085', N'Received');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo, Notes)
VALUES(N'20121228', N'7024-7058', N'Under Review', N'280680', N'Translated from French to English by Walter Englund.');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121228', N'5020-4860', N'Approved'), (N'20121228', N'5020-4860', N'Approved');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20121228', N'9713-0475', N'Under Review', N'204068'),
      (N'20121229', N'2947-5085', N'Under Review', N'280680');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121230', N'2947-5085', N'Approved'), (N'20121230', N'9270-4957', N'Rejected');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, Notes)
VALUES(N'20121230', N'8060-0568', N'Approved', N'Translated from French to English by Walter Englund.');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20121230', N'4293-7405', N'Approved'), (N'20121230', N'6028-3058', N'Approved'),
      (N'20121230', N'2848-6158', N'Approved'), (N'20121230', N'2848-6158', N'Approved');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20130102', N'7024-7058', N'Assigned', N'280485');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20130110', N'2845-0684', N'Approved');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus, AssignedTo)
VALUES(N'20130103', N'9713-0475', N'Under Review', N'204068');
GO
INSERT INTO Authorship.ManuscriptsStatusChanges(DateChanged, ManuscriptCode, ManuscriptStatus)
VALUES(N'20130115', N'7024-7058', N'Approved'), (N'20130115', N'9203-9472', N'Approved'),
      (N'20130115', N'9285-4068', N'Approved'), (N'20130115', N'9713-0475', N'Approved');
GO

Press F5 to execute

Introduction to Index Uniqueness

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 an 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:

If you are using the New Index dialog box, under Index Type, click the Unique check box

If you are using the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to Yes

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:

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

If an index is unique but you want to reverse this characteristic:

If you are using the New Index dialog box, under Index Type, remove the check mark on the Unique check box
If you are using the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to No

Unique Indexes and Data Entry

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:

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)

Options on Indexes

Disabling an Index

You have the option of using an index when you want or asking the database engine to ignore it for some operations. This is done by disabling the index. Of course, you do this after creating the index, that is, by modifying an existing index.

To disable an index, create an ALTER INDEX statement and add the DISABLE flag before the end statement.

Practical Learning: Disabling an Index

Click inside the Query Editor and press Ctrl + A

To create records, type the following:

USE InformationTechnologyJournal1;
GO

ALTER INDEX IX_ReviewersCitizenships ON Authorship.Reviewers
DISABLE;
GO

To execute, on the SQL Editor toolbar, click the Execute button

Setting a Condition

By default, an index includes all records that involve the columns specified in the parentheses of the table. Instead of considering all records when looking for values, you can set a condition that asks the database engine to check only specific records and/or to ignore some other records. To set the condition, after the name of the table and its parentheses, create a WHERE condition.

Practical Learning: Setting a Condition on an Index

Click inside the Query Editor and press Ctrl + A

To create records, type the following:

USE InformationTechnologyJournal1;
GO

CREATE NONCLUSTERED INDEX IX_AuthorsCitizenships
ON Authorship.Authors(FirstName, LastName)
WHERE Citizenship = N'USA';
GO

To execute, on the SQL Editor toolbar, click the Execute button

Including Additional Columns

We have already seen how to specify the columns that would compose the index. Besides those columns, you can add some others that can make it fast to find records.

To add columns to an index, when creating it, after the name of the table and its parentheses, add the INCLUDE keyword and one or a list of fields.

Practical Learning: Including Additional Columns to an Index

Click inside the Query Editor and press Ctrl + A

To create records, type the following:

USE InformationTechnologyJournal1;
GO

CREATE NONCLUSTERED INDEX IX_AuthorsNames
ON Authorship.Authors(LastName, Citizenship)
INCLUDE (FirstName);
GO

To execute, on the SQL Editor toolbar, click the Execute button
Close Microsoft SQL Server
When asked whether you want to save, click No