What's this column [Bmk1002] in the table scan operator of my execution plan?

Question

I have a simple script.

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
    DROP TABLE dbo.Customers;
GO
CREATE TABLE dbo.Customers
(
 custid INT NOT NULL,
 companyname VARCHAR(125) NOT NULL,
 phone VARCHAR(120) NOT NULL,
 address VARCHAR(150) NOT NULL
);
INSERT  INTO dbo.Customers
        (custid, companyname, phone, address)
VALUES  (1, 'cust 1', '(111) 111-1111', 'address 1'),
        (2, 'cust 2', '(222) 222-2222', 'address 2'),
        (3, 'cust 3', '(333) 333-3333', 'address 3'),
        (4, 'cust 4', '(444) 444-4444', 'address 4'),
        (5, 'cust 5', '(555) 555-5555', 'address 5');
IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL
    DROP TABLE dbo.CustomersStage;
GO
CREATE TABLE dbo.CustomersStage
(
 custid INT NOT NULL,
 companyname VARCHAR(125) NOT NULL,
 phone VARCHAR(120) NOT NULL,
 address VARCHAR(150) NOT NULL
);
INSERT  INTO dbo.CustomersStage
        (custid, companyname, phone, address)
VALUES  (2, 'AAAAA', '(222) 222-2222', 'address 2'),
        (1, 'cust 1111111111', '(111) 111-11111111111111', 'address 111111111'),
       -- (1, 'cust 1111111112222222222', '(111) 111-1111111112222222222', 'address 1111111112222222222'),
        (3, 'cust 3', '(333) 333-3333', 'address 3'),
        (5, 'BBBBB', 'CCCCC', 'DDDDD'),
        (6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
        (7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

SELECT  *
FROM    dbo.Customers;
SELECT  *
FROM    dbo.CustomersStage;

SET STATISTICS XML ON;
MERGE INTO dbo.Customers d
USING dbo.CustomersStage s
ON d.custid = s.custid
WHEN MATCHED THEN
    UPDATE SET d.companyname = s.companyname,
               d.phone = s.phone,
               d.address = s.address
WHEN NOT MATCHED THEN
    INSERT (
            custid,
            companyname,
            phone,
            address
           )
    VALUES (
            s.custid,
            s.companyname,
            s.phone,
            s.address
           );
SET STATISTICS XML OFF;

SELECT  *
FROM    dbo.Customers;

The execution plan of the MERGE statement looks like this.

You can download the execution plan here at https://drive.google.com/file/d/0B4xMAUd6DN6XdkZyTmJkdF9TY3c/view?

My question is, what exactly is this [Bmk1002]? Hope someone can help to explain it.

[Bmk1002] may be: 1) Nested loops (joins, outer references); 2) Index seek; One example is here: http://sqlbitdatatype.blogspot.kr/2012/03/bookmark-explain-plan-understanding_22.html — Khazratbek, Mar 25 '16 at 05:40

Martin Smith · Accepted Answer · 2016-03-25T13:34:18.250

It is the "bookmark" (also referred to in the phrase "bookmark lookup").

This is the physical location of the row (File:Page:Slot).

Customers is a heap

SELECT %%lockres%%
FROM Customers

Will show you these values (if it had a clustered index you could use %%physloc%% instead but the raw output from this is not as friendly).

It is needed in this execution plan because the specification of merge requires it to throw an error if there is an attempt to update or delete the same row more than once.

As there is no other unique key available SQL Server uses this bookmark instead as a value guaranteed to be able to uniquely identify a row.

The plan calculates ROW_NUMBER() OVER (PARTITION BY Bmk1002 ORDER BY Bmk1002) ^* and the assert operator raises an error if this is ever >1 (which would happen if there were multiple rows in dbo.CustomersStage that join to the same one in dbo.Customers).

^* Actually the plan calls conditional_row_number rather than row_number. This is an internal undocumented function. I imagine that it uses the conditional version rather than straight row_number as it needs to ignore rows with an action of "insert" in the check for duplicates.

Thanks Martin! "bmk" stands for bookmark which I should have thought of. — Just a learner, Mar 25 '16 at 17:47

score 1 · Answer 2 · answered Mar 25 '16 at 06:47

The Bmkxxxx is an additional column, not referenced in the query. It's the key value from the tablescan and it will be used in the later parts of the query execution. Check out this excellent ebook from Red-gate to learn more about execution plans. http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf

And download this tool from SQL Sentry to help you navigate execution plans. http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view

And this course at Pluralsight is excellent. https://www.pluralsight.com/courses/sqlserver-query-plan-analysis

What's this column [Bmk1002] in the table scan operator of my execution plan?

2 Answers2