When reading that title, you probably want to get to concept of time travel first, because if you could really do that, you wouldn’t have any more backup and recovery problems. The time travel I will cover is about getting to past versions of data in your data warehouse tables. This is done in a similar way to what you can do with temporal tables in SQL Server, but also VERY different.

The schema locks section is a wee bit of a follow on to Part 4 where I covered isolation where I showed how updating data was handled concurrently. In this post I wanted to briefly finish that conversation and note how it works when you have schema locks.

Schema locks

You may not realize that you have ever encountered a schema lock. They are (well, should be) quite rare in most production workloads. There are different ways schema locks rear their head, and probably the most typical is indexing. You rebuild something like a clustered index in a non-online matter, and any users may have to wait.

Where I notice them most of the time is when I am building code that I am not quite sure I want to commit to the database, so I might do something like start a transaction, do the change, then test it out. For example, say you have a table like:

CREATE TABLE TestTruncate
(
    TestSchemaLock int
); 

And you want to add a column and you want to try something before you add this new column for everyone:

--Connection A
BEGIN TRANSACTION
--can't be NOT NULL column. Will cover that process later
ALTER TABLE TestSchemaLock
    ADD Value varchar(10) NULL;

In this connection:

--Connection A
SELECT *
FROM   TestSchemaLock;

Returns 0 rows, with the new Value column added:

But go to a different connection:

--Connection B
SELECT *
FROM   TestSchemaLock; 

Any user trying to fetch data from that table will be blocked until that table modification has been cleared. Seems okay, but what about tools listing objects? These connections are blocked from accessing the tables.

So while this would likely never affect you for long in a production space, I do block myself occasionally when I am doing exploratory work.

Like in this case, if you go to your instance in management studios and try to expand any of the object lists (for example, in management studio… You will see this:

But that (expanding) won’t go away until you commit or rollback your transaction:

--Connection A
ROLLBACK;

Now, your other connections (and SSMS) will be cleared to use your object. (which is kind of fun to watch if you have enough stuff blocked ON the screen.

Before I did the rollback I checked the locks being held using the same DMV you would use in SQL Server (DMV discussion to follow in a few weeks):

DECLARE @FocusSpid INT = 188;
SELECT request_session_id,
      CASE WHEN request_session_id = @FocusSpid
           THEN 'BLOCKER'   
       WHEN request_status = 'WAIT'
          THEN 'BLOCKED BY'
          ELSE '' END AS Status,
       request_status,
       resource_type, resource_description,
       request_mode, request_type
FROM  sys.dm_tran_locks
WHERE request_mode LIKE '%sch%' -- schema locks
ORDER BY request_session_id; 

This returns:

There is a lot to unpack in that return set, (so I will leave it to the previously mentioned later post to dig deeper into the details) but you can see that the schema lock was blocked and there were a couple of connections blocked. This goes for any type of object, such as a stored procedure:

BEGIN TRANSACTION
GO
CREATE PROCEDURE bob AS SELECT 'hi'; 

And also just beware that this affects any user trying to look at the stored procedures on your instance.

Note, this really isn’t different from the way SQL Server works, but it felt germane not only to last week’s topic of Atomicity and Isolation, but also to the next section’s discussion of time travel.

Time Travel

Temporal tables have been around for over 10 years now, and I wrote a series on them back in 2015 (I am thinking about refreshing it, since it was based on a SQL Server 2016 CTP, and AI can really help you with them now in ways that we didn’t really dream of back then).

I am using them in my SQL Azure instance on Fabric when I am building a metadata change log. Drop a column, I want that column to show up in my history. Add one, I want to know when that was added.

But in a Fabric Data Warehouse, system-versioned temporal tables are not allowed (note SQL database in Microsoft Fabric is listed, but not the Warehouse here in the documentation for temporal tables)

So what is a person to do? With some caveats, use the built-in time travel. Time travel is a feature built into the way data is stored in the data warehouse.

If you are wanting to dig deeper, the Data Mozart site has an article called: Parquet file format – everything you need to know!. I am personally in a journey to figure out the feature’s abilities at this point, so I am mostly sticking to the very practical side of this stuff…for now.

The feature we will look at will let you see how the data in a table looked at some time in the past. There are some limitations with this model that will not necessarily allow this feature to be used as a primary method of capturing history, but it is an amazingly useful feature. More details can be found here in the documentation as well.

An Example

So say you have the following table, with 5 rows loaded:

DROP TABLE IF EXISTS SchemaTesting
CREATE TABLE SchemaTesting
(
    SchemaTestingId INT NOT NULL,
    Value1   VARCHAR(10)
);
--load 5 rows
INSERT INTO SchemaTesting
SELECT Value, RIGHT('000000000' + CAST(Value AS VARCHAR(10)),10)
FROM GENERATE_SERIES(1,5); 

Now, I am going to capture the point in time between the time I load those 5 rows, and when I add the next group.

SELECT CONVERT(char(23),SYSDATETIME(), 127); 

This will return the time in the format we need in a few minutes:

2026-09-08T15:01:25.802

Now, add another set of 100 rows:

DECLARE @startingPoint INT = 
     (SELECT MAX(SchemaTestingId) FROM SchemaTesting);
--load 100 rows
INSERT INTO SchemaTesting
SELECT Value, RIGHT('000000000' + CAST(Value AS VARCHAR(10)),10)
FROM   GENERATE_SERIES(@startingPoint + 1,@startingPoint + 100);

Then fetch the time again:

2026-09-08T15:01:50.631

Now, let’s count the rows currently in the table:

SELECT COUNT(*)
FROM   SchemaTesting; 

This returns that there are 105 rows currently.

But let’s say a user was saying earlier (around '2026-09-08T15:01:25.802' as our customers are amazingly specific!), I need to know what data was in the SchemaTesting table. In Fabric Data Warehouse T-SQL, while we don’t have system valued temporal tables that need to be pre-configured, we do have something a bit nicer (with some caveats that I will note in a bit, so don’t stop reading here )

The syntax is a query hint: (Note that this syntax ONLY seems to make sense with SELECT clauses and in a later example it seemed to ignore the clause with an INSERT statement, but it can be used in a different types of statements as you will see).

OPTION (FOR TIMESTAMP AS OF 'YYYY-MM-DDTHH:MM:SS.mmm')

You can only send 3 fractional digits to the clause for the seconds. So this will fail:

SELECT SchemaTestingId
FROM   SchemaTesting
OPTION (FOR TIMESTAMP AS OF '2026-01-01T00:00:00.0000');

With this error:

Msg 241, Level 15, State 5, Line 460
Conversion failed when converting date and/or
time from character string.

Take the last 0 off the string and it will give you a different error about the time that we will cover later. You also cannot use variable:

DECLARE @datetime2 datetime2(3) = sysdatetime();
SELECT SchemaTestingId
FROM   SchemaTesting
OPTION (FOR TIMESTAMP AS OF @datetime2); 

As this will cause the following error:

Msg 241, Level 16, State 7, Line 169
Conversion failed when converting date and/or
time from character string.
Msg 241, Level 15, State 4, Line 180
Conversion failed when converting date and/OR
time from character string.

Based on this error, I tried the following, but it also failed me despite the fact that the string that is contained in @stringTime will work:

DECLARE @stringTime varchar(33) = CONVERT(varchar(23),sysdatetime(), 127);
SELECT @stringTime;
SELECT SchemaTestingId
FROM   SchemaTesting
OPTION (FOR TIMESTAMP AS OF @stringTime); 

Note that it is a pre-execution error, and you won’t see any output from the SELECT @stringTime statement if you execute the following SELECT as well. it will fail compilation If you try to compile that into a stored procedure for example, you get the same 241 error. So you need to get the time you are after:

Okay, using the timestamp we captured earlier, we can see the rows in the table just after we added the first 5 rows.

SELECT SchemaTestingId
FROM   SchemaTesting
OPTION (FOR TIMESTAMP AS OF '2026-09-08T15:01:25.802');

This returns the 5 original rows:

Now, remember we have 105 rows in the table currently. And then you execute that statement that every single DBA truly is terrified of. The DELETE (or UPDATE) where you (accidentally!) don’t remember the WHERE clause.

DELETE FROM SchemaTesting
--WHERE <some clause that you commented
--       out because you were testing> ; 

“Uh oh,” said the dba who thought they were fixing a problem at 3:00 AM but rather just created a MAJOR issue. All the data is gone and this innocent message says it all:

(105 rows affected)

Two choices.

1. Panic.
2. Panic, but then realize the platform you are on has help.

If you can find the time you executed that statement, you can get it back. If you are lucky enough to be doing this on a quiet enough server you can use top 1, but note that once you run this statement looking for the word DELETE you will add a row for the query looking for DELETE!

SELECT top 1
    CONVERT(varchar(23),start_time, 127) as time_stamp,
    --end_time,
    login_name,
    status, -- Succeeded, Failed, or Canceled
    statement_type, -- SELECT, INSERT, UPDATE, DELETE etc.
    row_count,
    command -- This contains the actual SQL statement text
FROM queryinsights.exec_requests_history
where command like '%delete%'
ORDER BY start_time DESC;

So this is the the statement started, and so the data should be at that moment how it looked.

SELECT COUNT(*)
FROM   SchemaTesting
OPTION (FOR TIMESTAMP AS OF '2026-09-08T15:27:37.311');

You will see that those 105 rows are back. You can look at the data to make sure, especially depending on the data you expected it to be. So how can you save yourself? Can I just insert the data back in?

This seemed too good to be true…

INSERT INTO SchemaTesting(SchemaTestingId,Value1)
SELECT SchemaTestingId,Value1
FROM SchemaTesting 
--time right after the insert of 1000 rows
OPTION (FOR TIMESTAMP AS OF '2026-09-08T15:27:37.311'); 

And it was. AS noted, you need to be careful that the FOR TIMESTAMP option:

(0 rows affected)

No error, but no data inserted. But all hope is not lost. This following did work nicely.

CREATE TABLE SchemaTestingRestore
AS 
SELECT SchemaTestingId,Value1
FROM SchemaTesting 
--time right after the insert of 1000 rows
OPTION (FOR TIMESTAMP AS OF '2026-09-08T15:27:37.311');

Yes! This output what we were hoping:

(105 rows affected)

So now you can put the data back into your table.

INSERT INTO SchemaTesting(SchemaTestingId,Value1)
SELECT SchemaTestingId,Value1
FROM   SchemaTestingRestore;

Back to 105 rows!

(105 rows affected)

Very useful.

The caveats

Like Genie noted when fulfilling wishes: “There are a few, uh, provisos, a couple of quid pro quos” and here there are several. Firstly, time travel isn’t eternal. you will get 7 – 150 days depending on how you have it configured (documentation on this here, and (don’t take my word as knowing yet, but.. there has to be some cost involved, either time, performance, or money.

But the main caveat I want to share is what happens when you modify a table. So for this, I am going to start over with a new table:

DROP TABLE IF EXISTS SchemaMod;
CREATE TABLE SchemaMod
(
    SchemaModId INT NOT NULL,
    Value1   VARCHAR(10)
);
--load 5 rows
INSERT INTO SchemaMod
SELECT Value, RIGHT('000000000' + 
                  CAST(Value AS VARCHAR(10)),10)
FROM GENERATE_SERIES(1,5); 

Ok, with 5 rows, I will capture the current time (using a string using a query I created to make this easier to handle, and I probably end up with this in a scalar function soon enough:

SELECT 
   CONCAT('OPTION (FOR TIMESTAMP AS OF ''',CONVERT(varchar(23),sysdatetime(), 127),''')' );

This returned:

OPTION (FOR TIMESTAMP AS OF '2026-09-08T16:16:14.747');

So what will happen if I alter this table and add a new column?

ALTER TABLE SchemaMod
   ADD Value2 INT NULL; 

So then I tried to see the data before I altered the table. Drumroll please…

SELECT *
FROM   SchemaMod
--just after rows addded.
OPTION (FOR TIMESTAMP AS OF '2026-09-08T16:16:14.747'); 

Nooo!!!

Msg 12516, Level 16, State 2, Line 293
The TIMESTAMP in the query (2026-09-08T16:16:14.747) is BEFORE
the object was last changed (with ALTER). Specify a TIMESTAMP
at or after the last ALTER time (2026-09-08T16:17:01.980).

So yes, long story short, whenever you change the schema, drop the table, etc.. You lose the history in the table. So if you need longer term history, you will need to do your own solution. And while time travel will save you when you modify data incorrectly, even delete it, this requires that the table stays the same.

Why? Because (based on the first line of the limitations listed here in the documentation for time travel in a Fabric Data Warehouse:

Any modifications made to the schema of a table, including but not limited to adding or removing columns, are only queryable from the point in time the change was made. A time-travel query to a point in time before the schema change succeeds only when it references columns that already existed at that point in time, and fails if it references columns introduced later. Similarly, dropping and recreating a table with the same data removes its history.

So the structures have been reset.

Truncating a table

But, what about a TRUNCATE TABLE operation? This is why you it helps to have some idea of how things are implemented. Because it helps you to extrapolate how other situations will work. TRUNCATE TABLE, which typically is created to completely reset the physical structures of an object, is most likely going to fail as well. But if it isn’t 100% obvious, why not try it?

SELECT *
FROM   SchemaMod; 

So we currently have 5 rows.

SELECT CONCAT('OPTION (FOR TIMESTAMP AS OF ''',CONVERT(varchar(23),sysdatetime(), 127),''')' );

Then let’s try:

TRUNCATE TABLE SchemaMod;

Then something happens that I did not expect:

--pre-truncate
SELECT *
FROM   SchemaMod
OPTION (FOR TIMESTAMP AS OF '2026-09-08T16:32:33.257');
--now
SELECT *
FROM   SchemaMod;

It did not actually reset the history/physical structures.

Ah, much learning must I do.

Summary

In this entry in my series of posts on the differences between T-SQL in a Fabric Data Warehouse and SQL Server, I covered, more or less, history. You don’t get history on objects (in fact you get schema blocking), but you do get built in data versioning, with some important caveats surrounding the stability of the physical structures (which interestingly are not reset by a TRUNCATE TABLE operation.