Introduction

There are several reasons to use a local AI model instead of ChatGPT, Gemini, or other online AI services:

• First, you are paranoic and you believe that the CIA is spying on you ???
• Also, there is confidential information in your company, and you do not trust this AI public service.
• You work in a place where the internet is not enabled due to security policies.
• The job is in the desert or in the rainforest, and there is no internet access.
• It is a free service because you do not want to pay for it.
• If the internet fails, you want to have a service available for emergencies.

So, in this article, we will teach the following:

• Teach you what Ollama is (a program used to handle AI models on your laptop) and learn how to install it on your local machine.
• Also, we will teach you how what an AI model on-premises is and teach the most famous ones, and some advantages of each one.
• We will talk about hardware requirements for different local AI models.
• In addition, we will install a model (qwen2.5-coder:1.5b) and test it.
• Finally, we will generate T-SQL queries and play with the local AI model.

So, let’s start with it.

Requirements

• First, SQL Server is installed and SSMS.
• This test will be done on a Windows 11 machine.

What is Ollama?

The llama is a nice animal from Peru and Bolivia. It grows in cold places close to the mountains. But this is not a biology article. Let’s talk about the AI software.

Ollama is a company founded by Jeffrey Morgan and Michael Chiang. They wanted to create a simple tool to handle local LLMs (Large Language Model, like DeepSeek, Claude, Llama, ChatGPT). Before Ollama, it was complex to configure and compile these LLMs. They created a nice and friendly tool to handle them.

So, in a few words, Ollama is a centralized tool to install and configure different AI models on-premises.

How to Install Ollama

Installing Ollama is a straightforward process. Just click on this link and press the Download button from the Ollama web page.

There is a version of macOS, Linux, or Windows. Press the Download for Windows button.

Once downloaded, run the installer and press the install button.

You can go to settings to sign in with an account and use the cloud models.

Press the Sign In button.

You can sign in with the following accounts:

• First, you have your Google Account.
• Secondly, with GitHub.
• Finally, your email address.

Try to Sign in.

Now, we have Ollama installed. The second step is to select your Model.

What is an AI model on-premises

You can have a paid or an open-source model on your machine. You can find a list of models in this link.

The models show some tags, like these:

Let me explain the most important tags:

Cloud

It means that you can also invoke the model in the cloud. Ollama is usually used to install a model locally, but you can also use models in the Cloud. They are models that you do not need to install on your machine.

Embedding

When it supports embedding, you can convert text into vectors. This is used for a powerful search. It converts text into vectors for semantic searches.

Vision

If the model includes vision, it can analyze images, photos, and documents. It can understand images and process information about them. For example, it can detect text in images and colors and objects within them.

Thinking

This option is used for deep reasoning to solve complex problems. It is a feature in AI to solve in a step-by-step process complex problems. It is useful for Advanced coding tasks, solving complex mathematical problems, and scientific problems.

Tools

Tools are the capability to do some actions, like navigate the internet or call the calculator. Usually you have capabilities to access external components like accessing an API, local files, modifying part of the code, running commands in a terminal, or checking some logs.

1B 3B 7B

1B means a billion parameters, 3B means 3 billion parameters. This tag is for billions of parameters. The more parameters, the more hardware required. In neural network, a parameter

A 1B-3B model is a light one, while a 30B or more model requires a powerful server with a lot of GPU/RAM. The more parameters, the more knowledge capacity the model has. A 1B model takes approximately 1 GB of VRAM. A 3B consumes 3 GB of VRAM. A 1B model may have hallucinations. It can be useful for a simple chat task where you can train and do not require too much reasoning.

So, what model should I use?

It depends on your needs. For example, if you require only a simple AI chatbot, Llama or Mistral can be enough. If you need to process some images and require an AI chatbot, Gemma3 could help. Qwen 3.6, Codellama,  could be used for programming.  For semantic search (smart AI search using vectors), nomic-embed-text, mxbai-embed-large. If you need embeddings for multiple languages.

The following table provides a summary of some of the most famous models and their capabilities.

We will try with a model that does not require too much RAM, the qwen2.5-coder:1.5b. It is a lightweight model that can generate some T-SQL code. If you have just 8 GB of RAM, this model will run fine.

Note that this software is lightweight; it is dumber than ChatGPT, Gemini, or other commercial models, but you do not need to pay for it, and you can teach your model to improve.

To install, run this command in the CMD:

ollama run qwen2.5-coder:1.5b

This will install the model in Ollama.

Once installed, in Ollama, select the model installed in Ollama. And you can chat in the Send a message section.

Chat with Qwen

We will chat with the model to understand the capabilities (and disabilities).

Me: Hello, Qwen, tell me what you can do as an AI model and what you cannot do

Qwen:

As an AI model, I am programmed to assist with a wide range of tasks, including answering questions, providing information on a variety of topics, generating text, assisting with programming projects, performing data analysis, providing suggestions for solutions, helping with language translation, summarizing texts, etc.

However, there are certain limitations and things I cannot do. For example, I am unable to create new individuals or entities, reproduce copyrighted material without permission, engage in activities that promote illegal or harmful content, violate laws or regulations, or engage in activities that cause harm to others. Additionally, I must adhere to ethical standards and respect user privacy, and not engage in activities that may harm society or individuals.

Me: Sounds fine. Let's ask this model about T-SQL code and its limitations.

Try some T-SQL code with Qwen

Me: We will ask the model its T-SQL skills. What can you do in T-SQL?

Qwen:

In SQL Server Transact-SQL (T-SQL), there are many built-in functions and procedures that can be used to perform a wide range of tasks. Here are some examples:

1. Data manipulation: You can use SELECT, INSERT, UPDATE, DELETE, and MERGE statements to manipulate data in tables.
2. Querying data: You can use SELECT statements to retrieve data from one or more tables, performing various operations such as filtering, grouping, sorting, and aggregation.
3. Transactions: You can use transaction management functions such as BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION to ensure that multiple database operations are performed together or not at all.
4. Procedures and stored procedures: You can create stored procedures in T-SQL to encapsulate a set of related database operations, making them reusable and easier to maintain.
5. Functions: You can define custom functions using the CREATE FUNCTION statement in T-SQL to perform specific tasks such as calculations, string manipulation, or data transformations.
6. Views: You can create views in T-SQL that provide a simplified interface to access multiple tables and columns in a database.
7. Database management: You can use system stored procedures and functions to manage the database server, such as sp_MSrepl_add_script to deploy scripts to other servers, sys.sp_MSrepl_drop_script to remove scripts from servers, and sp_MSrepl_list_published_databases to list all published databases.

These are just a few examples of what you can do in T-SQL. The language is powerful and flexible enough to perform a wide range of tasks based on your specific requirements.

Me: All right, I will test with some simple

Create some tables

Me: I will try to create some simple code to create tables.

I need a table with data of students, an intermediate table students-grades, and a table with the grades.

Qwen:

Sure! Below are the SQL scripts to create the tables, insert data, and create the view and stored procedure:

Create Tables

1. Students Table

CREATE TABLE Students (
StudentID INT PRIMARY KEY IDENTITY(1,1),
StudentName NVARCHAR(255) NOT NULL
);

1. Students-Grades Intermediate Table

CREATE TABLE Students_Grades (
StudentID INT,
GradeID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (GradeID) REFERENCES Grades(GradeID)
);

1. Grades Table

CREATE TABLE Grades (
GradeID INT PRIMARY KEY IDENTITY(1,1),
Subject NVARCHAR(255) NOT NULL,
Score INT
);

Me: Not bad. I do not like the order of the tables. It should show the intermediate table at the end, but it is OK.

Populating tables

Me: I will ask Qwen to populate the tables.

Populate the tables with 10 students and 50 grades, and populate the intermediate table.

Qwen: Sure! Below are the SQL scripts to insert data:

Insert Data

Students Table

Inserting 10 students into the Students table:

INSERT INTO Students (StudentName)
VALUES ('Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank', 'Grace', 'Hannah', 'Ivy', 'Jack');

Grades Table

Inserting 50 grades into the Grades table:

INSERT INTO Grades (Subject, Score)
VALUES 
('Math', 85), ('Science', 92), ('English', 78), ('History', 90), ('Art', 88),
('Physics', 86), ('Chemistry', 91), ('Biology', 89), ('Geography', 87),
('Dance', 93), ('Gymnastics', 84), ('Swimming', 92), ('Tennis', 87),
('Table Tennis', 86), ('Rowing', 85), ('Skating', 90), ('Soccer', 88),
('Basketball', 87), ('Volleyball', 89), ('Golf', 88), ('Cycling', 84),
('Running', 83), ('Jogging', 82), ('Climbing', 80), ('Hiking', 78);

Students-Grades Intermediate Table

Inserting the intermediate data linking students to their grades:

DECLARE @StudentID INT, @GradeID INT;
SET @GradeID = 1;

SELECT TOP 10 StudentID
FROM Grades
ORDER BY Score DESC;

WHILE @@ROWCOUNT > 0
BEGIN
  SELECT Top 1 @StudentID FROM Students ORDER BY StudentName ASC;
  INSERT INTO Students_Grades (StudentID, GradeID)
  VALUES (@StudentID, @GradeID);
  SET @GradeID += 1;
END;

Me: The code to insert data for the students table is incorrect. This is basic stuff, but Qwen failed. For the intermediate table, it also fails.

Creating views with Gwen

Me: I want a view of the student information and grades based on the previous tables.

Qwen: Sure! Below are the SQL scripts to create the view.

Create View

Creating a view to include student information and grades:

CREATE VIEW StudentGradesView AS
  SELECT S.StudentName, G.Subject, G.Score
  FROM Students S
JOIN Students_Grades SG ON S.StudentID = SG.StudentID
JOIN Grades G ON SG.GradeID = G.GradeID;

Example Usage

To use the view and stored procedure, you can execute the following queries:

-- Using the view to get student grades
SELECT * FROM StudentGradesView;

Me: Not bad, this code works.

Create a Stored Procedure

Me: I want a stored procedure of the student information and grades based on the previous tables.

Qwen: Sure! Below are the SQL scripts to create the stored procedure.

Creating a stored procedure to retrieve student information and grades for a specific student:

CREATE PROCEDURE GetStudentGrades @StudentName NVARCHAR(255)
AS
BEGIN
  SELECT S.StudentName, G.Subject, G.Score
  FROM Students S
JOIN Students_Grades SG ON S.StudentID = SG.StudentID
JOIN Grades G ON SG.GradeID = G.GradeID
WHERE S.StudentName = @StudentName;
END;

-- Using the stored procedure to get student grades

EXEC GetStudentGrades 'Alice';

These scripts should help you populate your database with data and create a user-friendly interface for querying and managing student information and grades.

Me: This code works.

Asking a medium-level T-SQL question

Me: I will ask a more complex question to our AI model. Let’s take a look at it.

Can you provide me with some examples to enable the xp_cmdshell and the code to check the current IP address?

Qwen:

Certainly! Here are some examples of how to enable the xp_cmdshell in SQL Server and use it to retrieve the current IP address:

To enable the xp_cmdshell in SQL Server, you can use the following commands:

sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

This will set the value of the xp_cmdshell configuration option to 1, which enables the xp_cmdshell extended stored procedure.

To use the xp_cmdshell extended stored procedure to retrieve the current IP address, you can use the following code:

DECLARE @cmd NVARCHAR(2048);
SET @cmd = 'hostname';
EXEC sp_executesql @cmd;

This will execute the hostname command and return the current machine's hostname. You can modify the command to retrieve other information, such as IP address, MAC address, etc.

Note that the xp_cmdshell extended stored procedure is considered a security risk because it allows remote access to the SQL Server server. Therefore, you should only enable it if you have a secure environment and are confident that you are using it safely.

Me: OK, show me the example to get the IP address.

Qwen: Sure! Here's an example of how to retrieve the current IP address in SQL Server using T-SQL:

SELECT CAST(HOSTPROPERTY('MachineName', 'IPAddress') AS NVARCHAR(256));

This will execute a system procedure called HOSTPROPERTY, which retrieves information about the machine on which the SQL Server instance is running. The first argument to HOSTPROPERTY is 'MachineName', and the second argument is 'IPAddress'. The result of this query will be the current IP address of the machine. Note that this method may not work for all machines or operating systems, as the HOSTPROPERTY function may not always provide accurate results.

Me: I do not know where Qwen got this information, but it is completely wrong. There is a saying: You get what you pay for.  This is a free AI model, so it is less sophisticated than other commercial AI models.

Conclusion

These portable AI models are interesting. If you cannot use the commercial paid models, it is an interesting choice that may work after some training for some routine jobs. You will need to dedicate some time to teaching your model how you work and how to do things. This Qwen model is a light one. There betters’ models that require better hardware. So far, it is like a dumb intern who can learn with patience and effort. I am pretty sure that these models will be better in the future, and we are just testing some models too early. Each day, the AI moles are growing more and more.