The SQL Server 2025 vector data type, and how it affects web search

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The SQL Server 2025 vector data type, and how it affects web search
Microsoft have announced the preview availability of SQL Server 2025, complete with the new vector field. This blog explains what a vector field is, and why you might need it.

Posted by Andy Brown on 11 December 2024

In this blog

A brief digression - old-fashioned web search
How the brave new world of AI search will work

On November 19th 2024 Microsoft announced SQL Server 2025 - you can now apply to get a pre-release evaluation copy. This blog explains what the new vector field in SQL Server 2025 is for, using the example of our Wise Owl website search facility:

It's about to get much easier to get a web search like this to give good results.

A brief digression - old-fashioned web search

At the moment our site (and presumably many other sites like it?) relies upon the SQL FreeTextTable function. Here is the query which powers our search:

SELECT

smt.SitePageGroupId,

sp.Summary,

sp.InternalUrl,

SearchResults.Rank

FROM

tblSitePage As sp

INNER JOIN FreeTextTable(tblSitePage, SearchText, @SearchString) AS SearchResults

ON sp.SitePageId = SearchResults.[KEY]

INNER JOIN tblSitePageType AS smt

ON smt.SitePageTypeId = sp.SitePageTypeId

ORDER BY

[Rank] DESC

The FreeTextTable function in the query above has these arguments:

Argument	What it contains
tblSitePage	The name of a table in our database that has been marked for full-text querying
SearchText	An indexed column within this table (this column contains all of the text for each page of our website)
@SearchString	A parameter passed into the stored procedure giving the string to be searched for

Here are the first few lines the stored procedure containing the above query returns, using the example search text Advanced SQL training stored procedures:

These are reasonable results, but we can leverage AI search to make them better (more relevant).

How the brave new world of AI search will work

The above method is good insofar as it goes, but it doesn't allow you to use your website's contents as an AI-style large language model. To do this you need to store your website content as vectors of numbers. To understand what a vector is, consider how an AI tool would view our search string Advanced SQL training stored procedures:

You can us the OpenAI API Tokenizer to see how ChatGPT would view any passage of text as a vector of numbers.

So in our case, to get better results we want to search for the vector of numbers [45855, 13033, 6151, 16240, 19586] against a much larger vector giving a numeric version of the contents of each page of our website. You could store both the search vector and the webpage contents in the new vector data type:

column_name VECTOR( {} ) [NOT NULL | NULL]

Vectors are stored in an optimized binary format. Each element of the vector is stored as a single-precision (4-byte) floating-point value. You can see much more on vector data types at this Microsoft site.

So a future version of our search will consist of comparing two vectors against each other: a small vector of numbers representing what the user is searching for, and a (much) longer vector giving for each page of our website a numerical representation of the text it contains.

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

A brief digression - old-fashioned web search

How the brave new world of AI search will work

Head office

London

Manchester