SQL Server Logical Reads vs Physical Reads

When it comes to SQL Server query tuning, two of the most used phrases are logical reads and physical reads. But do you know exactly what are these, how they distinguish? It is not a very complicated thing, but recently I started explaining small things for me (and you) so when we go to complex stuff we have a strong base to sit on.

Logical Read

As a measurement, we are told always use logical reads. Logical read is a page read from the buffer pool (SQL Server's in-memory cache). Every page comes to SQL server, through buffer pool. So, it is good and stable measurement to see how much data, query is playing with.

Physical Read

Physical read is a page that had to be pulled from disk, because it is not in the buffer pool (yet). Therefore, physical reads are always subset of logical reads. When page is read from disk it is placed in buffer pool. Then SQL server read from there. 

Since data is coming from disk, more physical reads mean, more dealy.

Let's have a look into an example.

SET STATISTICS IO ON;

SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 776;

In my computer, output was:

(228 rows affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1128, physical reads 3, 
page server reads 0, read-ahead reads 120, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

If I run second time physical reads will be zero.

(228 rows affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1128, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

In both occations, logical reads were same, but physical reads were 3 in first run and 0 in second run (because pages were in buffere pool in second run).

Hope that clarifies.

P.S. If you wondering about "Read-ahed reads", they are also physical reads, but it is just SQL Server being proactive. When SQL server fetch some pages from disk, if it sense, user will query data relatively close to what it fetch, it reads those pages as well and put them in buffere pool. But those read ahead pages were not used in the query that just ran.

SQL Server Performance Tuning Excersises - 2

This is the 2nd part to of the series of blogs where we discuss Performance Tuning Excersises generate by Claude.

Setup

Require StackOverflow database 2010 (10GB) or 2013 (50GB) version.

You can find the download instructions for StackOverflow database in this Brent Ozar page.

Following script create indexes required.

-- Start from a known state
DROP INDEX IF EXISTS IX_Users_Reputation ON dbo.Users;
DROP INDEX IF EXISTS IX_Posts_OwnerUserId ON dbo.Posts;
GO

CREATE INDEX IX_Users_Reputation
    ON dbo.Users (Reputation)
    INCLUDE (DisplayName);

CREATE INDEX IX_Posts_OwnerUserId
    ON dbo.Posts (OwnerUserId)
    INCLUDE (Score);
GO

-- Fresh, fullscan stats so you can't blame stale statistics.
-- This matters: I want to take that explanation off the table up front.
UPDATE STATISTICS dbo.Users WITH FULLSCAN;
UPDATE STATISTICS dbo.Posts WITH FULLSCAN;
GO

Then we create the stored procedure:

CREATE OR ALTER PROC dbo.rpt_TopContributors
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM dbo.Users AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    WHERE u.Reputation >= @MinReputation
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC;
END
GO

Note that in here we are filtering based on reputation points. So, we need two end of reputation points to check the stored procedure. In here Claude has picked 100000 as high reputation point, which means only few users are returned and 10 has the low reputation threshold, which return most of the users in Users table.

So, our test execution script will be:

SET STATISTICS IO, TIME ON;

EXEC sp_recompile 'dbo.rpt_TopContributors';  -- safer than FREEPROCCACHE
EXEC dbo.rpt_TopContributors @MinReputation = 100000;
EXEC dbo.rpt_TopContributors @MinReputation = 10;

In round 1 of testing, we run high reputation threshold first and then lower reputation (as per above).

Result Set 1: @MinReputation = 100000 (compiled with @MinReputation = 10000): 

Table 'Posts'. Scan count 453, logical reads 2622, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 240 ms.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 245 ms.

Result Set 2: @MinReputation = 10 (Compiled with @MinReputation = 100000): 

Table 'Posts'. Scan count 234232, logical reads 755528, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 1010, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1593 ms,  elapsed time = 1796 ms.

 SQL Server Execution Times:
   CPU time = 1593 ms,  elapsed time = 1796 ms.

In round 2 of testing, we run min reputation threshold first and then higher reputation second.

Result Set 3: @MinReputation = 10 (compiled with @MinReputation = 10): 

Table 'Users'. Scan count 0, logical reads 318, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 750 ms,  elapsed time = 971 ms.

 SQL Server Execution Times:
   CPU time = 765 ms,  elapsed time = 1034 ms.

Result Set 4: @MinReputation = 100000 (compiled with @MinReputation = 10): 

Table 'Users'. Scan count 0, logical reads 342, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 19, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 8334, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 735 ms,  elapsed time = 875 ms.

 SQL Server Execution Times:
   CPU time = 735 ms,  elapsed time = 875 ms.

My thoughts:

In above I have used StackOverflow 2010 database.

Looking at the results, we can see result set 2, i.e. executing with @MinReputation = 10 when stored procedure compiled with @MinReputation = 100000 is the worst scenario. Although time wise it is not much visible in 2010 version of the stack overflow database, it has significantly more logical reads (about 755000 in my case). 

If we look at the execution plan for first two result sets, it starts with seeking into reputation index. It is because SQL server thought there will be very small number of users with reputation higher than 100000 (because plan is compiled with @MinReputation = 100000). So, it only found few with reputation higher than 100000. But when we ran with @MinReputation = 10, reputation index returned lot more users (234232 user in my case). In the second part of plan SQL server index seek on each of those users return. This index seek was ok when there were less users (less number of seeks), but for large number of users it is too much. This is why you see performance degrade.

If plan was compiled with @MinReputation = 10 (result set 3 and 4), we can see different shape of plan. In this case SQL server, choose to scan the entire post owners index and sort it on post count. Then for each group (group by OwnerUserId), it seeked into the users table, until it finds 100 users (to full fill TOP 100 users).

For result set 3 and 4, logical reads and cpu time is mostly similar.

My Solution

Knowing that result set 3 and 4 behaved fairly equally not depending on parameter, I suggest we optimize the plan for @MinReputation = 10. So, no matter which parameter we use, it will use the plan compiled for @MinReputation = 10.

ALTER   PROC [dbo].[rpt_TopContributors]
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM dbo.Users AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    WHERE u.Reputation >= @MinReputation
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC
    OPTION (OPTIMIZE FOR (@MinReputation = 10));
END

Above is my solution, note the use of OPTIMIZE FOR hint.

Claude Solution:

Well Claude has accepted my solution, but told me to OPTIMIZE for UNKNOWN. But ideal solution it suggested is using "temp" table to fetch users first and then query posts. To do this query will be split to two. See below:

ALTER   PROC [dbo].[rpt_TopContributors]
    @MinReputation INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT u.Id,
           u.DisplayName,
           u.Reputation
    INTO #tempUsers
    FROM dbo.Users u
    WHERE u.Reputation >= @MinReputation

    SELECT TOP (100)
           u.Id,
           u.DisplayName,
           u.Reputation,
           COUNT_BIG(p.Id) AS PostCount,
           MAX(p.Score)    AS BestPostScore
    FROM #tempUsers AS u
    JOIN dbo.Posts AS p
        ON p.OwnerUserId = u.Id
    GROUP BY u.Id, u.DisplayName, u.Reputation
    ORDER BY PostCount DESC
END

Running above shows fairly low number of logical reads and cpu time for all parameter combinations.

Also Claude has mentioned with temp table usage, I get automatic recompilation, based on the number of rows it retrieved into the temp table. This was something I didn't thought of.

Claude as your Teacher

I've been focusing and studyingon SQL server performance tuning for more than a year now. I was involved in performance tuning task before, but I wanted to be an expert on that area. So I have been following online courses and doing some self-studies and reading books in my spare time.

However, I still feel I lack real world work, which require to be an expert in that field. Best way to get real world experience is find a job on that area. But in my current job I get very little performance tuning experience. I do get lot more SQL development experience. So do use my knowledge on performance tuning when I develop SQL stuff (e.g. stored procedures). 

This led me to search for some real-world experience, without leaving my current job. I did find some information on internet, but didn't find much useful or catered for me (mostly because performance tuning is case by case issue, I think).

Idea came to me suddenly recently, what if I can use AI tool as my real world experience provider. So I started working with Claude to get something developed. I used "Project" feature in Claude chat.

Let us first have a quick look into Claude Project feature.

You can find Project in Claude in left hand side menu:

Claude Projects has 3 components:

• Memory -> This is something claude autogenerate, while you work on the project. But you can edit if you noticed something is not correct.
• Instructions -> Most important part. You can give instructions specific to this project and Claude will follow this everytime it do a task within this project.
• Files -> Any additional knowledge you want to give to claude on the subject.

Your recent chats relate to this project appear under "Recents" section.

So how did I get Claude to give me (or rather teach me) performance tuning exercises?

I gave following prompt/instruction on the project:

I want to challenge my self by resolving various SQL server performance issues. In order to do this I want you to think like a SQL server performance tuning expert and generate me scenarios with performance issues. I want you to generate these challenges in 3 difficult levels -> Easy, Medium and Expert. When I'm ready, I'm going to ask you to generate a scenario and present me in a particular difficult level. For these challenges, you are going to use AdventureWorks sample database provided by Microsoft (which is publicly available) and StackOverflow database which is also available publicly. I want to generate a query or stored procedure or function with a issue and just tell me what users experience when they use it (issues they face). For this you might also need to give some context information also (e.g. parameters particular stored procedure is running, indexes which are already in use) to me. You might also need to give some script to setup the data and other objects to match the scenario in my testing database (e.g. create or drop index scripts). Once you give me the challenge, I will resolve it and present you the solution, for your evaluation. 

Then on project chat area, I gave following prompt:

Please read the instructions given and present me with Easy challenge.

I'm actually really impressed with results. It gave me a scenario where I have to inspect a slow running stored procedure and identify the issue and give solution for it. Not only it gave me a scenario and excersise, it gave me instructions to setup the database in a manner where the issue is occurring.

I'm not going to explain the whole exercises here as it will make this blog very long. But here are the highlights.

In Step 1, it gave me a script to setup the database. Claude used AdventureWorks database. Because default data didn't cater for this exercise, it gave a script to create alternative "SalesOrders" table with about 1.5 million rows in it.

In Step 2, it continued the setup and told me to create an index in a specific way.

In Step 3, it gave me script to create the troublesome stored procedure.

Finally Claude explained the scenario. Here are the actual text:

There's a "Sales by Year" report page in the app that calls dbo.GetOrdersByYear with a single year, e.g. EXEC dbo.GetOrdersByYear @Year = 2022;. Users are complaining that this report is sluggish and keeps getting slower the longer the system's been live — it used to feel snappy when the table was small, now it's a few seconds and climbing. The DBA is confused because there's a perfectly good index sitting on OrderDate that includes the exact columns the query returns, yet it doesn't seem to be helping. CPU also ticks up noticeably whenever the report runs, even though each call only returns one year's worth of rows.

What I want from you:

Run the proc with the actual execution plan on and SET STATISTICS IO, TIME ON, then come back with three things — the root cause (why that index isn't being used the way you'd hope), your fix (the rewritten proc), and the evidence that it worked (before/after logical reads, plan operator change, and duration). Keep the proc's signature the same; the caller shouldn't have to change how it invokes it.  

Not a very bad exercise to start with, isn't it?

I was able to resolve it. When I presented the solution, for my surprise, it showed me some minor things (e.g. data type conversion, which I didn't think of in this situation as it didn't contribute much), I missed.

I'm going to continue to use it. Hoping to blog a about each exercise, and my solution, so you can also learn something from it. If you have any ideas to improve this project, please comments under this blog.

Schema Compare in SSMS 22.7.0

One of the coolest features released lately with SSMS (SQL Server Management Studio) is Schema Compare feature, which released on version 22.7.0.

To me it is one of anticipated feature. Yes, I know we had commercial products like Redgate SQL compare, but most organisations I worked with (small to mid size), doesn't have budget to afford that (or don't want to spend money on that).

There fore I had quick look at what it can do.

Currently it can be invoked through "Tools" menu in SSMS.

Worth noting, this feature is still in "Preview" mode, so it is not fully production ready and might not compare all of the database objects.

Schema Compare UI is as follows:

It has two main panes, one in top (showing what it compare) and one in bottom showing differences in objects when selected.

Optoins button give ability customize your comparison. When pressed following dialog box is opened:

Dialog box has two tabs.

1. General Options
2. Object types to compare

General options tab give you control over the comparison process and change script generation process. It gives options such as "Ignore ANSI NULL". Object Types to compare tab allows you to select which objects to compare:

You can compare following database types:

• Databses
• Databse Projects
• Data-tier Application Files (dacpac)

As the source you can select any of these.

In my demo I have selected two versions of Stack Overlow database:

Then press "Compare" button.

Comparison takes little while, if you database has thousands of ojects, it will definitely take considerable time. I think this is something they need to focus on before release to production.

Here are the results from comparison:

It shows deleted table in target table -> dbo.LegacyPostVotes. New table in source database -> dbo.Badges. And also shows several changes. If you click on one of these lines, you can see detail view of the change. For example following screenshot shows, what appear when I select db.usp_GetUserById stored procedure, which was changed:

You can include/exclude changes you want or don't want. Then press either "Generate Script" button or "Apply" button. Generate Script button, generates the change script and open in a new Script Window. Apply button directly apply included changes into target database.

In my openion, It is not perfect, but start to walk write direction I would say.

I'm hoping to test this more with future releases and write about it.

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.

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.

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.