Temporal Tables Granularity I've start using temporal tables on SQL Server 2017 and I've noticed that rapid updates aren't recorded in the history table. Does anyone know if this is configurable or do I just have to live with this limitation.

More info... 

For testing I have a Table setup with a single row

In SSMS If I do something like this:

UPDATE MyTable SET MyCol = 'a' 
UPDATE MyTable SET MyCol = 'b'
UPDATE MyTable SET MyCol = 'c'


The History Table doesn't have any records for 'a' and 'b' changes.

I repeated this with a short delay between the updates like this:

UPDATE MyTable SET MyCol = 'a'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'b'
WAITFOR DELAY '00:00:00.400'
UPDATE MyTable SET MyCol = 'c'


And this results in the 'a' and 'b' being recorded in the history. However the lower the delay the less likely the records are to appear.

UPDATED:

Here a full script to show the issue. With the delays in place the select return 4 rows, with the delays commented out only one row is returned

-- Create Table 
CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] NOT NULL,
    [MyCol] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- Add History
ALTER TABLE  [dbo].[MyTable] ADD
    SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);


ALTER TABLE  [dbo].[MyTable] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));

-- Add a Single Row
INSERT INTO [dbo].[MyTable] (Id, MyCol)  
VALUES ('2a2979d2-529a-442b-b272-3662feda731b', 'init');

-- Update the Row Multiple times,  Comment out the Delays or lower them to under around 400ms to see inconsistent results
UPDATE MyTable SET MyCol = 'a'; 
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'b';
WAITFOR DELAY '00:00:01'
UPDATE MyTable SET MyCol = 'c';

-- Query the history
SELECT * From [dbo].[MyTable] FOR SYSTEM_TIME ALL;

-- Remove the test tables
ALTER TABLE [dbo].[MyTable] SET(SYSTEM_VERSIONING = OFF);
DROP TABLE [dbo].[MyTable_History];
DROP TABLE [dbo].[MyTable];
The documentation says:


  ⓘ Note
  
  FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Those rows will be
  generated if you perform multiple updates on the same primary key
  within the same transaction. In that case, temporal querying surfaces
  only row versions before the transactions and ones that became actual
  after the transactions. If you need to include those rows in the
  analysis, query the history table directly.


You have typed SysStartTime and SysEndTime as datetime2 with no fractional seconds. This increases the chance they will compare equal, giving a "period of validity with zero duration".

You can see the history rows exist by looking in the history table directly:

SELECT * FROM [dbo].[MyTable_History];


The only issue is that the FOR SYSTEM_TIME query does not show rows where SysStartTime and SysEndTime are equal.

db&lt;>fiddle demo