All Constraints in SQL Server

Last month we discussed quirky check constraint on SQL Server. That made me think of all constraints in SQL Server.

What is a constraints

Before we actually see all the constraint types in SQL Server, let us find out what is a constraint in SQL Server and why it is there.

Constraints are basically guard rails in database to prevent invalid, inconsistent and meaning data in tables. It is a rule SQL Server enforces automatically on a table column or set of columns.

Instead of trusting database users or applications, SQL server itself can enforce these rules to make sure data is protected.

Example for database rules are:

• A person should not have two identical NIC (social security) numbers
• An order should always belong to a customer
• Age should never be negative

Constraints not only help to keep data in check, but it also help SQL Server Optimizer to arrive at better execution plans. For example, if optimizer knows data in a column cannot be null, it can create a plan which was otherwise not possible.

01. Primary Key Constraint

I think most important of all constraint is primary key constrint. Basic use of Primary key is identifying a specific row in a table. It is actually a combination of NOT NULL and UNIQUE constraints, which means you cannot have null on Primary Key columns and each value need to be unique. Otherwise SQL server cannot identify the row.

One table can have only one Primary Key constraint.

Primary key can be defined two ways. You can specify it as constraint on it's own line (like below).

CREATE TABLE Customers (

    CustomerId   INT           NOT NULL,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)

);

or you can specify against the column. But in this case, you cannot specify a name for the constraint.

CREATE TABLE Customers (

    CustomerId   INT PRIMARY KEY,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)

);

When you add a primary key to a table, that table is physically restructured to sort on specified primary key column values (default behaviour, but can be changed).

You can have composite primary keys. Which means you can define a primary key on more than one column.

CREATE TABLE Customers (

    Surname NVARCHAR(100)  NOT NULL,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (Surname, Email)

);

Experts recommend to have primary key in every table.

02. Foreign Key Constraint

Foreign key constraint create relationship between two tables. This rule make sure, value user enter into the column actually exists in the foreign table.

For example, let us consider Order table.

CREATE TABLE Orders (

    OrderId    INT  NOT NULL,

    CustomerId INT  NOT NULL,

    OrderDate  DATE NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),

    CONSTRAINT FK_Orders_Customers

        FOREIGN KEY (CustomerId)

        REFERENCES Customers (CustomerId)

);

In above example, CustomerId column in the Orders table reference (or have a relationship) with CustomerId field in Customers table. Which means, user cannot enter a customer id which doesn't exists in the Customers table into Orders table. This make sure orphan or invalid data is not entered into Orders table.

You can have self referencing Foreign keys. For example, see below Employees table:

CREATE TABLE Employees (

    EmployeeId INT           NOT NULL,

    FullName   NVARCHAR(255) NOT NULL,

    ManagerId  INT           NULL,  -- NULL means "this person has no manager" (the CEO, basically)

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),

    CONSTRAINT FK_Employees_Manager

        FOREIGN KEY (ManagerId)

        REFERENCES Employees (EmployeeId)  -- references the same table!

);

In here, "ManagerId" column has self-referencing relationship with EmployeeId column in same table. Basically, manager id is employee id of the manager of that particular employee.

As an additional feature, you can specify, what action to take when referenced column is updated or deleted.

CONSTRAINT FK_Orders_Customers

    FOREIGN KEY (CustomerId)

    REFERENCES Customers (CustomerId)

    ON DELETE CASCADE   -- delete the order if the customer is deleted

    ON UPDATE NO ACTION -- (default) block updates that would break the link

In above example if a customer is deleted in Custoemrs table, all orders reference by that customer id, will be also deleted.

It is recommend to use these actions carefully as it can create un-intended behaviours.

You can have foreign key constraint on multiple columns - Composit Foreign key. 

For example, consider following scenario:

CREATE TABLE OrderItems (

    OrderId   INT            NOT NULL,

    ProductId INT            NOT NULL,

    Quantity  INT            NOT NULL,

    UnitPrice DECIMAL(10, 2) NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderId, ProductId),  -- composite PK

);

CREATE TABLE OrderItemNotes (

    NoteId    INT            NOT NULL,

    OrderId   INT            NOT NULL,

    ProductId INT            NOT NULL,

    Note      NVARCHAR(1000) NOT NULL,

    CONSTRAINT PK_OrderItemNotes PRIMARY KEY (NoteId),

    CONSTRAINT FK_OrderItemNotes_OrderItems

        FOREIGN KEY (OrderId, ProductId)               -- both columns together...

        REFERENCES OrderItems (OrderId, ProductId)     -- ...must exist as a pair

);

In above example, OrderId and ProductId is the primary key of the OrderItems tables. Then those two columns were referenced in OrderItemNotes table with a foreign key relationship. Important thing is combination need to be unique, therefore, you need Primary key or unique constraint on parent table on those columns you reference in child table.

However, note that two columns need to be in same table.

When referencing columns, child table doesn't have to have same name for the columns. But need to have compatible data types and need to match the order.

03. Unique Constraint

Keep all values in a column unique and no duplicates are allowed. For example, let us consider Email column in Customers table.

ALTER TABLE Customers

ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

Above constraint make sure no two customers have same email address.

Unlike primary key, you can have multiple unique constraints defined on a table.

Unique key constraint considers NULL as a value and allow 1 null value (no duplicate nulls though). You cannot have filtered unique constraints; however, you can create unique index which makes the same functionality (we will not discuss about indexes here).

Like primary keys, you can have unique constraint on multiple columns. Which means uniqueness is checked across columns.

-- A customer can't place the same order twice on the same day

ALTER TABLE Orders

ADD CONSTRAINT UQ_Orders_CustomerDate UNIQUE (CustomerId, OrderDate);

04. Not NULL Constraint

Make sure column always have a value (no null values are allowed). This check is enforced every time row is inserted or updated.

CREATE TABLE Products (

    ProductId   INT            NOT NULL,

    ProductName NVARCHAR(255)  NOT NULL,  -- can never be empty

    Description NVARCHAR(MAX)  NULL,      -- optional

    Price       DECIMAL(10, 2) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId)

);

In above example, ProductName cannot be null, always need to have a value. But in contrast, Description can be null. Also note Primary key constraints automatically enforce not null.

05. Default Constraint

This constraint makes sure there is always a value in the specified column, even when user didn't insert a value.

CREATE TABLE Orders (

    OrderId    INT          NOT NULL,

    CustomerId INT          NOT NULL,

    OrderDate  DATE         NOT NULL,

    Status     NVARCHAR(50) NULL CONSTRAINT DF_Orders_Status DEFAULT 'Pending',

    CreatedAt  DATETIME2    NOT NULL CONSTRAINT DF_Orders_CreatedAt DEFAULT SYSDATETIME(),

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId)

);

In above example, if user didn't specify a value to Status column, value "Pending" is get inserted. However, if user specify "NULL" value to Status field explicitly in the statement, NULL will be set for that column. Default value only applied when statement (insert or update) doesn't explicitly specify a value. To avoid this, you can have NOT NULL constraint on columns with default values.

06. Check Constraint

Check constraint allow you do define a custom rule using any expression that evaluate to true or false. If the expression evaluates to false for a given row, the insert or update will fail for that row.

ALTER TABLE Products

ADD CONSTRAINT CHK_Products_Price CHECK (Price > 0);

In above example, check constraint make sure, users cannot insert or update a row where Price is less than or equal to 0.

You can have check constraints on multiple columns:

-- A multi-column check: end date must be after start date

ALTER TABLE Promotions

ADD CONSTRAINT CHK_Promotions_DateRange

    CHECK (EndDate > StartDate);

However, if condition evaluate to NULL, then condition will pass the check. To avoid this you can pair the check constraint with NOT NULL.

Disabling Constraints

You can disable Foreign key constraints and Check constraints temporarily. This tick is used when loading data to a table fast (bulk load).

-- Disable a constraint

ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers;

-- Re-enable (and verify existing data)

ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

Note that "WITH CHECK" tells SQL server to validate all existing rows. So if validation fails for any of the rows, constraints will not be enabled.

You can bypass this by specifying WITH NOCHECK, but this is not recommended.

If constraints are re-enabled with NOCHECK, they are marked as NOT TRUSTED. Which means those constraints will not be used in query optimization or in execution plan building (i.e. SQL server will not be believing data is valid).

SQL Server Quirky Check Constraints

Recently at work, I found out curious table structure. I found out in one of the table, primary key had foreign key constraint. Well you will ask what's the strange thing about that? The strange thing is, this foreign key is referring to it self. To be clear, this is not a composite primary key, just a single column standard primary key. Investigation showed that it was done by mistake, no harm done, remove it and every one lived happily ever after.

But that got me thinking, why would SQL server allowed it? Does it has a use case? Curiously I went to find out about it more.

So code for this is something like below:

ALTER TABLE dbo.Employee WITH CHECK 

ADD CONSTRAINT FK_somename 

FOREIGN KEY (EmployeeId) REFERENCES dbo.Employee(EmployeeId)

So as would any one in this AI age would do, I have asked the question from 3 different AI models and all 3 basically gave the same answer.

As suspected, although valid syntax, this has no valid use case.

As a check constraint, this is utterly useless. Check constraint suppose to check value in foreign table already in source table. In this case of course value is already in source table because both are same and same column.

So will it fail when I insert a new value? No.

When inserting a brand new employee Id (e.g. 1010), value doesn't exists in the table yet. So FK constraint should fail. But it doesn't because order of operations happens in SQL server make sure it doesn't fail.

SQL Server's order of operations for an INSERT with FK constraints is roughly:

• Write the row into the table (tentatively, within the transaction)
• Then run the FK constraint check against the table's current state
• If the check passes → commit. If not → rollback.

In step 2 new id is already in the table, even though it is not committed. There fore FK rule pass. There fore, no rollback.

Is it ok to leave it if I found such a quirk? No. It is no harm removing it. Plus it add although tiny, overhead to the insert operation. With thousands of insert these tiny overheads can add upto become issues. So remove it if you found a one.

Why does SQL Server allows it?

Well my geuess is not by design decission, probably by accident when designing all constraint rules.

If you find any useful use case for this, please let me know in comments.

This interesting nature of check contraints made me thinking, we should re-visit all SQL server constraints and see what they are (just for fun and education). I might try to write a blog on that next month.

Schedule Tasks in Claude Cowork

It is all good to have AI agents helping you. But most annoying thing I see with them is I have to be there to invoke and guide. If there is a agent who activitate it self, it would be great. I know there are several solutions for this. But recently I came across Schedule Tasks in Claude Cowork. So I have given a try.

You need to have Claude desktop app installed on your desktop/laptop to get this working. I'm not going to go into installation of it and configuring it here, and assuming it is already done.

Accessing Schedule Tasks

Once you are in Claude desktop app, go to Claude Cowork tab and there you can see "Scheduled" section.

As per screenshot you see all your already created scheduled tasks. 

One of the most important thing to remember is these schedule tasks are NOT using Windows Task Scheduler or any windows service. Instead it purely depend on Claude App running on the desktop either open or in background. You can see if it is running background by checking apps on system tray. Every minute app check for schedule task list and see if it has a task to run and if it find a one it invoke it.

If Claude app is not running or computer is on sleep or computer is turned off, schedule task will not run on time. 

However, it will do a catch up next time you open up the Claude, if it missed a one. Note that it will only run one catch up. For example if computer was turned off for days, it will only run last missed run.

Scheduled task section has option to make sure computer doesn't go to sleep if claude app is running on background. If running the task on schedule is important, it will be good idea to turn this on.

Creating a Scheduled Task

Press the "New Task" button.

You will something like below:

Here I'm going to create claude cowork schedule task to grab latest tech info for my area of expertise to keep my self up to date.

Below the big box under the description, give proper instruction on what to do. Rather than typing your self you can use LLM chat to create a instructions for it. Here is what I have got as instructions:

IP Ban Utility by Digital Ruby

Recently we commissioned a new CMS server for one of our client, although it mostly locked down, we noticed lot of failed login attempts in Windows Event Viewer.

If you go to Event Viewer -> Windows Logs > Security log and then filter for event id 4625 (Logon), you will be able to see login event (both success and failed). Audit failure events shows failed login attempts.

Relate to that 4740 (User Account Management) shows if above failed login attempts caused windows to lock down your account (in case hacker identified one of your account).

So how to prevent this. Most appropriate thing is to remove it from from public access. But server like CMS server need expose to internet. In that case you can lock down RDP and other access methods and just open web traffic. However, there are instances where servers need to be open for public traffic/access, specially if thirdparty is working on them (continuously).

In that case, you can restrict access to known IP addresses, or use VPNs.

If every method is un-available to you, I found out there is another option. That is banning IPs that un-authorized login attempts are coming. You can do this in your firewall.

But hardest thing is hackers change their IPs randomly, when you block one IP they come from another.

I found this elegant peice of software developed by Digital Ruby software house. It is called IP Ban. Process is simple. It monitor the event log for failed login attempts and if it exceed number you specified (from a particular IP), then it add a firewall rule to stop traffic from that IP address.

IPBan is available free on Github -> https://github.com/digitalruby/ipban

You can download it from here -> https://github.com/DigitalRuby/IPBan/releases

More information about developer can be found here -> https://www.digitalruby.com/server-software/

If you need pro version, this is the place to buy -> https://ipban.com/products

IPBan works on both Windows and Linux servers.

Installation is easy (run following in PowerShell):

$ProgressPreference = 'SilentlyContinue'; [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12; iex "& { $((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/DigitalRuby/IPBan/master/IPBanCore/Windows/Scripts/install_latest.ps1')) } -startupType 'delayed-auto'"

It is getting installed as Windows Service (IPBan). You can find the program in default insallation location "C:\Program Files\IPBan".

There is a xml config file on installation directory, where you can change many settings. But mostly default settings works. 

One of the interesting settings is "Whitelist" setting, which allows you to specify comma seperated list of IPs to avoid banning. Make sure to set this for one of your sever, so in case something goes wrong you can log on from there.

It adds Firewall rules prefixed with "IPBan_" so it is easy to identify. You can change the prefix if you prefer something else.

Usually ban is held for 1 day, but you can change this setting.

SQL Server SESSION_CONTEXT

Last week I came across intresting challenge. I was enhancing an auditing framework for application. This application used SQL triggers for auditing. Basically all tables in database (excluding some system table), had auditing fields such as created date, created by, last modified date and last modified by. Each table where it wanted to audit, had triggers to write values of those field everytime row is updated. Audited data was written to big audit log table. Basically everytime row is updated (or created) above audit fields were updated by app, then trigger write the values of those auditting fields to audit log. But issue was, how to track who deleted a row?

When you delete a row, application cannot write to those auditing fields. I mean you can write, but that will be just stupid to update each row just before it get deleted. That will increase writes just to make it auditable (plus extra audits). So I had to find a solution.

My research showed one of the ways to tackle is SQL Server Session Context.

It is a simple concept introduced in SQL Server 2016. Session context is array of key-value pairs attached to a session.

So in your session, you can specify session specific meta data in this array and read it from SQL server to make different dicissions based on the meta data specified for connection. It is like dictionary attached to your connection.

Session context is stored on memory, therefore, it is fast. It is scoped for session and therefore, isolated from others.

So how to make it work?

First you need to set the values in the context:

EXEC sp_set_session_context 

    @key = N'UserId', 

    @value = 123;

Then you can use those values through out your session:

SELECT SESSION_CONTEXT(N'UserId');

If you don't want to make it updatable, you can set the read-only flag:

EXEC sp_set_session_context 

    @key = N'UserId', 

    @value = 123,

    @read_only = 1;

So how did it help my situation:

Well, just before the delete, I could set the "UserId" or "OperatingUserId" like variable in the session context. Then on the trigger, I can read this value and create the audit log for delete with correct user id.

You will say 'Well, it is still a additional write, just before delete?'. 

Yes and No. It is a update to connection, which is held in small part of the memory, so it is not going to write to disk. In fact there is a limitation on session context. Session context only allow maximum 256 key-value pairs, but total size need to be under 1MB (approx).

But be aware it is not a place to hold password like secret information, because if multiple people share the connection (like through application), can see the whole context.

In a connection, where session is shared (like through multi user application). It is vital that you set the userId like values just before where it is going to be use. Otherwise you will be using wrong values, because other users may also set it. There might be concurrency issue, but in my case, rows are deleted in-frequently, so it was ok.

In addition to auditing, you can use it to identify tenant when you application is multi tenant. And it can also be used with row level security (cuatiosly).

Don't over use it, just use it sparegly, 

There are know issues, where session context provide wrong results when queries go parallel. But in my case, delete is always single threaded.

Agent Types in VS Codes

Recently I was working on VS code lot. In fact I was working with Github Copilot lot on VS Code. However have to admit I'm not aware of full capabilities of agentic framework available on VS Code through Github Copilot. I'm just learning as I go.

One of the options you see on Github Copilot window is Agent Type.

There are few options available in this:

• Local
• Background
• Cloud
• Claude (thirdparty)

I wanted to find out what are the different capabilities of each of the Agent types. Here is my find.

Local

Of course this is the default agent which I'm using most of the time. In fact all the time so far. 

Local agent runs within your VS code environment in your local machine. There fore it has access to all the resouces in VS Code, such as your workspace, files, context (stack trace, unit test results, liniting errors, etc.). 

It can use all models available in VS code. Also have access to all agent tools (browsing, MCP and extension provided tools).

Mostly, it is interactive, you can chat with it and give real time feedback and you can steer them the direction you want easily if you see them going off track.

That's why local agents are the ones I use most.

Background

Background agent, also known as Copilot CLI sessions, run in background. Main feature of this is they run even you close down VSCode. These are well suited for long running tasks, such as you have defined and planned all the steps need to take and where you can sure agent can run independantly long time without your input.

When these sessions require attention, they will notify in chat window same way the local agent would do.

There are two isolation levels for these:

• Worktree -> create seperate GIT work tree and work independantly to your main code.
• Workspace -> workspace is where background agent also works in same environment as what you see in VSCode

There are some limitations with background agents. They cannot access all tools in VSCode, plus they don't have access to extension provided tools. Also they are limited to CLI provided models.

Background agent can be start using agent type drop down:

Then you can selection isolation in isolation drop down:

You can also specify a working folder, so agent can independantly work without interfearance:

Cloud Agent

Cloud agents as name suggest runs on cloud (or remote infrastructure). The main cloud agent is Github Copilot cloud agent, which runs on Githug infrastructure (i.e. your remote github repository).

Github Copilot cloud agent as integrated access to Git hub repositories, there for it can do most of the actions (if not all) that any github user can do. Those include large scale refactoring, complete feature development, automatic pull request creation and code reviews.

You can also have thirdparty cloud agents, such as Claude Code and Open AI's Codex.

You can select cloud agent from agent type drop down:

Then you can select repository and give instruction to the agent.

BMAD Method - Part 2

Since I last talk about BMAD Method (here), about 2 months ago. It has evolved fast. Infact, today (23/02/2206) they have released their newest stable release for version 6. If you can remember, I used version 4.4x in my last article. That's how fast AI industry is moving.

Since then I have used BMAD method successfully in one of production application, and I'm very happy about it.

Since I bought Claude pro subscription recently, I'm going discuss how we can use BMAD method through Claude code (which is the recomended way of using it by its developers).

You can use ClaudeCode, in any terminal (standalone terminal, terminal in a IDE or any other terminal supported way). But since I'm familiar with VSCode, I'm going to use terminal in VSCode to use ClaudeCode.

First thing I would suggest doing in install ClaudeCode extension into VSCode.

Once extension is installed, you can go through onboarding wizard to get it configured. There are several ways you can use ClaudeAI models through ClaudeCode. 

Since I have pro subscription, I will use that. Note that using throug API is very costly. When you click on above button, it will link to your subscription.

Now ClaudeCode extention is configured. Let's get started... well not so fast. ClaudeCode actual program is still not installed in your computer (just the VS Extension is installed). You need to install it using PowerShell.

Here is the command for that (runs on powershell)

irm https://claude.ai/install.ps1 | iex

Or you can download the installation from Antropic.

It take little bit time based on your internet speed to install ClaudeCode through powershell, so be patient. Once installed you need to add installation path to the "Path" environment variable for easy access.

Once ClaudeCode is configured, you can launch it through VSCode. There will be a Claude icon on top right hand corner in VSCode window.

Now that we have both installed and cofigure, open a terminal and let's go to the folder where we want to create the app (or where existing app is).

Then install bmad method with npx:

npx bmad-method install

Since you specify the version, it will ask to install the latest stable version:

Installation wizard starts and following screen appear. At the end of that screen it will ask directory to work.

You can accpet the current directory or give folder path to create a new. If it doesn't exists bmad will ask your permission to create it.

I have chosen bmad6test folder.

Then on next screen you need to select modules. BMAD is a platform where you have core framework and you can add modules according to your requirement. For example, if you developing a Game, you can install "BMad Game Dev Studio" module.

I have chosen BMad Agile-AI Driven Development module.

You can choose to install custom modules, but that for advanced users.

Then the most important decision, you will require to choose which AI tool you will be using BMAD method with.

As you can see Claude Code and Cursor is the preferred ones. But there are long list you can choose from including previously shown Github Copilot.

Next it will ask what should it call you (a name for your self).

Next it will ask, preferred language and output language, where output document should be saved and ect.

Module installation can be done express way or customized approach, but I choose express way and use all defaults (which suites me, but if you prefer you can test with custom approach).

That's it.

After BMAD installation, lets switch to the working directory and start VSCode.

Once you inside VSCode open ClaudeCode.

In the ClaudeCode prompt, you can start typing bmad commands and it will show what is available.

Good place to start is bmad-help method.

Google Antigravity

These days every one in developer world is talking about Google's Antigravity. So I was also curious  to find about it.

So what is Google's Antigravity? Well, it is Integrated Development Environment (i.e. IDE), similar to popular IDEs like cursor, windsurf and VSCode. Like all of above mentioned, it is a fork of VSCode.

So what is the uniqueness of that? Well, that's what I want to find out too. As per Google, it is not only an IDE, it is an agent management system. In order to find out about more, I decided to install and test.

You can download Google's Antigravity from this site. Available for Mac, Windows and some Linux distributions (e.g. Ubantu 20+). Windows installation is about 152 MB.

Setup starts with usual User Agreement screen:

Then you get to choose the installation location. You will require about 800 MB disk space for this. Next screen allows you to select additional tasks.

Installation is relatively quick (will depend on your computer/disk speed, took only 2min on mine).

Once installed, unlike VSCode, you are taken through setup wizard.

You can import your setting from other similar IDEs. But I choose to start fresh as I didn't want to effect all the testings I have done with other IDEs.

In next step you can select the theme (light, dark, solarized light, tokyo night). I choose dark of course.

Next step is vital. You get to choose how autonomus your agents will be within IDE.

Since this is local installation, agent get access can access all resource in your system, something you might not want to happen. There fore you can select how much freedom you are given to agents. You have 4 options:

• Secure mode is the safest, in this more agent will ask permission for everything before it go and do something.
• Review driven development (whic is the recomended), require you to review what agent is going to do before it does.
• Agent-driven development -> Give more freedome to agent to do most tasks.
• Custom configuraiton allows you to configure which tasks are allowed and which are not allowed.

In next step you configure your editor.

In order to use Antigravity for free, you need google and gmail account. Wizard will ask you to sign in to this account in next screen.

Final screen is on terms of use.

This is what opening screen looks like:

It is some what similar to VSCode. Note the "Agent Manager" button in the second row in middle. I think is the uniqueness of the IDE.

I will have a play and let you know how my experience is in future blogs.

Installing SSMS 22 for SQL 2025

 

Since we already have SQL Server Installation running from previous blog post, easiest thing was to launch the SSMS from same wizard.

Which will redirect you to this URL.

You will find a button to download the SSMS setup, save the exe to disk and run it.

Most probably the first thing you will see when you launch setup is setup downloading latest setup it self.

Note that SSMS installation also have the same UI as Visual Studio installation.

This is what you first presented with, similar to Visual Studio installation, now you need to select which components you are going to install for SSMS.

Core SSMS components are already selected and cannot be deselect (see right hand side column).

You can choose to install following components:

• AI Assistance (Github Copilot)
• Business Intelligence (SSAS, SSRS, SSIS tools integration with VS)
• Hybrid and Migration tools
• Code Tools (Version control -> GIT and Query Hints)

For my testing I have selected all.

For me it showed total disk requirement is 3.42GB (hmm).

You can further customize the installation by selecting/un-selecting individual components (if required) in "Individual Components" tab.

You can select additional language packs in next tab, if required. I have left it with English.

"Installation Locations" tab, you can change the default installation location.

Once you happy, with all configuration, press "Install" button.

During the installation, you will see following:

After the installation, you can launch the SSMS as you would do normally and here is the new splash screen with welcome changes:

New connection dialog box and new UI is also appealing:

Your recent connections can be found on the top, which is I think very useful feature. Rest of the dialog is familiar to any one who used SSMS 20. 

If you not already familiar with "Encryption" box on connection dialog (which was also in SSMS 20), make sure to select optional. Otherwise you will not be able to connect to SQL server which doesn't have an SSL connection.

Copilot window can be seen in the right side, which is also a interesting addition.

First thing I configured is "Color Theme" to new Dark Mode:

Oh I love that feature, which was missing for ages.

There are many more themes:

That's all for now. I will explore more and let you know if there are anything interesting.

Installing SQL Server 2025

Microsoft has recently release SQL Server 2025. So it is my time to get it hands on.

One of the thing they highlight in this release is AI ready, but I have my doubts on that. SQL Server 2025 i.e. version 17 has vector data type support in-built, which is why they say it is AI ready. But how useful is that? We will see.

One other major thing I see is discontinuation of "Web" edition. Probably not a significant thing for most, but, as a developer and DBA involved in SME (Small and Medium Enterprises), we edition was a very attractive cost reduction option. So I'm kind of disappointed with this.

Resource Governor is now available for standard edition, which is good and also express edition now support up to 50GB database. All these are good for SME sector.

There are two developer editions now:

1. Standard Developer Edition
2. Enterprise Developer Edition

They provide feature of corresponding edition to developer for free.

You can get more information about SQL Server 2025 from it's official page -> here.

I have tried Standard Developer Edition, as I'm mostly work on standard Edition.

Setup file is about 1.2 GB in size, so note that when you download it.

First of all new icon is looking good and modern.

When you start the wizard you get decade old starting screen. I think it is big time that Microsoft need to modernize this?

I went for new installation.

Then you need to choose, the edition you want to install. Note that there is lot more options in there now, such as pay-as-you-go edition via Azure subscription. That's looks cool.

I'm installing Standard Developer edition.

Then you accept the licensing terms.

After that, installation will check for further updates to installation and check installation rules (requirements). Of course there is usual warning on Firewall rules, this can be setup later once installation is done.

Next, you are presented with "Azure Extension for SQL Server". This allows on-premise SQL server to be part of your Azure management group, so you can manage them from a central location. I think this is another cool feature. For my testing purpose, I will just skip this.

You presented with familiar, feature selection page, when you press Next on above screen. I have just selected SQL server database engine. Note that there is new "AI service and Language Extension" feature, which I will skip for now.

Also note the link to download, BI Report server as there is no more Reporting Services.

When you press next on above page, setup will check feature rules (any conflicts and pre-requisite missing).

Then you are presented with feature configuration. This will be vary depending on features you have selected.

I have gone for named instance, since I already have default instance used for previous version of the installation. Note that setup has identified my version 15 installation and it has put the edition as "Enterprise Developer" (there was only one developer edition back then).

Server configuration page:

In Database Engine configuration page, you have few tabs to configure.

First Authentication tab:

I have gone for mixed mode authentication, because I needed offline authentication for testing. Nothing seems to be new in this tab.

Next tab, you choose directories for various tasks:

Since this is just a demo, I will be using the defaults.

TempDB configuration is much more detail now. You can select multiple directories for tempdb data.

You can configure memory now during setup:

MaxDOP can be configured also:

Most of these were configurable later via server property page. In this version we have ability configure them setup time.

Now we are ready to install.

Time for the setup to complete will be vary depending on the features and options you have selected. But at the end you will see following dialog:

Next we will log into database using SSMS. Wait, we need to install new SSMS first for that, so let's take a look at that in next blog post.