Bit by Bit - SQL Client Throws Saving Float[] Into Vector - SOLVED

 Surprise Solution Populating Vectors

The Issue

I have been working for several hours on a RAG system for AI and SQL Server now has Vector datatype for RAG and semantic temporal data solutions. SQL 2025 officially introduces this feature with a few wrinkles. There are a few database level switches that need to be enabled first.

ALTER DATABASE YourDb SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

This next oddity (for the lack of better term) If you use VS and Object or SQL Server explorer, It will sometimes reset the dimensions on a configured Vector column. I didn't test for the exact conditions, patience was thin. If you try to use queries in VS2026 involving the vector columns you will get errors that will not let you proceed. This was in VS2026 Insiders build. It has to do with the compatibility level and preview features.

       I was able to successfully work with the tables in SSMS 22.1+.  The next hurdle was trying to populate the RAG db. specifically the Vector column. SqlClient was throwing complaining there was no mapping from single to a native type or a more bizarre exception that I was trying to pass invalid Json into the Vector column. Which I wasn't trying to.

       At 4:30 in the morning I am trying to tackle this situation, I have 2 AI's that were stumped also, Windows copilot on one screen and copilot in VS neither one solved it cleanly, I was packing the floats into bytes and passing them as binary.

Solution

        After scratching our collective heads for a while I opened SSMS and asked the AI in that app to confirm the solution, it's still GitHub copilot but it is focused on SQL. It was long shot but at nearly 5 am a longshot is welcome. Drum roll please... The current official path for populating Vector columns with floats[] delivered from embedding models is to pass them in as a Json string.! Yes string, nvarchar or nchar, SQL server translates this string into the floats that the column is expecting.

  ```using (var cmd = new SqlCommand("INSERT INTO dbo.embeddings (id, vector_col) VALUES (@id, @vec)", conn))

{

    cmd.Parameters.AddWithValue("@id", 1);

    

    // Pass vector as JSON string

    float[] embedding = { 0.1f, 0.2f, 0.3f };

    string vectorJson = $"[{string.Join(",", embedding)}]";

    

    cmd.Parameters.Add("@vec", SqlDbType.NVarChar).Value = vectorJson;

    cmd.ExecuteNonQuery();

}```

 

         Why Not Binary?

• SqlDbType.Vector may not be available in current client libraries (preview feature)

• Binary conversion requires knowledge of internal format (8-byte header + float data)

• String/JSON approach is officially supported and simpler

Confirmation

No, you don't need to convert to binary. Use string parameters with JSON array format - this is the intended client interaction model for VECTOR columns.