dbo.digits() - a sample In-line TVF using CTE

I found a question in one of the online forums where a user wanted to generate digits, like for an counter, but control how many digits and a range of allowed digits.

ex. I want 2 digits, and only the digits between 3 and 5 giving me a result like this.

digits
  33
  34
  35
  43
  44
  45
  53
  54
  55

I came up with this solution...

CREATE FUNCTION dbo.digits(
@len int,
@min int,
@max int
)
RETURNS TABLE
AS
RETURN
WITH
digits (digit) AS (
SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL
SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL
SELECT '9'
),
selection AS (
SELECT
digit
FROM digits
WHERE
CAST(digit AS int) BETWEEN @min AND @max
),
result AS (
SELECT
0 AS position,
CAST(digit as varchar(max)) digit
FROM selection
UNION ALL
SELECT
r.position + 1,
CONCAT(s.digit,r.digit)
FROM result r
CROSS JOIN selection s
WHERE r.position < @len
)
SELECT
digit AS digits
FROM result
WHERE position = @len - 1

You then execute the TVF with this line:
SELECT * FROM dbo.digits(2, 3, 5) ORDER BY 1

It's about time...

It's been a while since last post, and it is not as frequent as I wish, but it's about time or more lack of time.

First I want to celebrate Microsoft SQL Server for yet another amazing release. Now it is Microsoft SQL Server 2019, there are so many new features I guess I must start writing some about everything I love in this product. And that is almost a promise, I'll try to do more posts, and feature all this goodness.

Now over to todays topic, time. I found a question on the SQL Server 2008 - 2019 Facebook group, https://www.facebook.com/groups/7193059737/10157742825254738. And it was from Islam Selim:

Hi all
I need to create time column in view calculate differeance between two other datetime columns.. Plz help

And my response was this:

CREATE FUNCTION dbo.fn_TimeBetween (
 @start datetime,
 @end datetime,
 @days int  = 0 -- 0 for 00H,00M,00S (Default), 1 for 00D,00H,00M,00S
 )
RETURNS varchar(max)
AS
BEGIN
DECLARE
 @distance int = DATEDIFF(second, @start, @end),
 @result varchar(max);
DECLARE
 @seconds int = @distance % 60
SET
 @distance = (@distance - @seconds) / 60
DECLARE
 @minutes int = @distance % 60
SET
 @distance = (@distance - @minutes) / 60
DECLARE
 @hours int = @distance % 24
SET
 @distance = (@distance - @hours) / 24
IF @days = 0
 SET @result = CAST(@distance * 24 + @hours AS varchar(max)) + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    +'S'
ELSE
 SET @result = CAST(@distance AS varchar(max))     + 'D,' +
 RIGHT('0' +  CAST(@hours AS varchar(max)), 2)    + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'S'
RETURN @result
END

Skriptet

USE minaAdresser;
GO
CREATE SCHEMA Tabeller;
GO
CREATE SCHEMA Adresser;
GO
CREATE TABLE Tabeller.Namn
 (
  NamnID int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Tabeller_Namn
   PRIMARY KEY CLUSTERED,
  Namn nvarchar(255) NOT NULL
   CONSTRAINT uq_Tabeller_Namn_Namn
   UNIQUE
 )
CREATE TABLE Tabeller.Adresser_Adress
 (
  AdressID  int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Adresser_Adress
   PRIMARY KEY CLUSTERED,
  Förnamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Förnamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Efternamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Efternamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gata int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Gata
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gatunummer nchar(15) NOT NULL
   CONSTRAINT df_Adresser_Adress_Gatunummer
   DEFAULT (''),
  Postnummer char(6) NOT NULL
   CONSTRAINT ck_Adresser_Adress_Gatunummer
   CHECK (Postnummer LIKE '[1-9][0-9][0-9] [0-9][0-9]'),
  Ort int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Ort
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID)
 )
GO
CREATE VIEW Adresser.Adress
AS
SELECT
 a.AdressID,
 fn.Namn AS Förnamn,
 en.Namn AS Efternamn,
 g.Namn AS Gata,
 a.Gatunummer,
 a.Postnummer,
 o.Namn AS Ort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE VIEW Adresser.Utskrift
AS
SELECT
 a.AdressID,
 fn.Namn + ' ' + en.Namn    AS Mottagare,
 g.Namn + ' ' + a.Gatunummer   AS Postadress,
 a.Postnummer + ' ' + UPPER(o.Namn) AS Postort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE FUNCTION Adresser.hämtaNamn(@NamnID int)
RETURNS nvarchar(255)
AS
BEGIN
RETURN(SELECT Namn FROM Tabeller.Namn WHERE NamnID = @NamnID)
END
GO
CREATE FUNCTION Adresser.hämtaNamnID(@Namn nvarchar(255))
RETURNS int
AS
BEGIN
RETURN(SELECT NamnID FROM Tabeller.Namn WHERE Namn = @Namn)
END
GO
CREATE PROCEDURE Adresser.skapaNamn @Namn nvarchar(255), @NamnID int OUTPUT
AS
BEGIN
DECLARE @Retur int = 0
IF @Namn IS NULL
BEGIN
 SET @Namn = ''
 SET @Retur += 10
END
SET @NamnID = Adresser.hämtaNamnID(@Namn)
IF @NamnID IS NULL
BEGIN
 INSERT INTO Tabeller.Namn(Namn) VALUES(@Namn)
 SET @NamnID = SCOPE_IDENTITY()
 SET @Retur += 1
END
RETURN @Retur
END
GO
CREATE PROCEDURE Adresser.uppdateraAdress
 @Förnamn nvarchar(255),
 @Efternamn nvarchar(255),
 @Gata nvarchar(255),
 @Gatunummer nvarchar(255),
 @Postnummer nvarchar(255),
 @Ort nvarchar(255),
 @AdressID int OUTPUT,
 @updateAdressID int = -1
AS
BEGIN
DECLARE
 @Retur int = 0,
 @Exec int = 0,
 @FörnamnID int,
 @EfternamnID int,
 @GataID int,
 @OrtID int
IF @Postnummer NOT LIKE '[1-9][0-9][0-9] [0-9][0-9]' SET @Retur = -3
ELSE IF @Gatunummer IS NULL SET @Retur = -2
ELSE
BEGIN
EXEC @Exec = Adresser.skapaNamn @Förnamn, @FörnamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Efternamn, @EfternamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 1000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Gata, @GataID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 100000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Ort, @OrtID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10000000
 SET @Exec = 0
END
END
IF @Retur >= 0
BEGIN
IF @updateAdressID = -1
BEGIN
SELECT @AdressID = AdressID FROM Tabeller.Adresser_Adress
WHERE
 Förnamn = @FörnamnID
AND Efternamn = @EfternamnID
AND Gata = @GataID
AND Gatunummer = @Gatunummer
AND Postnummer = @Postnummer
AND Ort = @OrtID
IF @AdressID IS NULL
BEGIN
INSERT INTO Tabeller.Adresser_Adress(Förnamn, Efternamn, Gata, Gatunummer, Postnummer, Ort)
VALUES(@FörnamnID, @EfternamnID, @GataID, @Gatunummer, @Postnummer, @OrtID)
SET @AdressID = SCOPE_IDENTITY()
SET @Retur += 1
END
END
ELSE
BEGIN
DECLARE @updated TABLE (AdressID int)
UPDATE Tabeller.Adresser_Adress
SET
 Förnamn = @FörnamnID,
 Efternamn = @EfternamnID,
 Gata = @GataID,
 Gatunummer = @Gatunummer,
 Postnummer = @Postnummer,
 Ort = @OrtID
OUTPUT inserted.AdressID INTO @updated
WHERE AdressID = @updateAdressID
SELECT @AdressID = AdressID FROM @updated WHERE AdressID = @updateAdressID
SET @Retur += 2
END
END
RETURN @Retur
END
GO
USE master

Ok, just a sample PIVOT

DECLARE @DatabaseObjects TABLE
  (
    DatabaseName sysname,
    ObjectType sysname,
    ObjectName sysname
  );
DECLARE
  @DatabaseName sysname,
  @SQLStatement varchar(max),
  @ExecuteStatement varchar(max),
  @Columns varchar(max);

SET NOCOUNT ON;
SET @SQLStatement = 'SELECT ''{DatabaseName}'' AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS [ObjectName] FROM [{DatabaseName}].[sys].[objects];';
DECLARE
  DatabaseCrawler CURSOR
  FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ExecuteStatement = REPLACE(@SQLStatement, '{DatabaseName}', @DatabaseName);
  INSERT INTO @DatabaseObjects
  EXEC (@ExecuteStatement);
  FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE DatabaseCrawler;
SET NOCOUNT OFF;

SELECT * INTO #DatabaseObjects FROM @DatabaseObjects

SELECT @Columns = ISNULL(@Columns, '') + '[' + [Column] + '],' FROM (SELECT DISTINCT ObjectType AS [Column] FROM @DatabaseObjects) Columns

SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SET @SQLStatement = 'SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt'

SET @ExecuteStatement = REPLACE(@SQLStatement, '{Columns}', @Columns);

EXEC (@ExecuteStatement)
DROP TABLE #DatabaseObjects

By request

DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates

If you don't like your query plan!

In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key or you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.

I'd like to end this post with a lovely quote:

I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax. -Conor Cunningham

Data Modelling, why is it important to understand logical and physical...

Coming from a 200K+ IT Consultants organisation, and at least 25 years of solving the "performance issues" after "stuff went into production" I'd say it depends, but usually the Projects lacked a Data(base) Architect during the architectural design, early design/implementation phase.

I usually say it's a database, not an objectbase. When we model data for a solution we do it in multiple layers. Usually it starts with business objects like Customers, Suppliers, Products, Orders, and then it comes down to Classes, that need "storage" by an Entity Model (Yes, any ORM would go but as long EF is without "Core" I'm happy). This logical data model need physical support, thus a database. Basically every logical data model object need a storage object, at the lowest level a Table, which gives Us a one to one relation between Entity and Table.
And this is how it usually ends, I'm not saying this is right. I'm only saying this is where it usually ends up.

Back to my saying this is a database, not an objectbase. Databases have data storage objects (Tables), and data access objects (Procedures, Views, I'm leaving out the functions here). The application want to have something to feed the Entities from the Tables, this is where we use views as proxies between the logical and physical model as we probably want to model the data (This is what most people would call normalize data, I use the terms of normalize, generalize, and denormalize and care less about what xNF we get. My goal is the most optimal physical datamodel for performance, data integrity, size.) to support the R in CRUD(Views for -R--). And then add Procedures for the C-UD in CRUD. Basic rule is one table and Three procedures per Entity, let us call the View by the EntityName, and the procedures ins-, upd, and delEntityName, and then add them as methods for the entities(EF supports this, EFCore doesn't!). And then add instead of triggers on the views launching the procedures for the developer not using the entity model.

So what is the problem of this approach, developers cannot change entity models without understanding what would happen with the database objects. So, if they after a release want to add attributes to an entity they need to change a table to add a column, change the view to handle the column, change three procedures, and then change the triggers. This would suck big time... To mitigate that problem the developer can create a supporting class for the minor release, think patch. In practical terms, create a new class with the keys, and additional attributes, getting a table for that, running CRUD against that table. And then, when the Product reaching a Major release merge together. But with this in mind, how ofter does a datamodel actually change after the release? I'd say not often, small changes, really seldom...

And finally, what are the benefits. First that people gets in mind is performance (I always get Premature Optimization n my face every time I bring this up.), and we get data integrity. Both in terms of data security, and data quality. With security we can make sure that only the right user can reach the right data, all the way down on row/column level, actually all the way down to every single data point we might have. But we also get higher quality in the data as we have a chance of applying a "higher level of normalization" as we can normalize, generalize, and denormalize data with the focus on data, not business objects.

As an example, take a Contacts table containing whatever needed for sending postcards, covering everyone in the World. We would need attributes like this: ContactID, FirstName, MiddleName, LastName, Address, Zipcode, City, County, State, Country, Continent. See this as a conceptual model, it doesn't need to be perfect...

Let us estimate a logical row to something between 20 and 500 bytes depending on the strings, and let us say that the average string is 9 characters, and we go by unicode giving us an average of 18 bytes per string, and that we have 10 strings and one key which need to support at least 8.000.000.000 individuals and 2.000.000.000 companies, that is 10.000.000.000 rows in the table. SQL Server has a storage level called data page that limits how many rows can be fetched through an IO operation. A data page is 8kB, and can store a single physical row up to 8060 B.

To calculate the rows per page we simply divide 8060 bytes per page / bytes per row giving us rows per page. To get the size we divide rows per page with rows per table giving us the result in pages per table. And as every page is 8 kB we just multiply with 8 and get kB per table.

Legend:

B = Bytes, kB = Kilobytes, r = Row, p = Page, t = Table, r = Rows, Br = Bytes per Row, Bp = Bytes per Page, kBp = Kilobytes per Page, rp = Rows per Page, pt = Pages per Table, rt = Rows per table, Bt = Bytes per Table, kBt = Kilobytes per Table

Formulas:

Bp / Br = rp (Round down)

rt / rp = pt (Round up)

pt * kBp = kBt

10.000.000.000 rows per table and 20 Bytes per row:

403 Rows per page, 24.813.896 pages per table, 193.859 MB in the table

10.000.000.000 rows per table and 100 Bytes per row:

80 Rows per page, 125.000.000 pages per table, 976.563 MB in the table

10.000.000.000 rows per table and 500 Bytes per row:

16 Rows per page, 625.000.000 pages per table, 4.882.813 MB in the table

Breaking this table up in a Contacts, Streets, Cities, Regions

Regions(RegionID, State, Country, Region)

Cities(CityID, City, County, RegionID)

Streets(StreetID, Street, Zipcode, CityID)

Contacts(ContactID, Names, StreetID, StreetNumber)

Makes this probably down to 60% in size.

This is normalization!

There is a lot of strings, create a string table and assign StringIDs to the strings in the entity tables. Let us do some generalization! I add some rough estimates

Strings(StringID, String), 5.000.000 unique strings, avg row 20 B, total of 97 MB

Regions(RegionID, StateStringID, CountryStringID, RegionStringID), 10.000 states, always 20 B per row, total of 200 kB

Cities(CityID, CityStringID, CountyStringID, RegionID), 250.000 cities, always 20 B per row, total of 5 MB

Streets(StreetID, StreetStringID, Zipcode, CityID), 25.000.000 streets, always 25 B per row, total of 607 MB

Contacts(ContactID, FirstNameStringID, MiddleNameStringID, LastNameStringID, StreetID, StreetNumber), 10.000.000.000 contacts, always 38 B per row, total of 368.514 MB

Adding this together gives us a model of 368 GB. Compare 976 GB to 368 GB is a new model that is less than 38 % of the original model.

And then we have a really meshy model, so let us denormalize some…

Strings(StringID, String), this is the same as before, 97 MB.

Addresses(StreetID, StreetStringID, Zipcode, CityStringID, CountyStringID, StateStringID, CountryStringID, RegionStringID), always 42 B per row, total of 1023 MB.

Contacts(ContactID, FirstNameStringID, MiddleNameStringID, LastNameStringID, StreetID, StreetNumber), same as before, 368.514 MB

This final model is probably somewhere in the 35-40% range of the original model giving us approximatly 3 times so many business objects, in the same amount of RAM in cache on the SQL Server. And we will have a more slow, linear growth in in the database when we add content instead of an exponential growth.

And then we have the views and procedures making the model work from the business layer.

Long reply, adding to your story. Hoping to get the developers to understand the importance of a difference between an entity and/or class in the business layer, and a table in the database. There is no relation at all in between, as the business layer works with business objects, and in the database we work with data.

Some In Memory OLTP analytical scripts

I will add descriptions about this in time.

Please use this as an inspiration for your own In Memory OLTP analysis.

CREATE DATABASE InMemDemos
ON PRIMARY (NAME=InMemDemos_Data, FILENAME='D:\Data\InMemDemos_Data.mdf', SIZE=10MB, FILEGROWTH=10MB),
FILEGROUP InMemDemos_FG CONTAINS MEMORY_OPTIMIZED_DATA (NAME=InMemDemos_FG, FILENAME='D:\Data\InMemDemos_FG')
LOG ON (NAME=InMemDemos_Log, FILENAME='D:\Data\InMemDemos_Log.ldf', SIZE=10MB, FILEGROWTH=10MB)

First part creates the database, Second part creates three tables:

USE InMemDemos
GO
CREATE TABLE InMem_BC100
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)
CREATE TABLE InMem_BC1000000
(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
Data CHAR(100) COLLATE Finnish_Swedish_BIN2 NOT NULL
)
WITH
(
MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA
)

Let us populate the tables :

WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC100(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000
GO
WITH
a2(Col) AS (SELECT NULL UNION ALL SELECT NULL),
a4(Col) AS (SELECT NULL FROM a2 x CROSS JOIN a2 y),
a16(Col) AS (SELECT NULL FROM a4 x CROSS JOIN a4 y),
a256(Col) AS (SELECT NULL FROM a16 x CROSS JOIN a16 y),
a64k(Col) AS (SELECT NULL FROM a256 x CROSS JOIN a256 y),
a16m(Col) AS (SELECT NULL FROM a64k x CROSS JOIN a64k y),
tally(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM a16m)
INSERT INTO InMem_BC1000000(Data)
SELECT CAST(num as VARCHAR(100)) + REPLICATE('_', 100-(LEN(CAST(num as VARCHAR(100))) * 2)) + CAST(num as VARCHAR(100)) FROM tally WHERE num <= 500000

How about some checkpoint statistics:

SELECT * FROM sys.dm_db_xtp_checkpoint_stats

 And status about the Data and Delta files:

SELECT
da.container_guid,
da.relative_file_path AS Data_File,
de.relative_file_path AS Delta_File,
da.internal_storage_slot,
da.state_desc,
da.file_size_in_bytes AS Data_File_Size,
da.file_size_used_in_bytes AS Data_File_Size_Used,
de.file_size_in_bytes AS Delta_File_Size,
de.file_size_used_in_bytes AS Delta_File_Size_Used,
da.inserted_row_count,
de.deleted_row_count
FROM sys.dm_db_xtp_checkpoint_files da
INNER JOIN sys.dm_db_xtp_checkpoint_files de
ON da.checkpoint_file_id = de.checkpoint_pair_file_id
WHERE da.file_type_desc = 'DATA' AND de.file_type_desc = 'DELTA'

And continue with some Table statistics:

SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
xtms.memory_allocated_for_table_kb,
xtms.memory_used_by_table_kb,
xtms.memory_allocated_for_indexes_kb,
xtms.memory_used_by_indexes_kb
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.dm_db_xtp_table_memory_stats xtms
ON t.object_id = xtms.object_id

 All the way to specific indexes:

SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS Table_Name,
i.name AS Index_Name,
i.type_desc AS Index_Type,
xhis.total_bucket_count,
xhis.empty_bucket_count,
xhis.avg_chain_length,
xhis.max_chain_length,
xmc.allocated_bytes,
xmc.used_bytes,
xis.*
FROM sys.schemas s
INNER JOIN sys.tables T
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
INNER JOIN sys.dm_db_xtp_index_stats xis
ON t.object_id = xis.object_id AND i.index_id = xis.index_id
INNER JOIN sys.dm_db_xtp_hash_index_stats xhis
ON xis.object_id = xhis.object_id AND xis.index_id = xhis.index_id
INNER JOIN sys.dm_db_xtp_memory_consumers xmc
ON xis.object_id = xmc.object_id AND xis.index_id = xmc.index_id

Remeber to throw some checkpoint from time to time:

CHECKPOINT

 Ok, I'm done now and I hope you can make use of the queries I shared here.