redb.MSSql - Minimum Sql Server Version supported

[manutiedra]

It would be nice that you specify in the documentation that the required version of SQL Server. I have SQL Server 2016 and it doesn't work (Microsoft.Data.SqlClient.SqlException: ''STRING_AGG' is not a recognized built-in function name.') because that is a feature that was introduced in SQL Server 2017.

[reliktbk]

You are right, and thank you for pinning it down. The error text alone would have cost the next person an afternoon.

The real floor is SQL Server 2017, and not because of one function. STRING_AGG appears 90 times across the provider, including the object-to-JSON path and the PVT aggregate, and TRIM (also 2017) another 52 times. Neither has a cheap 2016 fallback, so lowering the floor is not a small patch and we are not planning one.

Where the requirement is stated today: the repository README, "PostgreSQL 14+, Microsoft SQL Server 2019+". Where it is not stated, and should be: the redb.MSSql package README and its NuGet description, which is exactly where you were standing. That is our miss and we are fixing it there.

On the numbers: 2017 is the floor by feature, 2019 is the version we state because that is what we are willing to stand behind, and this is what we actually develop and test against:

FROM mcr.microsoft.com/mssql/server:2025-latest

USER root

# Install dependencies and add Microsoft repository
RUN apt-get update && \
    apt-get install -y curl apt-transport-https gnupg && \
    curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-preview.list -o /etc/apt/sources.list.d/mssql-server.list

# Install FTS package and clean up
RUN apt-get update && \
    ACCEPT_EULA=Y apt-get install -y mssql-server-fts && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

USER mssql

The Full-Text package in there is history, not a requirement. redb creates its catalog and index under IF SERVERPROPERTY('IsFullTextInstalled') = 1, so an instance without Full-Text skips them silently, and the index is being removed entirely: every string predicate compiles to LIKE, which never reads it. Do not copy that step unless you want it for your own reasons.

Worth knowing if you are starting a project now: a major 4 is in preparation. It brings lazy references (a virtual property stays a stub until you touch it, with LoadAsync(depth: 1) as the default), unique keys on both the object and Props fields, and a schema-upgrade contract for databases the application does not own. Several MSSQL-specific corrections ride along, including load depth that now matches PostgreSQL and SQLite. No date yet.

Pro stays free in 4, exactly as through the whole 3.x line: no license key, no license server, commercial production included. That is already in the code, not just the plan.

[manutiedra]

Thank you for the information. For new developments I will also use sql server 2025 via docker.

I will keep in mind the possibility to use red.Route.Llm in the future, but first I want to use only redb just for storage to internalize it better.

I will have to review the docs as I checked them before holidays, but I did a quick test adding a byte[] to the Product entity created by the template (public byte[] Info { get; set; } = [];) and it gives me this error:

Are byte arrays supported?

[reliktbk]

I checked this across the matrix rather than guessing, on the published 3.7.2, five bytes written and read back:

	SQLite	PostgreSQL	SQL Server
byte[] in Props, Free	throws	throws	throws, your case
byte[] in Props, Pro	5 bytes	5 bytes	5 bytes
value_bytes base field, Free	5 bytes	throws	silently NULL
value_bytes base field, Pro	5 bytes	5 bytes	5 bytes

Short answer: switch to redb.MSSql.Pro and it works today. Pro is free across the whole line, no key and no license server, commercial use included, so it costs you nothing but the package name.

Why Free fails: it materializes objects from a JSON projection, and that path has three separate byte defects. A byte[] property is classified as an array of Byte, so it arrives as a JSON array while System.Text.Json reads byte[] from base64, which is the exception you hit. On PostgreSQL the base field is emitted as bytea::text, hex rather than base64. On SQL Server the writer never emitted value_bytes at all, so it never reached the column, which is worse than an exception because nothing tells you. Pro reads the typed columns directly and meets none of that.

For binary data, prefer the object's own field over a byte[] property:

var doc = new RedbObject<Product> { Name = "manual.pdf", Props = new Product { ... } };
doc.value_bytes = File.ReadAllBytes(path);
await redb.SaveAsync(doc);

var loaded = await redb.LoadAsync<Product>(doc.Id);
var bytes = loaded.value_bytes;

I tested that shape with a 1 MB payload on SQL Server Pro 3.7.2: it comes back byte for byte identical, _objects._value_bytes holds the full 1048576 bytes, and it adds no rows to _values at all.

value_bytes is one of the object's root fields, a single VARBINARY(MAX) column on _objects, so it stays one row whatever the size. A byte[] Props property on 3.7.2 is still stored as one row per byte: your five bytes are six rows in _values, and a megabyte would be a million rows.

Two things to plan around with value_bytes. There is exactly one per object, so several payloads mean several objects. And it is a root field, read back on every load of that object, so a large blob is better placed on its own child object that you load only when you need it.

4.0 fixes the Props storage shape itself: a byte[] property becomes the scalar BLOB it is, one _values._ByteArray per property, and a database written with the old layout converts itself on the next schema sync.

[reliktbk]

A follow-up on the object's other base fields, since you are reading the schema anyway.

value_bytes is the payload slot. The rest of that row, ValueLong, ValueGuid, ValueDatetime, ValueNumeric and ValueString, is the filtering surface: external keys, grouping, anything you narrow a result set by. WhereRedb reads them straight off _objects, with no join to _values:

await redb.Query<Product>().WhereRedb(o => o.ValueString == "SKU-1001").ToListAsync();

One caveat that matters on SQL Server, and I read it off a schema created by the 3.7.2 packages rather than from our sources: _objects there carries partial indexes on _value_long, _value_guid, _value_datetime and _value_numeric, but none on _value_string, because the column is NVARCHAR(MAX) and cannot serve as an index key. PostgreSQL does have that index. So a ValueString filter works on SQL Server today, it just scans instead of seeking. If you want a fast external key before 4.0, ValueLong or ValueGuid is the indexed choice.

4.0 narrows the column to NVARCHAR(450), which keeps the index key legal, and adds the index. The limit is enforced in C# as well, so an over-long value gives you a clear exception instead of a silent truncation.

[manutiedra]

Thank you very much for your detailed explanations. It is helping me a lot!

[reliktbk]

see https://github.com/redbase-app/redb/tree/main/redb.Examples

[reliktbk]

Follow-up on both open items here.

The major has a date now: 4.0.0 was released on 11 September. It carries what this thread previewed: lazy references (a virtual property stays a stub until you touch it, LoadAsync(depth: 1) as the default), unique keys on both the object and Props fields, and the schema-upgrade contract for a database the application does not own. The MSSQL corrections rode along, including query-page load depth, which used to be 1 on SQL Server where PostgreSQL and SQLite used 10.

Pro is free on 4.x, no license key, no license server, commercial production included, exactly as stated above. The constant that decides it moved with the release rather than after it.

The documentation miss is fixed. The redb.MSSql package README and its NuGet description now state the floor where you were standing when you hit it: SQL Server 2017 or newer, with 2022 or newer required only for the $min and $max expression operators. The full-text index is gone from this release as well, so an instance without Full-Text has one less thing to think about.

The announcement, with the breaking changes and the upgrade notes: https://github.com/orgs/redbase-app/discussions/13