U1YUtbve

1use master;
2go
3
4alter database DB219238 set single_user with rollback immediate;
5drop database if exists DB219238;
6go
7
8
9CREATE DATABASE DB219238
10ON PRIMARY 
11    -- Primary filegroup gets the default values
12    ( NAME = N'DB219238', 
13      FILENAME = N'D:DatabaseDataDB219238DB219238.mdf' , 
14      SIZE = 3MB , 
15      MAXSIZE = UNLIMITED, 
16      FILEGROWTH = 65536KB ), 
17FILEGROUP [DATA]  DEFAULT
18    -- This will hold the temporal table
19    ( NAME = N'Data', 
20      FILENAME = N'D:DatabaseDataDB219238Data.ndf' , 
21      SIZE = 1MB ,            -- deliberately small so I can observer growth events
22      MAXSIZE = UNLIMITED, 
23      FILEGROWTH = 1MB ),     -- deliberately small
24FILEGROUP [History]
25    -- This will hold the history table
26    ( NAME = N'History', 
27      FILENAME = N'D:DatabaseDataDB219238History.ndf' , 
28      SIZE = 1MB ,            -- deliberately small, as above
29      MAXSIZE = UNLIMITED, 
30      FILEGROWTH = 1MB )      -- deliberately small
31LOG ON 
32    -- Just the defaults
33    ( NAME = N'DB219238_log', 
34      FILENAME = N'D:DatabaseLogDB219238_log.ldf' , 
35      SIZE = 8192KB , 
36      MAXSIZE = 2048GB , 
37      FILEGROWTH = 65536KB );
38GO
39
40-- I don't want to be concerned with log growth    
41ALTER DATABASE DB219238 SET RECOVERY SIMPLE;
42GO
43	
44use DB219238;
45go
46
47create table dbo.Example_History
48(
49    ExampleId           int             not NULL,
50    Information         char(4100)      not NULL,
51    SysStartTimeUTC     datetime2(7)    not NULL,
52    SysEndTimeUTC       datetime2(7)    not NULL,
53) on History;    -- note the filegroup
54GO
55checkpoint;
56go
57
58
59create table dbo.Example
60(
61    ExampleId                           int IDENTITY(1,1)   not NULL,
62    Information                         char(4100)          not NULL,
63
64    SysStartTimeUTC                     datetime2(7) GENERATED ALWAYS AS ROW START  not NULL,
65    SysEndTimeUTC                       datetime2(7) GENERATED ALWAYS AS ROW END    not NULL,
66
67    constraint PK_Example primary key clustered
68    (
69        ExampleId ASC
70    ),
71
72    period for system_time (SysStartTimeUTC, SysEndTimeUTC)
73) on Data    -- note the filegroup
74with
75(
76    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.Example_History )
77);
78GO
79checkpoint;
80go
81	
82insert dbo.Example
83(
84    Information
85)
86select
87    '';
88GO 10000    -- arbitrary, but proved sufficient to demonstrate the case
89checkpoint;
90go
91	
92create clustered index IX_History on dbo.Example_History (ExampleId);
93checkpoint;