Visually Creating an Index
|
|
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. 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, in the Object Explorer, expand the
database that holds the table or view and expand the Tables node.
Right-click the table or view 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:
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:
From the boxes under Column Name, you can select each
column. Once you are ready, click OK.
 Practical
Learning: Visually Creating an Index
|
|
- In the Object Explorer, under DepartmentStore2, right-click Tables
and click Refresh
- Expand the Tables node. Right-click dbo.Employees and click Design
- Right-click anywhere in the white area and click Indexes/Keys...
- Click Add
- Click Columns and click its ellipsis button
- Click the combo box of the default EmployeeNumber and select
LastName
- Click the box under LastName
- Click the arrow of its combo box and select Username
- Click OK
- Click (Name) and type IX_Credentials
- Click Close
- Close the table
- When asked whether you want to save, click Yes
Creating an Index
With 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 window. 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 on 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. 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
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
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
In microsoft sql Server (and most database system),
an index is treated as an object. That is, an index can be checked or
deleted at will.
If you don't need an index anymore, you can delete it.
You can do this visually or manually.
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.
Here is an example:
USE Exercise;
GO
DROP INDEX IX_Employees ON Employees;
GO
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
USE Exercise;
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 or view 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 assist you with checking the existence of an index,
Transact-SQL provides the following formula:
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = IndexName)
Do something here
The primary thing you need to provide in this formula
is the name of the index. Once you have checked, you can take the
necessary action. Here is an example:
USE Exercise;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_Employees')
DROP INDEX IX_Employees
ON Employees
GO
CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
microsoft sql server supports various types of
indexes. The two broadest categories are clustered and non-clustered.
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, display the
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:
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:
-- =============================================
-- 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
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 (we
have already seen how to take care of this, using check constraints;
later on, we will see again how to create unique records)
- 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
While a clustered index uses a sorted list of records
of a table or view, 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, display the
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:
-- =============================================
-- 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
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.
Practical
Learning: Creating a Non-Clustered Index With SQL
|
|
- Click in the top section of the Query window, press Ctrl + A to
select the whole text
- To create an index using Transact-SQL, type the following:
USE HotelManagement1;
GO
IF EXISTS(SELECT name FROM sys.indexes
WHERE name = N'IX_RoomsIdentities')
DROP INDEX IX_RoomsIdentities
ON Rooms
CREATE NONCLUSTERED INDEX IX_RoomsIdentities
ON Rooms(RoomNumber, LocationCode);
GO
- Press F5 to execute
Great Stuff Abhinav!!!
ReplyDeleteKeep up the good work.
Thank's for appreciation abhinab..
ReplyDelete