A Non-clustered index is built using the same 8K-page B-tree structure that is used to build a Clustered index, except that the data and the Non-clustered index are stored separately. A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered key, and the leaf level nodes of the Non-clustered index are made of index pages instead of data pages. The index pages of the Non-clustered index contain Non-clustered index key values with pointers to the storage location of these rows in the underlying heap table or the Clustered index.

SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.tbTable01') AND name = N'IDX_tbTable01_Col01')
	DROP INDEX [IDX_tbTable01_Col01] ON [dbo].[tbTable01]
GO

CREATE NONCLUSTERED INDEX [IDX_tbTable01_Col01] ON [dbo].[tbTable01]
(
	Col01 ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IDX_tbTable01_Col01] ON [dbo].[tbTable01]
(
	[Col01],[Col02]
)
INCLUDE ([Col03])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgTableIndexes01]
GO
CREATE NONCLUSTERED INDEX [IDX_tbTable01_Col01_1] ON [dbo].[tbTable01]
(
	[Col01] ASC
)
INCLUDE 
([Col04], [Col06], [Col08])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Sources:

Last modified: June 17, 2020

Author

Comments

Write a Reply or Comment