Get rid of Tags table in SQL Server Memory

[marcominerva]

Currently, SqlMemoryDb saves the tags as JSON in the KMMemories table, then it denormalizes each value in the KMMemoriesTags_{index} table.

Instead of this approach, we could just use the KMMemories table, leveraging the native JSON support. For example, if we want to filter based on the value of a UserId tag, the query will be like the following, without the need to use the Tags table:

SELECT *
FROM KMMemories
CROSS APPLY OPENJSON(Tags, '$.userId') 
WHERE value = '42';

JSON support is available in SQL Server 2016 and later (and of course in Azure SQL Database), so I think that nowadays it shouldn't be a problem (the previous 2014 version has already ended the Mainstream support in 2019 and the Extended support will end this year).

[marcominerva]

@dluc any tought on this?

[dluc]

If we change the schema to store tags as a JSON field, I think we should index the list of tags inside, and compare performance with the previous schema.

About JSON indexing https://learn.microsoft.com/sql/relational-databases/json/index-json-data

[marcominerva]

At this moment, no index is defined on the Tags table, so the missing index could be an issue also with the current schema. My big concern is about the growing size of the table. If you agree, I can prepare some benchmarks and share the SQL script with you.

What do you think about?