-- This script creates 27 tables, with the 9 data types
-- specified in each of three formats (normal clustered
-- index, row compressed, and columnstore compressed).

-- Then it populates them with 1,000,000 rows, measures
-- space used, and drops them. You can run it over and
-- over, leaving no trace, unless you already have a 
-- table named dbo.[does not exist] (you won't anymore).

-- I placed the resulting output script in a comment below,
-- but I find the generation more fun (and easier to perfect).

-- If you want to change any aspects (like using datetime2(3)),
-- you can do that here rather than search/replace in the
-- output, which is tedious and error-prone.

DECLARE @cr nchar(2) = nchar(13) + nchar(10);

DECLARE @dt  nvarchar(max) = N'DROP TABLE IF EXISTS dbo.[does not exist]',
        @ct  nvarchar(max) = @cr, -- create table
        @pt  nvarchar(max) = @cr, -- populate table
        @su  nvarchar(max) = @cr, -- run sys.sp_spaceused
        @sql nvarchar(max);       -- assemble parts

;WITH data_types AS (
  SELECT r,n,t FROM (VALUES
    (1, 'Datetime2s_7',      'datetime2(7)'),
    (2, 'Datetime2s_0',      'datetime2(0)'),
    (3, 'Datetimes',         'datetime'),
    (4, 'Smalldatetimes',    'smalldatetime'),
    (5, 'Dates',             'date'),
    (6, 'DatetimeOffsets_7', 'datetimeoffset(7)'),
    (7, 'DatetimeOffsets_0', 'datetimeoffset(0)'),
    (8, 'Times_7',           'time(7)'),
    (9, 'Times_0',           'time(0)')
  ) AS dt(r,n,t)),
storage_types AS ( 
  SELECT r,n,tsql FROM (VALUES
    (1, 'regular',     '(d));'),
    (2, 'row',         '(d)) WITH (DATA_COMPRESSION = ROW);'),
    (3, 'columnstore', ' COLUMNSTORE);')
  ) AS st(r,n,tsql)) 
SELECT @dt += N', ' + @cr + N'  dbo.' + dt.n + N'_' + st.n, 
       @ct += N'CREATE TABLE dbo.' + dt.n + N'_' + st.n
              + N'(d ' + dt.t + N', INDEX cix CLUSTERED' + st.tsql + @cr,
       @pt += @cr + CASE WHEN st.r = 1 AND dt.r = 1 THEN
              N';WITH x AS (SELECT n = 1 UNION ALL SELECT n+1 FROM x WHERE n < 1000000)
                 INSERT dbo.Datetime2s_7_regular(d) SELECT DATEADD(SECOND, n, sysutcdatetime()) 
                 FROM x OPTION (MAXRECURSION 0);' + @cr + N'ALTER TABLE dbo.Datetime2s_7_regular REBUILD;'
           ELSE N'INSERT dbo.' + dt.n + N'_' + st.n + N'(d) SELECT d FROM dbo.Datetime2s_7_regular;' END,
       @su += N'EXEC sys.sp_spaceused N''dbo.' + dt.n + N'_' + st.n + N''';' + @cr
FROM data_types AS dt CROSS JOIN storage_types AS st
ORDER BY st.r, dt.r;

SET @sql = N'SET NOCOUNT ON;' + @cr + @dt + ';' + @cr + @ct + @cr + @pt + @cr + @su + @cr + @dt;

EXEC sys.sp_executesql @sql;

/* resulting script

SET NOCOUNT ON;

DROP TABLE IF EXISTS dbo.[does not exist], 
  dbo.Datetime2s_7_regular, 
  dbo.Datetime2s_0_regular, 
  dbo.Datetimes_regular, 
  dbo.Smalldatetimes_regular, 
  dbo.Dates_regular, 
  dbo.DatetimeOffsets_7_regular, 
  dbo.DatetimeOffsets_0_regular, 
  dbo.Times_7_regular, 
  dbo.Times_0_regular, 
  dbo.Datetime2s_7_row, 
  dbo.Datetime2s_0_row, 
  dbo.Datetimes_row, 
  dbo.Smalldatetimes_row, 
  dbo.Dates_row, 
  dbo.DatetimeOffsets_7_row, 
  dbo.DatetimeOffsets_0_row, 
  dbo.Times_7_row, 
  dbo.Times_0_row, 
  dbo.Datetime2s_7_columnstore, 
  dbo.Datetime2s_0_columnstore, 
  dbo.Datetimes_columnstore, 
  dbo.Smalldatetimes_columnstore, 
  dbo.Dates_columnstore, 
  dbo.DatetimeOffsets_7_columnstore, 
  dbo.DatetimeOffsets_0_columnstore, 
  dbo.Times_7_columnstore, 
  dbo.Times_0_columnstore;

CREATE TABLE dbo.Datetime2s_7_regular(d datetime2(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetime2s_0_regular(d datetime2(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetimes_regular(d datetime, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Smalldatetimes_regular(d smalldatetime, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Dates_regular(d date, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.DatetimeOffsets_7_regular(d datetimeoffset(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.DatetimeOffsets_0_regular(d datetimeoffset(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Times_7_regular(d time(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Times_0_regular(d time(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetime2s_7_row(d datetime2(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetime2s_0_row(d datetime2(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetimes_row(d datetime, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Smalldatetimes_row(d smalldatetime, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Dates_row(d date, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.DatetimeOffsets_7_row(d datetimeoffset(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.DatetimeOffsets_0_row(d datetimeoffset(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Times_7_row(d time(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Times_0_row(d time(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetime2s_7_columnstore(d datetime2(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Datetime2s_0_columnstore(d datetime2(0), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Datetimes_columnstore(d datetime, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Smalldatetimes_columnstore(d smalldatetime, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Dates_columnstore(d date, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.DatetimeOffsets_7_columnstore(d datetimeoffset(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.DatetimeOffsets_0_columnstore(d datetimeoffset(0), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Times_7_columnstore(d time(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Times_0_columnstore(d time(0), INDEX cix CLUSTERED COLUMNSTORE);

;WITH x AS (SELECT n = 1 UNION ALL SELECT n+1 FROM x WHERE n < 1000000)
                 INSERT dbo.Datetime2s_7_regular(d) SELECT DATEADD(SECOND, n, sysutcdatetime()) 
                 FROM x OPTION (MAXRECURSION 0);
ALTER TABLE dbo.Datetime2s_7_regular REBUILD;
INSERT dbo.Datetime2s_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;

EXEC sys.sp_spaceused N'dbo.Datetime2s_7_regular';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_regular';
EXEC sys.sp_spaceused N'dbo.Datetimes_regular';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_regular';
EXEC sys.sp_spaceused N'dbo.Dates_regular';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_regular';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_regular';
EXEC sys.sp_spaceused N'dbo.Times_7_regular';
EXEC sys.sp_spaceused N'dbo.Times_0_regular';
EXEC sys.sp_spaceused N'dbo.Datetime2s_7_row';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_row';
EXEC sys.sp_spaceused N'dbo.Datetimes_row';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_row';
EXEC sys.sp_spaceused N'dbo.Dates_row';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_row';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_row';
EXEC sys.sp_spaceused N'dbo.Times_7_row';
EXEC sys.sp_spaceused N'dbo.Times_0_row';
EXEC sys.sp_spaceused N'dbo.Datetime2s_7_columnstore';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_columnstore';
EXEC sys.sp_spaceused N'dbo.Datetimes_columnstore';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_columnstore';
EXEC sys.sp_spaceused N'dbo.Dates_columnstore';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_columnstore';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_columnstore';
EXEC sys.sp_spaceused N'dbo.Times_7_columnstore';
EXEC sys.sp_spaceused N'dbo.Times_0_columnstore';

DROP TABLE IF EXISTS dbo.[does not exist], 
  dbo.Datetime2s_7_regular, 
  dbo.Datetime2s_0_regular, 
  dbo.Datetimes_regular, 
  dbo.Smalldatetimes_regular, 
  dbo.Dates_regular, 
  dbo.DatetimeOffsets_7_regular, 
  dbo.DatetimeOffsets_0_regular, 
  dbo.Times_7_regular, 
  dbo.Times_0_regular, 
  dbo.Datetime2s_7_row, 
  dbo.Datetime2s_0_row, 
  dbo.Datetimes_row, 
  dbo.Smalldatetimes_row, 
  dbo.Dates_row, 
  dbo.DatetimeOffsets_7_row, 
  dbo.DatetimeOffsets_0_row, 
  dbo.Times_7_row, 
  dbo.Times_0_row, 
  dbo.Datetime2s_7_columnstore, 
  dbo.Datetime2s_0_columnstore, 
  dbo.Datetimes_columnstore, 
  dbo.Smalldatetimes_columnstore, 
  dbo.Dates_columnstore, 
  dbo.DatetimeOffsets_7_columnstore, 
  dbo.DatetimeOffsets_0_columnstore, 
  dbo.Times_7_columnstore, 
  dbo.Times_0_columnstore;
*/