Special Icon on System-Versioned Table in SQL Server 2016 – Interview Question of the Week #101

After installing SQL Server 2016 Developer and WideWorldImporters, I noticed a small watch-like icon beside some tables in SSMS Object Explorer. I called this an interview question, but I made it up myself. It is useful trivia because the icon points to a real database feature.

What does the special table icon mean?

In the SSMS version shown below, the small watch-like marker identifies a system-versioned temporal table. The original WideWorldImporters screenshot is worth keeping here: it shows the exact Object Explorer clue that prompted the question.

A temporal table has a current table and a related history table. SQL Server records the previous row version when current rows change. That makes point-in-time queries possible; it does not mean the database has an independent backup or a complete record of who made each change.

Verify it from metadata

When the icon is unclear or SSMS has changed, ask the connected database. In WideWorldImporters, this read-only query lists current system-versioned tables and their history tables:

SELECT SCHEMA_NAME(t.schema_id) AS TableSchema,
       t.name AS CurrentTable,
       t.temporal_type_desc,
       SCHEMA_NAME(h.schema_id) AS HistorySchema,
       h.name AS HistoryTable
FROM sys.tables AS t
LEFT JOIN sys.tables AS h ON h.object_id = t.history_table_id
WHERE t.temporal_type = 2
ORDER BY TableSchema, CurrentTable;

The short answer remains the one visible in the screenshot: that icon marks a system-versioned table. If you want to build one and query its history, see my simple temporal-table tutorial. The original setup notes for SQL Server 2016 Developer and SSMS 2016 remain historical context for this screen. For the sample database, use my updated install guide.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.