Is Vector Search in SQL Server 2025 the Key to Unlocking New Data Insights?

 The advent of AI has ushered in ground breaking changes in most areas of technology. AI is synonymous with a humongous amount of data, data of all types including text, image, audio and video. The databases of the future needs to cater to this changing vista of data and yet compatible with existing technologies.

Microsoft SQL Server has always catered to the technological changes and along its developmental path has introduced many data types to handle diverse data varieties and in recent years has introduced data types such as, "Json', "Stock" and "Geographical" data types. These address  the stock market and the geographical data that accompanies all global operations. 

In addition to data types, it has also changed itself to accommodate not only relational data but also non-relational NoSQL type of data using integration with polyphase technology.

Vector Data Type

SQL Server 2025 has introduced a new **vector data type** designed to store vector data optimized for operations like similarity search and machine learning applications. This is quite a leap for a relational database management system (RDBMS), as it traditionally handles structured data but can handle NoSQL Data.

How is Vector datatype stored in the SQL Server 2025?

A vector in SQL Server is typically stored as a JSON array of floating-point values. Here's an example of how you might define and use a vector in SQL Server:

-- Create a table with a vector column

CREATE TABLE dbo.vectors (

    id INT PRIMARY KEY,

    v VECTOR(3) NOT NULL

);

-- Insert data into the table

INSERT INTO dbo.vectors (id, v) VALUES 

(1, '[0.11, 2, -35]'), 

(2, '[-100.2, 0.0123, 9.876]'

-- Select data from the table

SELECT * FROM dbo.vectors;

This table with two columns has a column of vector data type not null. The vectors are [0.11, 2, -35] and [-100.2, 0.0123, 9.876]

The columns store three dimensional vectors with each dimension stored as a single-precision floating point (float type).

Vector Data Type is not available in 2022

The vector data type is not available in SQL Server 2022. You may have to store vector data type in a JSON column or something customized.

Image Processing and AI

In recent times image processing has been intensely studied. One may ask how to store the dimensional representation of an image. Well, images can have a very high dimensional representation.

• Grayscale Image: A 100x100 pixel grayscale image can be represented as a 10,000-dimensional vector (100x100 = 10,000).

• Color Image: A 100x100 pixel color image with RGB channels can be represented as a 30,000-dimensional vector (100x100x3 = 30,000).

In SQL Server 2025, the maximum number of dimensions that can be stored using the vector data type is 1998. Each element of the vector is stored as a single-precision (4-byte) floating-point value.

Can image dimensions be stored in SQL Server 2025?

The short answer for storing image dimensions in SQL Server is that it is not possible to store image dimensions without reducing the number of dimensions. There are techniques to reduce the dimensions with various pay offs in quality. We shall discuss them in a later post using a programming language such as python with the use of certain libraries.

Going forward in populating vector data type into the server the SQL Server Integration services can be used to preprocess the image data and then populate the database. This will also be for a future post.

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.  http://hodentekMSSS.blogspot.com

You may also have a look at my database related books:

Does Google's Gemini work with SQL Server without any other downloads?

There are two ways it works. One is commercial, third party and the other is Google's approach.

1. Commercial product: It is called Countersoft Gemini that can be used with SQL Server on your local SQL Server installation. It offers must typical tasks such as performance analysis, change tracking and automating tasks. For using Countersoft, you should install it on your local server. It is not a web based service.

It is not free. There are two prices, $10 monthly per user in the cloud and $850 for 10 users.

Use this link here: https://www.countersoft.com/

2. Google's Gemini: This is the built-in AI assistant for DBMS on the Google Cloud Platform(GCP). It does performance, schema analysis, querying, etc. It is built-in and does not require anything special. However, for SQL Server, you need to have an instance installed in the GCP.

More here: 

https://gemini.google.com/app?utm_source=google&utm_medium=cpc&utm_campaign=2024enUS_gemfeb&gad_source=1&gclid=CjwKCAjw4f6zBhBVEiwATEHFVi_2XLUBnZCVss9YqGvsAFYIth7bzE2rn_4aC0aNY7Q3C4U3UcWa9RoCZe8QAvD_BwE

Unlocking Insights, Automating Decisions, and Transforming Businesses with SQL Server AI

 The world is datacentric and everything moves based on data. SQL Server is a database management system and takes care of every aspect of data, from second to second management by online transaction processing (OLTP) to mining stored, archived data that includes data that streams (OLAP).

Data and Data Science:

Raw data can be enormous and the only way to give meaning and take out value out is the role of analytics. The advanced analytics handlers are the data science professionals. They go through the mountain of data and distil out the most useful, and the most relevant information. The success of a company, or organization is dependent on these professionals. All of these example following use cases and many more not mentioned here may be cited: credit risk assessments; managing customer/employee churn; hospitalization metrices; targeted sales & campaigns, etc.

Data Science using SQL Server:

SQL Server is a very comprehensive, database management system well suited for everything related to data present anywhere from OLTP data to OLAP data. It can be present both on site and in the cloud and integrated with the AZURE cloud which in turn is integrated with a myriad of other data related applications not only from Microsoft but from many other applications. Doing data science with SQL Server results in high value and high returns.

Data scientists can connect to a myriad of databases that can be used to train data and test the machine learning tool at their hands.

Where does AI come in or fit in?

The data scientists access the data from the client's database and combine with data from other sources to develop models using R software for joining the data and filtering based on criteria. They may begin shaping the data by creating extra informational features like new columns, or useful data partitioned or transformed, etc. This data shaping lays the foundation for predictive analytics. Going a step further they can put into operation a plan for the model that applications can use for producing useful outputs in the form of a predictive model.

This just one scenario where data is taken out of database and worked upon to get at the useful information by creating models. How easy is it? Moving large amounts of data in and out of database comes at a cost. Thinking about cost and other contingent aspects like the location of data, the security of data (that was not mentioned so far), the latency involved if geographically separated sites are involved, and not having features of DBMS like indexing, column stores, high availability, etc. one can conclude that it may be prudent to do as much of the filtering and shaping done on the database using all the tools the DBMS can provide instead of working on raw data from a database.

As a result of considerations discussed previously it will be indeed beneficial if data science and AI on the existing database can be carried out prior to moving out data. This allows you to leverage all the inbuilt features of the DBMS previously discussed. If the data involves geographical data these can be handled inside SQL Server's inbuilt data types. If on the other hand data has to be accessed from other data sources outside of Microsoft, the linked source feature of the DBMS can be used. 

The following picture copied from a Microsoft site shows the two ways discussed so far.

Another important consideration is after doing predictive analytics the operationalization of what has been achieved with data science, namely the predictive model. The deployment of this to a production environment can be accomplished by using programming using SQL Server R services in the form of a stored procedure. The predictive model will be stored as varbinary (max) in a database table.

Look forward to more discussions related to SQL Server and AI.

R package for Machine Learning - caret

The most trending topics these days are Artificial Intelligence and Machine Learning. Every day you get tons of news about AI and ML. R programming language is well placed to initiate you into these 'esoteric' topics. There is a learning curve and should you learn to scale the peaks there will be rewards. So much of 'carrot' talk.

In R, the package that does a lot of work for you as far as AI and ML are concerned is the package 'caret'. You should load this package and use its library to begin your AI/ML related activities. Of course there are more roads to Rome than one.

If have been working with R, you can launch R and install the package. If you are using Microsoft R (R Server/ R Client), you can launch the R GUI.

Run the install statement ass shown:

> install.packages("caret")

Installing package into ‘C:/Users/Owner/Documents/R/win-library/3.3’

(as ‘lib’ is unspecified)

also installing the dependencies ‘minqa’, ‘nloptr’, ‘RcppEigen’, ‘lme4’, ‘SparseM’, ‘MatrixModels’, ‘pbkrtest’, ‘quantreg’, ‘car’

After contacting the site to download a number of packages are downloaded and checked as shown here:

package ‘minqa’ successfully unpacked and MD5 sums checked

package ‘nloptr’ successfully unpacked and MD5 sums checked

package ‘RcppEigen’ successfully unpacked and MD5 sums checked

package ‘lme4’ successfully unpacked and MD5 sums checked

package ‘SparseM’ successfully unpacked and MD5 sums checked

package ‘MatrixModels’ successfully unpacked and MD5 sums checked

package ‘pbkrtest’ successfully unpacked and MD5 sums checked

package ‘quantreg’ successfully unpacked and MD5 sums checked

package ‘car’ successfully unpacked and MD5 sums checked

package ‘caret’ successfully unpacked and MD5 sums checked

Note that the 'caret' library requires ggplot2 which will also be loaded, if you already have it.

Please look up other R related posts on this blog:

http://hodentekhelp.blogspot.com/search?q=ggplot

Let us come back and look at how it may be used.

As is, this post is not 100% SQL Server except that I used the Microsoft R for this post.