Create a Table Index in Sql Server
CREATE [UNIQUE | DISTINCT] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_name (column_name [ASC | DESC],
column_name [ASC | DESC]...)
here UNIQUE means You can only have one primary key on each table. However, if you wish to enforce uniqueness in other non-key columns, you can designate that the index be created with the UNIQUE constraint. You can create multiple UNIQUE indexes for a single table and can include columns that contain NULL values (although only one NULL value is allowed per column combo).
[ CLUSTERED | NONCLUSTERED ] Specifies the index type, either CLUSTERED or NONCLUSTERED. You can only have one CLUSTERED index, but up to 249 NONCLUSTERED indexes. And index_name The table or view to be indexed.
The column or columns to be used as part of the index key.
[ ASC | DESC ] is The specific column order of indexing, either ASC for ascending order or DESC for descending order.
ClusterIndex :-
CREATE TABLE HumanResources.TerminationReason(
TerminationReasonID smallint IDENTITY(1,1) NOT NULL,
TerminationReason varchar(50) NOT NULL,
DepartmentID smallint NOT NULL,
CONSTRAINT FK_TerminationReason_DepartmentID
FOREIGN KEY (DepartmentID) REFERENCES
HumanResources.Department(DepartmentID)
)
Before I demonstrate how to use CREATE INDEX, it is important to remember that when a primary key is created on a column using CREATE TABLE or ALTER TABLE, that primary key also creates an index. In this example, a CLUSTERED index is created on the TerminationReasonID using ALTER TABLE:
ALTER TABLE HumanResources.TerminationReason
ADD CONSTRAINT PK_TerminationReason
PRIMARY KEY CLUSTERED (TerminationReasonID)
In this exercise, the TerminationReason table was created without a primary key defined, meaning that initially, the table was a “heap.” The primary key was then added afterwards using ALTER TABLE. The word CLUSTERED follows the PRIMARY KEY statement, thus also creating a clustered index with the new constraint:
ALTER TABLE HumanResources.TerminationReason
ADD CONSTRAINT PK_TerminationReason PRIMARY KEY CLUSTERED (TerminationReasonID)
Had the TerminationReasonID column not been chosen as the primary key, you could have still defined a clustered index on it by using CREATE INDEX:
CREATE CLUSTERED INDEX CI_TerminationReason_TerminationReasonID ON
HumanResources.TerminationReason (TerminationReasonID)
Had a nonclustered index already existed for the table, the creation of the new clustered index would have caused the nonclustered index to be rebuilt, in order to swap the nonclustered leaf level row identifier with the clustered key.
The NONCLUSTERED index in the example was created as follows:
CREATE NONCLUSTERED INDEX NCI_TerminationReason_DepartmentID ON
HumanResources.TerminationReason (DepartmentID)
Comments
Post a Comment