SQL Server – What is BMK Operator

execution-planoperatorsql server

I was trying to answer this question stating from clause is optional..But i am stuck with an Operator in plan..below is screenshot of execution plan ..

enter image description here

As you can see there is BMK operator in query plan ,but no indication showing how it is calculated..

steps i have tried so far:
I started searching with BMK1000 ,but it shows a bunch of questions with the same operator.finally i found one thread which says "The BMK that you're referencing is the storage location within the heap that gets kept with the nonclustered index instead of the cluster key."..But not sure how this is related to me,since i don't have any indexes..

ASK:
what is the BMK operator and how it is calculated.Any Pointers also will be helpfull

Here is SQLFiddle to Repro the issue

Best Answer

It's not an operator in its own right. It is an output column from the table scan operator on the heap. It is the "bookmark" that contains the physical address of the row (this is the same bookmark as is referred to in the phrase "bookmark lookup").

This is passed along the pipeline into the update operator so it knows the row it should be updating.

In SQL Server 2016 you can see the actual values for this column by using the query_trace_column_values extended event

enter image description here

Plugging those into sys.fn_PhysLocFormatter shows the file:page:slot

SELECT sys.fn_PhysLocFormatter(0xB002000001), 
       sys.fn_PhysLocFormatter(0xB0020000010001)

Returns 

+------------------+------------------+
| (No column name) | (No column name) |
+------------------+------------------+
| (1:688:0)        | (1:688:1)        |
+------------------+------------------+
Related Question