What data does the nonclustered indexes page contain?

Let's take a look at two examples:

 

1. Create a test table. C1 is the primary key.

 

Create Table [DBO]. [prodtable2] (

[C1] [int] identity (1, 1) notnull,

[C2] [datetime] Null,

[C3] [char] (25) null,

Constraint [pk_prodtable2]
Primary Key clustered

(

[C1] ASC

) With (pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on
[Primary]

) On [primary]

 

2. Create a non-clustered index:

Create nonclustered index (

[C2] ASC

)

Include ([C3]) with (pad_index = OFF, rows = OFF, sort_in_tempdb = OFF, ignore_dup_key = OFF, drop_existing = OFF, online = OFF, allow_row_locks = on, allow_page_locks = on) on
[Primary]

Go

 

3. query all the fields in the C1 table. The condition is C2:

 

Select *
From DBO. prodtable2

Where C2 = '2017-08-011. 100'

 

Execution Plan:

 

You can see that the query uses the index ix_c2 to return all data (C1, C2, C3) at a time. This indicates that in the cluster index table, the cluster is stored on the non-clustered index page.
Index key, value, nonclustered index value, and include field value.

 

 

4. Delete the primary key and run the same query to get the following execution plan:

 

We can see that the indexes ix_c2 and rid lookup are used to return data, indicating that the non-clustered index page level stores the RID (fileid: pageid: slotnumber) nonclustered In the heap table.
Index value and include field value.