Nonclustered Index

Non Clustered will not sort the physical order of the table. Each index row in the nonclustered index contains the nonclustered key value and a row locator. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

Example:

CREATE TABLE SAMPLE11

(

ID INT ,

NAME VARCHAR(10),

TYPE_DESC VARCHAR(50)

)

Creating index on single column:

CREATE NONCLUSTERED INDEX IDX_SAMPLE11_NONCLU ON SAMPLE11 (ID)

Result:

Drop Index:

DROP INDEX SAMPLE11.IDX_SAMPLE11_NONCLU

Result:

Creating index on multiple column:

CREATE NONCLUSTERED INDEX IDX_SAMPLE11_NONCLU ON SAMPLE11 (ID,NAME)

Result:

Drop Index:

DROP INDEX SAMPLE11.IDX_SAMPLE11_NONCLU

Result: