European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2025 Hosting - HostForLIFE.eu :: SQL Server Analytics-based Intelligent Inventory Management System

clock November 17, 2026 13:12 by author Peter

An All-Inclusive Guide to Developing Intelligent Stock Control with Angular, ASP.NET Core, SQL Server, and AI/Analytics

Overview
One of the most important duties for businesses in the retail, manufacturing, logistics, and e-commerce sectors is inventory management. Too little inventory leads to missed sales, whereas too much inventory raises storage costs. Conventional systems merely monitor reorder levels, purchases, and quantities. However, contemporary firms need something more sophisticated: deeper analytics, automatic replenishment, anomaly detection, and forecasting.

A Smart Inventory Management System powered by SQL Server Analytics provides these capabilities. With built-in query performance, Machine Learning Services, advanced reporting, and Angular-based dashboards, organizations can build intelligent solutions that predict demand, optimize stock levels, and reduce overall operational cost.

This guide explains how to build a complete production-ready, AI-enhanced smart inventory system using:

  • SQL Server (inventory, analytics, ML)
  • ASP.NET Core (API layer)
  • Angular (frontend UI)
  • AI models (forecasting and anomaly detection)
  • Background jobs (automated replenishment)

This article is designed for beginner, intermediate, and senior developers.

1. Understanding Smart Inventory Management

A smart inventory system goes beyond storing stock count. It uses analytics and AI to support business decisions.

  • Key Features
  • Real-time inventory tracking
  • Automatic reorder suggestions
  • Demand forecasting
  • Low-stock alerts
  • Supplier performance analysis
  • ABC classification
  • Expiry and batch tracking
  • Sales velocity analytics
  • Safety stock calculation
  • Dashboard visualizations

Traditional systems offer CRUD operations. Smart systems offer insights.

2. High-Level Architecture

Here is the recommended architecture for a robust smart inventory solution:
Angular UI
    ↓
ASP.NET Core REST API
    ↓
SQL Server (Inventory, Analytics, ML Models)
    ↓
Python/R ML Scripts (Forecasting)
    ↓
Background Services (alerts, reorder engine)


Each component has a specific responsibility:

  • Angular: dashboards, charts, tables, alerts, user operations
  • ASP.NET Core: APIs for stock, suppliers, forecasting, notifications
  • SQL Server: stores stock data, purchase history, demand patterns, model outputs
  • Machine Learning Services: forecasting models (Python/R)
  • Background Jobs: periodic analytics refresh, reorder suggestions, threshold monitoring

3. Designing the Inventory Database
SQL Server stores inventory, sales, supplier data, and forecast results.

3.1 Items Table

CREATE TABLE Items (
    ItemId INT IDENTITY PRIMARY KEY,
    ItemName NVARCHAR(200),
    SKU NVARCHAR(100),
    Category NVARCHAR(100),
    SupplierId INT,
    ReorderLevel INT,
    SafetyStock INT,
    UnitPrice DECIMAL(18,2)
);


3.2 Stock Table
CREATE TABLE Stock (
    StockId INT IDENTITY PRIMARY KEY,
    ItemId INT,
    Quantity INT,
    LastUpdated DATETIME DEFAULT GETDATE()
);


3.3 Sales History Table
CREATE TABLE SalesHistory (
    SaleId INT IDENTITY PRIMARY KEY,
    ItemId INT,
    QuantitySold INT,
    SaleDate DATE
);


3.4 Forecast Results
CREATE TABLE ForecastedDemand (
    ForecastId INT IDENTITY PRIMARY KEY,
    ItemId INT,
    ForecastMonth DATE,
    ForecastQuantity INT
);


3.5 Reorder Suggestions
CREATE TABLE SuggestedOrders (
    SuggestionId INT IDENTITY PRIMARY KEY,
    ItemId INT,
    SuggestedQuantity INT,
    GeneratedAt DATETIME DEFAULT GETDATE()
);

4. Using SQL Server Analytics for Demand Forecasting
SQL Server Machine Learning Services lets you run Python or R forecasting directly inside stored procedures. This avoids exporting data to external systems.

Example: Monthly Demand Forecast Using Python
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothing

df = InputDataSet
df["SaleDate"] = pd.to_datetime(df["SaleDate"])
df = df.resample("M", on="SaleDate").sum()

model = ExponentialSmoothing(df["QuantitySold"], trend="add", seasonal="add", seasonal_periods=12)
fit = model.fit()

forecast = fit.forecast(3)  # next 3 months

OutputDataSet = forecast.reset_index()
',
  @input_data_1 = N'SELECT SaleDate, QuantitySold FROM SalesHistory WHERE ItemId = 101'
WITH RESULT SETS ((ForecastMonth DATE, ForecastQuantity FLOAT));


Insert results into ForecastedDemand:
INSERT INTO ForecastedDemand(ItemId, ForecastMonth, ForecastQuantity)
EXEC ForecastStoredProcedure @ItemId = 101;


5. Building ASP.NET Core API Layer
ASP.NET Core exposes inventory data and forecast analytics to Angular.

5.1 Get Current Stock

[HttpGet("stock")]
public async Task<IActionResult> GetStock()
{
    var items = await _context.Stock
        .Include(x => x.Item)
        .Select(x => new {
            x.Item.ItemName,
            x.Quantity,
            x.Item.ReorderLevel,
            x.Item.SafetyStock
        })
        .ToListAsync();

    return Ok(items);
}


5.2 Get Forecast Results
[HttpGet("forecast/{itemId}")]
public async Task<IActionResult> GetForecast(int itemId)
{
    var result = await _context.ForecastedDemand
        .Where(x => x.ItemId == itemId)
        .OrderBy(x => x.ForecastMonth)
        .ToListAsync();

    return Ok(result);
}

5.3 Generate Reorder Suggestions
[HttpPost("suggest-reorder")]
public async Task<IActionResult> GenerateReorders()
{
    var items = await _context.Items.ToListAsync();
    var stock = await _context.Stock.ToListAsync();

    foreach(var item in items)
    {
        var qty = stock.First(s => s.ItemId == item.ItemId).Quantity;

        if(qty < item.ReorderLevel)
        {
            var suggestedQty = (item.ReorderLevel + item.SafetyStock) - qty;

            _context.SuggestedOrders.Add(new SuggestedOrder
            {
                ItemId = item.ItemId,
                SuggestedQuantity = suggestedQty
            });
        }
    }

    await _context.SaveChangesAsync();
    return Ok();
}


6. Angular Application Structure
Angular gives stock dashboards, charts, and alert screen.

Recommended modules:

  • DashboardModule
  • InventoryModule
  • SalesModule
  • ForecastModule
  • AlertsModule

Install Charts
npm install chart.js ngx-charts

7. Angular Service for Inventory API
inventory.service.ts

@Injectable({ providedIn: 'root' })
export class InventoryService {
  constructor(private http: HttpClient) {}

  getStock(): Observable<StockItem[]> {
    return this.http.get<StockItem[]>('/api/inventory/stock');
  }

  getForecast(id: number): Observable<Forecast[]> {
    return this.http.get<Forecast[]>(`/api/inventory/forecast/${id}`);
  }
}

export interface StockItem {
  itemName: string;
  quantity: number;
  reorderLevel: number;
  safetyStock: number;
}

export interface Forecast {
  forecastMonth: string;
  forecastQuantity: number;
}

8. Building Angular Dashboard
Stock Overview Component
stock-overview.component.ts
@Component({
  selector: 'app-stock-overview',
  templateUrl: './stock-overview.component.html'
})
export class StockOverviewComponent implements OnInit {

  displayedColumns = ['itemName','quantity','reorderLevel','safetyStock'];
  dataSource = new MatTableDataSource<StockItem>();

  constructor(private inventory: InventoryService) {}

  ngOnInit() {
    this.inventory.getStock().subscribe(res => {
      this.dataSource.data = res;
    });
  }
}


HTML:
<mat-card>
  <h2>Current Stock</h2>

  <table mat-table [dataSource]="dataSource" class="mat-elevation-z8">

    <ng-container matColumnDef="itemName">
      <th mat-header-cell *matHeaderCellDef> Item </th>
      <td mat-cell *matCellDef="let row">{{ row.itemName }}</td>
    </ng-container>

    <ng-container matColumnDef="quantity">
      <th mat-header-cell *matHeaderCellDef> Stock </th>
      <td mat-cell *matCellDef="let row">{{ row.quantity }}</td>
    </ng-container>

    <ng-container matColumnDef="reorderLevel">
      <th mat-header-cell *matHeaderCellDef> Reorder Level </th>
      <td mat-cell *matCellDef="let row">{{ row.reorderLevel }}</td>
    </ng-container>

    <ng-container matColumnDef="safetyStock">
      <th mat-header-cell *matHeaderCellDef> Safety Stock </th>
      <td mat-cell *matCellDef="let row">{{ row.safetyStock }}</td>
    </ng-container>

    <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
    <tr mat-row *matRowDef="let row; columns: displayedColumns"></tr>

  </table>
</mat-card>


9. Forecast Chart in Angular
forecast-chart.component.ts

@Component({
  selector: 'app-forecast-chart',
  templateUrl: './forecast-chart.component.html'
})
export class ForecastChartComponent implements OnInit {

  lineChartData = [];
  lineChartLabels = [];

  constructor(private inventory: InventoryService) {}

  ngOnInit() {
    this.inventory.getForecast(101).subscribe(res => {
      this.lineChartLabels = res.map(r => r.forecastMonth);
      this.lineChartData = [
        {
          data: res.map(r => r.forecastQuantity),
          label: 'Forecasted Demand'
        }
      ];
    });
  }
}


HTML:
<mat-card>
  <h2>Demand Forecast</h2>

  <canvas baseChart
          [datasets]="lineChartData"
          [labels]="lineChartLabels"
          chartType="line">
  </canvas>
</mat-card>


10. Automated Reorder Engine
A background job (Hangfire or Quartz.NET) can run nightly:

Example Scheduled Job

public class ReorderJob
{
    private readonly InventoryService _service;

    public ReorderJob(InventoryService service)
    {
        _service = service;
    }

    public async Task Execute()
    {
        await _service.GenerateReorderSuggestions();
    }
}

Register with Hangfire:
RecurringJob.AddOrUpdate<ReorderJob>(
    "auto-reorder",
    job => job.Execute(),
    Cron.Daily);


11. Alerts and Notifications
Based on stock thresholds or forecasts, send alerts:

SQL trigger-based alerts

IF EXISTS (SELECT * FROM Stock WHERE Quantity < ReorderLevel)
INSERT INTO Alerts(ItemId, Message) VALUES (@ItemId, 'Low Stock');

Angular Alerts Screen
Use Material Snackbar or Notification Panel to display alerts.

12. Performance Analytics

A smart system must analyze:

  • Inventory turnover rate
  • Slow-moving items
  • Fast-moving items
  • Supplier lead time trends
  • Stock aging reports
  • Purchase frequency

Example SQL for turnover:
SELECT ItemId,
       SUM(QuantitySold) / SUM(Quantity) AS TurnoverRate
FROM SalesHistory
JOIN Stock ON SalesHistory.ItemId = Stock.ItemId
GROUP BY SalesHistory.ItemId;

Angular visuals can show:

  • Doughnut charts
  • Column charts
  • Line graphs

13. AI Enhancements
13.1 Predictive Restocking
Use forecasting + safety stock.

13.2 Anomaly Detection
Detect unusual sales patterns.

13.3 Price Optimization
Suggest ideal selling price.

13.4 Stock-Out Prediction
Forecast when stock will reach zero.

13.5 Supplier Analytics
Predict late deliveries.

14. Security Best Practices

  • Secure API with JWT
  • Use stored procedures to avoid SQL injection
  • Encrypt sensitive data
  • Implement row-level security if multi-tenant
  • Use HTTPS for Angular app and API


15. Deployment Strategy
Recommended Setup

  • SQL Server: Azure SQL or VM
  • ASP.NET Core: Azure App Service
  • Angular: Azure Static Web App or Blob Storage website
  • ML Services: Azure Container Apps or SQL ML Services
  • Background jobs: Azure Functions or Hangfire

Conclusion
A Smart Inventory Management System powered by SQL Server Analytics combined with ASP.NET Core and Angular offers a complete, intelligent solution for modern organizations. It helps businesses not only track stock but also understand consumption patterns, predict demand, automate replenishment, and improve overall efficiency.

In this article, we covered:

  • Inventory schema design
  • SQL analytical queries
  • Demand forecasting with ML Services
  • ASP.NET Core APIs for inventory and forecasting
  • Angular dashboards for real-time stock visibility
  • Forecast charts
  • Automated reorder engine
  • Alerts and notifications
  • AI-driven enhancements
  • Security and deployment best practices

This system can be deployed across multiple industries including retail, food distribution, warehouses, manufacturing, and healthcare.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2025 Hosting - HostForLIFE.eu :: When a Database Index is Present but The Table is Still Scanned by The Query

clock November 13, 2026 14:15 by author Peter

One of the first things developers think about when a query starts to run slowly is adding an index to a database table. Creating an index does not ensure that every query against the table will utilise it, but it can have a significant impact. When a table has an index on the specific column being filtered, this might be misleading.

For example, you may have:
CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);


and still see a query like this perform a table scan:
SELECT *
FROM Orders
WHERE CustomerId = 1001;


The index exists, the query looks correct, and yet the database chooses to read a large part of the table.

This is usually not a database failure. The query optimizer is making a cost-based decision based on the available statistics, estimated number of matching rows, query shape, and other factors.

Understanding why that decision happens is more useful than simply adding another index.

How a Database Decides Whether to Use an Index

When a query is executed, the database optimizer looks at different possible execution strategies.

For a query such as:
SELECT *
FROM Orders
WHERE CustomerId = 1001;

the optimizer may consider:

Index Seek
    |
    v
Find matching CustomerId values
    |
    v
Read required rows

or:
Table Scan
    |
    v
Read table pages
    |
    v
Check CustomerId for each row


The optimizer estimates the cost of both approaches.
If the table is small, or if a large percentage of rows match the condition, reading the table sequentially can be cheaper than using the index and then fetching many rows from the table.

This is why an existing index does not automatically mean an index seek is the correct plan.

Index Seek and Table Scan Are Different Operations

An index seek means the database can navigate the index directly to the relevant part of the data.

For example:
CustomerId = 1001
       |
       v
Index
       |
       +---- matching entries
              |
              v
           Data rows


A table scan is different.

The database reads pages from the table and evaluates the predicate against the rows it encounters.

Conceptually:
Table
 |
 +-- Page 1 -> check rows
 +-- Page 2 -> check rows
 +-- Page 3 -> check rows
 +-- Page 4 -> check rows
 ...


Neither operation is automatically good or bad.
The correct choice depends on the amount of data that needs to be read.
For example, if a query needs almost every row in a table, an index may not provide much benefit.

The First Thing to Check: The Execution Plan

When a query ignores an index, do not immediately create another index.

Start with the execution plan.

For example, in SQL Server:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM Orders
WHERE CustomerId = 1001;


The actual execution plan can show whether SQL Server selected an Index Seek, Index Scan, Table Scan, Key Lookup, or another operator.

The important question is not simply:
"Did the database use my index?"


Instead ask:
"Why did the optimizer choose this execution plan?"


That question leads to the actual problem.

The Query May Return Too Many Rows

One common reason for a table scan is low selectivity.

Suppose an Orders table contains 10 million rows, and the index is:
CREATE INDEX IX_Orders_Status
ON Orders(Status);


Now consider:
SELECT *
FROM Orders
WHERE Status = 'Completed';

If most orders have the Completed status, the index may not be useful enough.

Using the index could involve:

  • Finding many matching index entries.
  • Following those entries to the table.
  • Reading a large number of data pages.
  • Returning the rows.

A sequential scan may be cheaper.

The important concept is selectivity.

A highly selective condition returns relatively few rows.

For example:
WHERE OrderId = 987654321

is usually much more selective than:
WHERE Status = 'Completed'

when most orders are completed.

Selectivity Matters More Than the Existence of an Index
Consider a table with one million rows.
If a query returns five rows, an index can be very useful.
If the query returns 900,000 rows, the database has much more data to process.
The index itself does not change this fact.

A useful mental model is:
Few matching rows
      |
      v
Index often helps

Many matching rows
      |
      v
Scan may be cheaper

This is why simply checking whether an index exists is not enough.
You need to understand the distribution of values in the column.

Statistics Can Cause the Optimizer to Make the Wrong Choice
Query optimizers rely heavily on statistics.

Statistics provide information about the data distribution, such as how common particular values are and how many rows the optimizer expects to match a predicate.

Suppose the optimizer estimates:
Expected rows: 100

but the query actually returns:
Actual rows: 500,000

The execution plan chosen using the original estimate may be inefficient.
This can happen when statistics are outdated or when the data distribution has changed significantly.
In SQL Server, you can inspect statistics and update them when appropriate.

For example:
UPDATE STATISTICS Orders;

This should not be treated as a universal solution. Updating statistics unnecessarily can create additional work, and the correct maintenance strategy depends on the database and workload.

The important point is that an index and its statistics are closely related to the optimizer's decision.

Data Growth Can Change an Execution Plan

A query that was fast yesterday may become slower after significant data growth.

Imagine an application initially has:
100,000 rows

and later grows to:
50,000,000 rows

The same index may still exist.
The same SQL query may still exist.

But the cost calculation can change dramatically.

This is particularly common in systems where a table grows continuously, such as:

  • transaction tables
  • audit logs
  • event data
  • telemetry
  • order history
  • application logs

Performance testing should therefore consider realistic data volumes rather than relying only on development databases.

The Query May Prevent Efficient Index Usage

Sometimes the problem is not the index. The query itself makes efficient index access difficult.

For example:

SELECT *
FROM Users
WHERE LOWER(Email) = '[email protected]';


The database may need to evaluate LOWER against many rows depending on the database engine and indexing strategy.

A function applied to an indexed column can make normal index access less effective.

The exact behavior depends on the database system and the available indexes.

If the application frequently searches using a transformed value, consider a schema or indexing strategy designed for that access pattern rather than repeatedly applying the transformation at query time.

Data Type Mismatches Can Also Cause Problems

Another issue is comparing different data types.

For example, suppose UserId is stored as an integer:
UserId INT

but the application sends a string parameter:
"12345"

The database may need to perform an implicit conversion.
Depending on the database engine and which side of the comparison is converted, this can affect index usage.
The safer approach is to use the correct parameter type from the application.

For example, in C#:
var userId = 12345;

command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;


The exact API depends on the data-access library, but the principle remains the same: keep parameter types consistent with the database schema.

A Covering Index Can Change the Cost

Sometimes the database does use an index, but the query is still expensive because it needs additional columns from the base table.

For example:
SELECT CustomerId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 1001;

An index on CustomerId may locate the matching rows, but the database may then need additional lookups to retrieve OrderDate and TotalAmount.

If this happens frequently, a covering index may be appropriate.

For example:
CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount);


Now the index contains the columns required by the query.
The database may be able to answer the query directly from the index without repeatedly accessing the base table.
However, adding included columns increases index size and maintenance cost.
Do not turn every frequently queried table into a collection of large covering indexes.

Key Lookups Can Make an Index Look Worse Than a Scan

A common execution plan pattern is:
Index Seek
    |
    v
Key Lookup
    |
    v
Return rows


This can be efficient when only a few rows are returned.
But if the index matches thousands or millions of rows, repeated lookups can become expensive.
At some point, the optimizer may decide that scanning the table is cheaper.

For example:
Index Seek + 500,000 lookups
              |
              v
        High I/O cost


versus:
Table Scan
     |
     v
Sequential page reads


This is one reason an index can exist and still not appear in the final execution plan.

Parameter Sniffing Can Change the Plan

In SQL Server, parameterized queries can introduce another issue called parameter sniffing.

Suppose a query uses:
SELECT *
FROM Orders
WHERE CustomerId = @CustomerId;

One customer might have five orders.
Another customer might have five million orders.
A plan that is efficient for the first customer may not be efficient for the second.
The same query can therefore behave differently depending on the parameter value and the execution plan that was compiled and reused.
If you suspect parameter sniffing, inspect the execution plan and compare estimated versus actual row counts for different parameter values.
Do not immediately add indexes. The problem may be plan selection rather than missing indexing.

A Query Can Scan the Index Instead of the Table

There is also an important distinction between an Index Scan and a Table Scan.
An Index Scan means the database reads a large portion of an index.

That can still be a reasonable plan.

For example, suppose an index is much narrower than the base table.

Reading the entire index may require fewer pages than reading the entire table.

So this:
Index Scan

is not automatically a performance problem.

Similarly, this:
Table Scan

is not automatically evidence that the database is poorly configured.
Always look at logical reads, CPU time, execution duration, returned rows, and the actual execution plan.

A Practical Troubleshooting Process

When you discover that a query is not using the expected index, use a consistent process.

Step 1: Capture the Actual Query
Do not investigate a simplified version if the application is executing something different.

Capture the actual SQL and parameters used by the application.

Step 2: Check the Actual Execution Plan

Look for:

  • Table Scan
  • Index Scan
  • Index Seek
  • Key Lookup
  • Sort
  • Hash Match
  • large estimated versus actual row differences

Step 3: Check the Number of Rows
Determine how many rows actually match the predicate.

If the query returns a large percentage of the table, a scan may be completely reasonable.

Step 4: Check Statistics

Look for stale or inaccurate statistics and verify that the optimizer has useful information about the data distribution.

Step 5: Check Predicates
Look for:

  • functions on indexed columns
  • implicit conversions
  • non-sargable expressions
  • leading wildcards
  • unnecessary calculations

Step 6: Check Existing Index Design
Ask whether the index actually supports the query.

Look at:

  • key columns
  • column order
  • included columns
  • index width
  • duplicate indexes
  • overlapping indexes

Step 7: Measure Before Changing Anything
Capture baseline metrics before modifying the query or index.

This gives you a reliable way to determine whether the change actually improved performance.

Common Mistakes
Creating More Indexes Without Reading the Plan

More indexes can increase storage and write overhead without fixing the real problem.

Assuming Every Query Should Use an Index
The optimizer can legitimately choose a scan.

Ignoring Data Distribution
An index on a low-selectivity column may not provide much benefit.
Using SELECT *

Returning unnecessary columns can increase I/O and make covering strategies more difficult.

Ignoring Actual Row Counts
Estimated and actual row counts should be compared when investigating unexpected plans.

Testing Only With Small Development Data

A query that works well against 10,000 rows may behave very differently against millions of rows.

Best Practices

  • Always inspect the execution plan before changing indexes.
  • Compare estimated rows with actual rows.
  • Understand the selectivity of indexed columns.
  • Keep statistics current according to the database maintenance strategy.
  • Avoid unnecessary functions and implicit conversions in predicates.
  • Consider covering indexes when repeated key lookups are expensive.
  • Test with production-like data volumes.
  • Measure logical reads, CPU, and execution time before and after changes.
  • Remove duplicate or unnecessary indexes instead of continuously adding new ones.
  • Remember that a scan is not automatically a performance problem.

Conclusion
The usage of an index seek in a query is not guarantyd by the existence of an index. Selectivity, statistics, row counts, query form, lookup costs, and the total cost of each possible execution plan are all taken into account by the optimiser. Start with the execution plan and actual row counts when a query scans the table even when it has an index. Before making any modifications, look into statistics, predicates, data types, and index design.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2025 Hosting - HostForLIFE.eu :: How to Reduce Lock Contention in SQL Server Financial Ledger Tables?

clock November 10, 2026 12:34 by author Peter

Financial ledger tables are some of the most heavily accessed structures in enterprise systems. They store critical accounting events such as journal entries, postings, adjustments, settlements, accruals, and reversals. These tables often experience very high write concurrency, especially in:

  • ERP posting engines
  • High-volume billing systems
  • Payment gateways
  • Reconciliation services
  • End-of-day and batch processing jobs

As concurrency increases, SQL Server frequently faces lock contention, leading to:

  • Long-running transactions
  • Deadlocks
  • Slow inserts/updates
  • Users experiencing blocking
  • Increased TempDB usage
  • Timeouts in APIs or stored procedures

This article explains why ledger tables suffer from lock contention, and provides practical, implementable techniques to reduce blocking, improve throughput, and increase system stability.

Why Financial Ledger Tables Face High Lock Contention

1. Constant Writes
Ledger tables often record every financial event.
Even medium systems may generate:

  • 50–200 records per user action
  • Thousands of rows per batch job
  • Millions of rows per day

Continuous writes cause frequent locks.

2. Wide Rows With Many Columns
Ledger rows often contain:

  • CompanyId
  • AccountId
  • Debit/Credit
  • Amount
  • Currency
  • ReferenceId
  • Audit columns

Wide rows increase the cost of locking and reduce throughput.

3. Heavy Use Of Aggregates
Financial reporting queries run concurrently with postings:
SELECT AccountId, SUM(Debit) - SUM(Credit)
FROM Ledger
WHERE AccountingPeriod = '2025-01'
GROUP BY AccountId;

Such queries escalate to locking large ranges of rows.

4. Range Locks Due To Non-Optimized Indexing
If AccountId or PostingDate is not properly indexed, SQL Server uses:

  • Table scans
  • Key-range locks
  • Intent locks

These escalate quickly under concurrency.

5. Long Transactions
Posting operations often perform multiple validations and write across several tables.
Long transactions hold locks longer, increasing blocking.

Lock Contention Symptoms In Ledger Tables
Common Issues

  • Blocking chains
  • Deadlocks involving ledger rows
  • LCK_M_S, LCK_M_U, LCK_M_X waits
  • WRITELOG and PAGELATCH waits
  • API timeouts during accounting operations
  • Transaction log growth due to long-running transactions

Typical Indicators
SELECT * FROM sys.dm_tran_locks 
WHERE resource_associated_entity_id = OBJECT_ID('Ledger');

SELECT * FROM sys.dm_os_waiting_tasks 
WHERE wait_type LIKE 'LCK%';

High frequency of these waits confirms contention.

Technique 1: Use Appropriate Isolation Levels
Default Behavior

Most systems run in READ COMMITTED, causing shared locks during reads.

Recommended
Enable READ_COMMITTED_SNAPSHOT (RCSI):

ALTER DATABASE FinanceDB SET READ_COMMITTED_SNAPSHOT ON;

Benefits:

  • Readers do not block writers
  • Writers do not block readers
  • Ledger reporting queries become non-blocking

This single change can reduce contention by 70–90%.

When To Use Snapshot Isolation
If high consistency is needed for accounting operations:
ALTER DATABASE FinanceDB SET ALLOW_SNAPSHOT_ISOLATION ON;

Then in code:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Use it for:

  • Posting engines
  • Settlement jobs
  • Reconciliation processes

Technique 2: Narrow The Update Scope
Problem

Some ledger updates touch many rows at once:
UPDATE Ledger
SET IsReconciled = 1
WHERE AccountId = @Acct AND PostingDate = @Date;


This creates:

  • Key-range locks
  • Long blocking spans
  • Update lock escalations

Solution
Break the update into small batches:
WHILE 1=1
BEGIN
    WITH cte AS (
        SELECT TOP (500) LedgerId
        FROM Ledger
        WHERE AccountId = @Acct
          AND PostingDate = @Date
          AND IsReconciled = 0
    )
    UPDATE cte SET IsReconciled = 1;

    IF @@ROWCOUNT < 500 BREAK;
END


Benefits:

  • Smaller locks
  • Faster release of resources
  • Lower deadlock probability

Technique 3: Use Optimized Indexing To Reduce Range Locks
Bad Indexing Pattern
Ledger tables often have an index like:
CREATE INDEX IX_Ledger_AccountId ON Ledger(AccountId);


Missing PostingDate or JournalEntryId forces range locks.

Recommended Covering Index
CREATE INDEX IX_Ledger_Account_Date 
ON Ledger(AccountId, PostingDate)
INCLUDE (Debit, Credit, Amount);


Why this helps:

  • Converts table scans into seeks
  • Reduces lock footprints
  • Prevents range locks from escalating

Partitioning Bonus
If table is huge (hundreds of millions):

Partition by PostingDate:
PARTITION BY RANGE (PostingDate)

This physically separates ledger rows, reducing lock footprint even further.

Technique 4: Use Insert-Only Patterns (Immutable Ledger Design)
The best ledger models treat entries as immutable:
No UPDATE

No DELETE

Only INSERT


Benefits:

  • No update locks
  • No deadlocks on row modifications
  • Append-only architecture scales extremely well

Example: Instead Of Updating Balances
Bad:
UPDATE AccountBalance 
SET ClosingBalance = ClosingBalance + @Amt 
WHERE AccountId = @Acct;

Good:
INSERT INTO AccountBalanceHistory(AccountId, AmountChange, Timestamp)
VALUES (@Acct, @Amt, GETUTCDATE());

Then compute balances in reporting layer or via materialized views.

This pattern removes 90% of lock contention.

Technique 5: Reduce Transaction Length
Problem
Posting operations often run like this:

BEGIN TRAN;

-- validations
-- external API call
-- logging
-- balance adjustment
-- final insert

COMMIT;

Long-running external calls hold locks for seconds.

Solution
Move non-critical logic outside the transaction:

BEGIN TRAN;

-- minimal required operations
INSERT INTO Ledger(...)
INSERT INTO Journal(...)

COMMIT;

-- post-commit logging
-- send notification
-- update cache
-- call external APIs


This shortens lock durations drastically.

Technique 6: Use Row-Versioning To Reduce Write Contention

Add a RowVersion column
ALTER TABLE Ledger 
ADD RowVer ROWVERSION;

Use optimistic concurrency:
UPDATE Ledger
SET Amount = @Amt
WHERE LedgerId = @Id AND RowVer = @OldVersion;


If the row changed, SQL Server returns 0 rows.
Client retries instead of blocking.

Technique 7: Move Operational Queries To Replica
If you use Availability Groups:

  1. Primary = Accept writes
  2. Secondary replicas = Handle reads

Reporting queries such as:
SELECT * FROM Ledger WHERE PostingDate BETWEEN ...

should be routed to replicas, not the primary.

This removes all read locks on the primary ledger.

Technique 8: Use Scalable Posting Patterns
Pattern 1: FIFO Posting Queue


Producer pushes journal requests to a queue:

  • Service Bus
  • Kafka
  • SQL Queue table

Consumer processes in controlled batches, reducing contention.

Pattern 2: Micro-batches

Process ledger writes in slices:
BatchSize = 1000

Best for high-traffic financial systems.

Pattern 3: Sharded Ledger Tables
Split ledger by:

  • Company
  • Account range
  • Region

This allows parallelism and reduces hot rows.

End-To-End Example: Optimizing Journal Posting
Original (High Contention)
BEGIN TRAN

INSERT INTO Ledger(AccountId, Debit, Credit, PostingDate)
VALUES (@Account, @Debit, @Credit, @Date)

UPDATE AccountBalance
SET Balance = Balance + @NetAmount
WHERE AccountId = @Account

COMMIT


Optimized

  • Use RCSI
  • Insert-only pattern
  • Batch account balance updates

BEGIN TRAN

INSERT INTO Ledger(AccountId, Debit, Credit, PostingDate)
VALUES (@Account, @Debit, @Credit, @Date)

INSERT INTO BalanceDelta(AccountId, AmountChange)
VALUES (@Account, @NetAmount)

COMMIT


A background worker aggregates BalanceDelta periodically:
UPDATE AccountBalance
SET Balance = Balance + d.AmountChange
FROM BalanceDelta d
WHERE AccountBalance.AccountId = d.AccountId

DELETE BalanceDelta WHERE Processed = 1;


This eliminates hot rows and reduces contention dramatically.

Diagram: Ledger Lock Contention vs Optimized Architecture
Before Optimization

Client Requests → Ledger Table (Hotspot)
                  ↑       ↓
                Updates  Aggregates
                  ↑       ↓
               Blocking / Deadlocks


After Optimization
Client → Ledger (Insert-Only) → Processed Quickly
Client → BalanceDelta → Background Worker → AccountBalance (Low Contention)
Reports → Secondary Replica (No Locks On Primary)


This architecture is used by modern ERP and billing systems.

Conclusion
Financial ledger tables face extreme lock contention because of:

  • High write concurrency
  • Frequent aggregates
  • Wide rows
  • Bad indexing
  • Long transactions

By applying the techniques in this guide, RCSI, batching, optimized indexing, insert-only design, optimistic concurrency, posting queues, and replicas, you can reduce contention by 80–95%, enabling smooth and scalable financial operations.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2025 Hosting - HostForLIFE.eu :: When Is It Appropriate to Use SQL Server Columnstore Indexes?

clock November 5, 2026 12:59 by author Peter

When applications need to evaluate millions of records, database performance becomes a problem. A report that summarizes client activity, analyzes product categories, or computes monthly sales may scan a significant portion of a table to yield a little result. For many application queries, traditional rowstore indexes are effective, particularly when users are retrieving specific records. Analytical queries, however, frequently read a small number of columns from a huge number of data. It might add needless labor to read and analyze full rows.

By storing data in a column-oriented fashion, SQL Server columnstore indexes solve this issue. They arrange values by column rather than rows, which enables the database engine to read just the columns required for a query. This can lower storage needs and enhance the performance of analytical queries. Columnstore indexes aren't always the ideal option, though. Traditional rowstore indexes may be more advantageous for transaction-heavy applications and searches that fetch individual entries.

In this article, you will learn how columnstore indexes work, how to create them in SQL Server, when to use them, and how to evaluate their performance.

What Is a Columnstore Index?
A columnstore index stores table data in a column-oriented format. SQL Server organizes column values into compressed groups, allowing analytical queries to process large amounts of data efficiently.

Consider a sales table with these columns:

OrderId

CustomerId

ProductId

Quantity

TotalAmount

1001

501

20

2

1500.00

1002

502

15

1

450.00

1003

503

20

4

3000.00

1004

501

18

3

2100.00

An application query might retrieve one order by its ID. That operation typically benefits from an index that quickly locates the required row.
A reporting query might calculate total sales for each product:
SELECT
    ProductId,
    SUM(TotalAmount) AS TotalSales
FROM dbo.Sales
GROUP BY ProductId;


This query reads many rows but uses only two columns. A columnstore index can help SQL Server process the required columns without reading every column in the table.

The benefit becomes more noticeable when a table contains millions of records and analytical queries scan large portions of the data.

How SQL Server Columnstore Indexes Work?

Columnstore indexes use several mechanisms to improve analytical workloads.

Column-oriented storage
Data is organized by column rather than primarily by row. Queries can read the columns they need instead of processing all fields in every row.
For example, a report that calculates total sales by product does not need customer names, shipping addresses, or order notes.

Data compression

Values in a column often have patterns that can be compressed efficiently. SQL Server uses columnstore compression techniques to reduce the amount of data that must be stored and read.

Lower storage requirements can also reduce I/O, which is important when analytical queries scan large datasets.

Row groups
SQL Server organizes columnstore data into row groups. Each row group contains a set of rows stored in column-oriented segments.
The engine can use metadata about these groups to avoid reading groups that cannot satisfy certain filters. This optimization is known as segment elimination.

For example, a query that filters sales by date may skip row groups whose recorded date ranges do not overlap the requested period, provided the data organization and metadata allow that elimination.

Batch-mode processing
SQL Server can process data in batches rather than handling every row individually. Batch-mode execution can reduce CPU overhead for supported analytical operations.
The actual execution plan determines whether batch mode is used. It depends on factors such as the SQL Server version, query shape, execution plan, and available features.
These mechanisms make columnstore indexes particularly useful for scanning and aggregating large datasets.

Types of Columnstore Indexes
SQL Server supports two main columnstore index types: clustered and nonclustered.

Feature

Clustered columnstore index

Nonclustered columnstore index

Data storage

Stores the table's data in columnstore format

Adds a columnstore structure to an existing rowstore table

Common use

Large analytical tables and data warehouses

Analytical queries against tables that also support transactional operations

Rowstore structure

The table itself uses columnstore storage

The underlying table remains rowstore

Transactional workload

Supported, but workload characteristics matter

Can support mixed workloads when designed carefully

Storage overhead

Columnstore data is the main table storage

Additional storage is required for the columnstore index

Design priority

Analytical scans and aggregations

Analytical access alongside existing rowstore access

The right choice depends on how the application reads and writes data.

Create a Clustered Columnstore Index
A clustered columnstore index is often a good starting point for large analytical tables that primarily support reporting, aggregation, and historical analysis.

Consider a table that stores sales history.
CREATE TABLE dbo.SalesHistory
(
    OrderId BIGINT NOT NULL,
    OrderDate DATE NOT NULL,
    ProductId INT NOT NULL,
    CustomerId INT NOT NULL,
    Quantity INT NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL
);


You can create a clustered columnstore index as follows:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON dbo.SalesHistory;

SQL Server stores the table in clustered columnstore format.

You can then run an analytical query:
SELECT
    ProductId,
    SUM(Quantity) AS UnitsSold,
    SUM(TotalAmount) AS TotalSales
FROM dbo.SalesHistory
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01'
GROUP BY ProductId;


This query summarizes sales for a date range. Because it reads many rows but uses only a few columns, it is a suitable candidate for columnstore optimization.
However, the index alone does not guarantee fast execution. The benefit depends on table size, data distribution, row-group quality, filter selectivity, and the rest of the execution plan.

Create a Nonclustered Columnstore Index
A nonclustered columnstore index can help when a table must support both transactional queries and analytical reporting.
For example, an order-processing system may need to retrieve an order quickly while also producing reports across a large number of orders.

The existing table can retain its rowstore organization, with a columnstore index added for analytical queries.
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales
ON dbo.Sales
(
    OrderDate,
    ProductId,
    Quantity,
    TotalAmount
);

This index contains only the selected columns.

A reporting query can use it when SQL Server determines that the index is suitable:
SELECT
    ProductId,
    SUM(TotalAmount) AS TotalSales
FROM dbo.Sales
WHERE OrderDate >= '2026-10-01'
  AND OrderDate < '2026-11-01'
GROUP BY ProductId;

The optimizer chooses the execution plan. Creating a nonclustered columnstore index does not force every reporting query to use it.

Also, maintaining a nonclustered columnstore index introduces storage and write-maintenance overhead. Test its effect on the application's insert, update, and delete operations before deploying it to a busy transactional table.

When Should You Use Columnstore Indexes?

Columnstore indexes are most useful when queries analyze large volumes of data.

Data warehouses and reporting systems
Data warehouses often store historical data and run queries that aggregate records across months or years.

Columnstore indexes can reduce the amount of data read and improve aggregation performance. They are a natural candidate for fact tables that contain large numbers of records.

Business intelligence dashboards
Dashboards frequently calculate totals, averages, counts, and grouped metrics.

For example:
SELECT
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    SUM(TotalAmount) AS MonthlyRevenue
FROM dbo.SalesHistory
GROUP BY
    YEAR(OrderDate),
    MONTH(OrderDate);

This query calculates revenue by month. A columnstore index may improve performance when the table contains a large historical dataset.

Large event and telemetry tables

Applications that collect application events, usage metrics, or operational records often need to analyze large volumes of data.

Columnstore indexes can help when queries scan many events and calculate aggregates. However, the best design depends on the ingestion rate, retention strategy, and whether the application also needs frequent point lookups.

Mixed transactional and analytical workloads
A nonclustered columnstore index can help a rowstore table support analytical queries without replacing its underlying rowstore structure.

This can be useful when a business application needs transactional operations alongside reporting. The impact on write performance must still be measured.

When Should You Avoid Columnstore Indexes?
Columnstore indexes are not a universal replacement for rowstore indexes.

Queries that retrieve individual records
Consider this query:
SELECT *
FROM dbo.Sales
WHERE OrderId = 1001;

The query retrieves a single order. A suitable rowstore index on OrderId is generally a more natural choice for this access pattern.

A columnstore index can support some selective queries, but it is usually not the first choice when the workload consists primarily of individual-row lookups.
Small tables

For a small table, a columnstore index may provide little benefit. The overhead of maintaining another index can outweigh the savings from compression and analytical processing.

Write-intensive workloads

Applications that insert, update, and delete records frequently must account for index-maintenance costs.

Columnstore indexes support data modifications, but the workload and index design influence performance. High-frequency transactional operations may need a different indexing strategy or a separate analytical data path.

Queries requiring many columns
Columnstore indexes are especially useful when queries access a subset of columns. If a query frequently needs nearly every column, some of the column-oriented storage advantage may be reduced.

The best design should follow the actual query patterns rather than a general preference for one index type.

How to Measure Columnstore Performance?

Before changing an index strategy, establish a baseline.
Run representative queries against the existing design and record execution time, logical reads, CPU usage, and relevant execution-plan details.

You can enable I/O and timing statistics in SQL Server Management Studio:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
    ProductId,
    SUM(TotalAmount) AS TotalSales
FROM dbo.SalesHistory
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01'
GROUP BY ProductId;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;


Run the same query after creating the columnstore index under comparable conditions.
Inspect the actual execution plan and look for operators such as columnstore scans and batch-mode processing where applicable.

Do not rely on elapsed time from a single execution. Cache state, concurrent workload, data distribution, and other environmental factors can affect results.

For production testing, use representative data volumes and workload patterns. Compare both read performance and the effect on writes.

Common Mistakes to Avoid
Creating a columnstore index without checking the workload

A reporting query may benefit, while a point-lookup query may not. Identify the queries causing the performance problem before choosing an index.

Assuming compression guarantees faster queries
Compression reduces storage and I/O, but query performance also depends on filters, execution plans, CPU usage, and data organization.

Ignoring index-maintenance overhead
Additional indexes can increase the cost of writes and consume storage. Evaluate the full workload rather than only the reporting query.

Ignoring row-group quality
Row groups with poor data distribution may limit segment elimination. Data loading patterns and maintenance can affect the effectiveness of columnstore storage.

Testing only with a small dataset
A design that works well with a few thousand rows may behave differently with millions of rows. Use realistic data volumes when evaluating the change.

Best Practices for SQL Server Columnstore Indexes
Start by identifying queries that scan large portions of a table and calculate aggregates. Check whether the workload is primarily analytical or transactional.
Use clustered columnstore indexes for suitable large analytical tables. Consider nonclustered columnstore indexes when an existing rowstore table also needs analytical capabilities.

Select only the columns needed for a nonclustered columnstore index when practical. Measure query performance, write overhead, storage consumption, and execution-plan behavior before making the change permanent.

Finally, monitor the index after deployment. Query patterns and data volumes change over time, so review whether the index continues to deliver measurable benefits.

Summary
SQL Server columnstore indexes help analytical queries process large datasets by storing values in a column-oriented format. Compression, row-group organization, segment elimination, and batch-mode execution can improve reporting and aggregation workloads. Clustered columnstore indexes are useful for many large analytical tables, while nonclustered columnstore indexes can support reporting on existing rowstore tables.

However, columnstore indexes are not automatically the best choice for small tables, point lookups, or every write-intensive workload. Measure real query behavior, inspect execution plans, and evaluate the effect on the entire application before adopting them.

The best indexing strategy is the one that matches your workload and produces measurable improvements without introducing unacceptable maintenance costs.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2025 Hosting - HostForLIFE.eu :: Database Failover is Tested Before it Occurs in Production

clock November 4, 2026 12:59 by author Peter

When an application needs it most, a database that has been operating flawlessly for months may nevertheless malfunction. The primary database may become inaccessible due to a server restart, network outage, storage issue, or database maintenance procedure. In these circumstances, applications that rely on a single database connection frequently encounter difficulties. Errors may be displayed to users, transactions may fail, and background jobs may cease to function.

Database failover helps an application continue working when its primary database becomes unavailable. However, configuring a secondary database is not enough. The application must also handle connection changes, retry failed operations safely, and recover from interrupted transactions.

The best way to find these problems is to test database failover before they happen in production. This article explains how to plan and test database failover in an ASP.NET Core application using SQL Server as an example. The same testing principles apply to PostgreSQL, MySQL, and managed cloud databases.

What Is Database Failover?

Database failover is the process of moving database operations from an unavailable primary database to another database instance.

In a typical high-availability setup, the application connects to a primary database. A secondary instance is available to take over if the primary fails.

A simplified architecture looks like this:
ASP.NET Core Application
          |
          v
Database Endpoint
          |
          v
   Primary Database
          |
     Replication
          |
          v
   Secondary Database


When the primary database becomes unavailable, the database platform or cluster management system promotes an eligible secondary instance.

The application then reconnects through the database endpoint and continues its work.

The exact behavior depends on the database technology and deployment configuration. Some systems provide automatic failover, while others require an administrator or orchestration system to initiate it.

Failover does not guarantee that every operation will succeed. Requests already running during the transition may fail, and transactions that were not committed may need to be executed again.

Why Database Failover Testing Matters
A database cluster can appear healthy while still having problems that only become visible during a failure.

For example, an ASP.NET Core application may use a connection pool containing connections to the old primary database. After failover, some of those connections may no longer be usable.

Another problem occurs when the application retries a database operation without checking whether the previous attempt succeeded.

Consider a payment operation:

  • The application sends a payment record to the database.
  • The database commits the transaction.
  • The connection breaks before the application receives confirmation.
  • The application retries the operation.
  • The payment record may be inserted twice.

This is why failover testing must cover more than database availability. It must also verify application behavior and data consistency.

Prepare a Test Environment
Do not start failover testing by shutting down a production database.
Use a separate environment that resembles the production architecture as closely as possible.

The environment should include:

  • A primary database instance.
  • A secondary database instance.
  • Replication or availability configuration.
  • The same database driver and connection settings used by the application.
  • An ASP.NET Core application connected through the intended database endpoint.
  • Monitoring for database health and application errors.

For SQL Server, an Always On Availability Group can provide a primary and secondary replica. A managed SQL database service may provide a different failover mechanism.

The test should use the same connection strategy that production uses. Testing directly against individual database instances will not accurately represent an application that connects through a failover-aware endpoint.

Before starting, verify that replication is healthy and that the secondary is eligible for failover.

Configure the ASP.NET Core Database Connection

An ASP.NET Core application commonly uses Entity Framework Core to access SQL Server.

A typical registration looks like this:
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection"));
});


The connection string should point to the database endpoint designed for application connections.

For an SQL Server Availability Group, the connection may use a listener:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:sql-listener,1433;Database=OrderDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;Connect Timeout=15"
  }
}


This is an illustrative configuration. Authentication and encryption settings must match the deployment environment.

The important point is that the application should not need to change its connection string manually every time the primary database changes.

Enable connection resiliency
Entity Framework Core supports connection resiliency through provider-specific execution strategies.

For SQL Server:
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection"),
        sqlOptions =>
        {
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5,
                maxRetryDelay: TimeSpan.FromSeconds(10),
                errorNumbersToAdd: null);
        });
});


This configuration allows EF Core to retry certain transient database failures.

However, retries are not a replacement for failover configuration. They cannot make an unavailable database available, and they do not guarantee that an operation is safe to execute again.

For example, a failed read can often be retried safely. A write operation needs more care because the first attempt may have committed before the connection failed.

Create a Test Operation

Before testing failover, create an operation that the application can execute repeatedly.

For example, an order service can insert a new order using a unique operation identifier.
public class Order
{
    public int Id { get; set; }

    public Guid OperationId { get; set; }

    public string CustomerName { get; set; } = string.Empty;

    public decimal Amount { get; set; }

    public DateTime CreatedAtUtc { get; set; }
}


Create a unique index on OperationId:
protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasIndex(x => x.OperationId)
        .IsUnique();
}

The operation identifier provides a way to detect duplicate submissions.

The service can then save an order using a stable identifier:

public async Task<int> CreateOrderAsync(
    Guid operationId,
    string customerName,
    decimal amount,
    CancellationToken cancellationToken)
{
    var existing = await _context.Orders
        .FirstOrDefaultAsync(
            x => x.OperationId == operationId,
            cancellationToken);

    if (existing != null)
    {
        return existing.Id;
    }

    var order = new Order
    {
        OperationId = operationId,
        CustomerName = customerName,
        Amount = amount,
        CreatedAtUtc = DateTime.UtcNow
    };

    _context.Orders.Add(order);

    await _context.SaveChangesAsync(cancellationToken);

    return order.Id;
}


This is a basic example of idempotent request handling. In a production implementation, the lookup and insert must also handle concurrent requests safely. The unique database constraint remains the final protection against duplicate operation IDs.

If a connection fails after a transaction commits, the client can retry using the same operation ID rather than creating a new logical operation.
Test Different Failover Scenarios

A useful failover test plan should include several types of failure.
Test 1: Planned failover

A planned failover moves database responsibilities to another replica through the database platform's supported process.

During this test:

  • Start continuous application requests.
  • Record the primary database instance.
  • Initiate a planned failover.
  • Observe connection errors and request failures.
  • Verify that the application reconnects.
  • Check whether new database operations succeed.
  • Confirm that previously committed records remain available.

For SQL Server Availability Groups, use the supported failover procedure and ensure the target replica meets the required synchronization conditions.

Do not manually change database roles or manipulate replication state outside the platform's supported process.

Test 2: Unexpected primary failure

A planned failover does not reproduce every real-world failure.
In a controlled test environment, simulate an unexpected primary outage using the database platform's supported failure-injection or infrastructure controls.

Observe whether the cluster detects the failure and promotes an eligible secondary.

Measure the time between the primary becoming unavailable and successful application recovery.

This period is important because it affects application availability.

Test 3: Network interruption
Sometimes the database remains healthy, but the application cannot communicate with it.
Simulate a network interruption between the application and database using controlled network rules or a failure-testing tool.

Verify that:

  • Existing database connections fail cleanly.
  • New connections recover after connectivity returns.
  • The application does not enter an endless retry loop.
  • Requests do not remain blocked indefinitely.
  • Connection pools recover without requiring an application restart.

This test is particularly useful for applications running across multiple networks or cloud environments.

Test 4: Failover during a transaction
A database failure during a transaction can produce different results depending on when the failure occurs.

Test at least these situations:

Failure point

Expected behavior

Before transaction starts

Operation can usually be retried

During transaction execution

Transaction may be rolled back

After commit but before response

Commit outcome may be uncertain

During a read operation

Read may be retried if appropriate

During a multi-step business operation

Business consistency must be verified

The most important case is a connection failure after commit but before the application receives confirmation.

The application must not assume that every connection exception means the transaction was rolled back.
Handle Transactions Carefully

EF Core execution strategies can retry operations, but user-managed transactions require special handling.

When using a retrying execution strategy, execute the complete transaction through the strategy rather than creating a transaction outside it.

For example:

var strategy = _context.Database
.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
await using var transaction =
    await _context.Database.BeginTransactionAsync(
        cancellationToken);

var order = new Order
{
    OperationId = operationId,
    CustomerName = customerName,
    Amount = amount,
    CreatedAtUtc = DateTime.UtcNow
};

_context.Orders.Add(order);

await _context.SaveChangesAsync(cancellationToken);

await transaction.CommitAsync(cancellationToken);
});

This demonstrates how an execution strategy can wrap a transaction. It does not, by itself, guarantee exactly-once business execution if the commit outcome is unknown.

For important operations, use stable operation IDs, unique constraints, and a way to verify whether the previous attempt committed before repeating the business action.

Avoid putting external side effects such as payment gateway calls or email delivery inside a database retry delegate. A database retry could execute those external operations more than once.

Use an outbox pattern when database changes must reliably trigger external actions.
Measure Failover Recovery Time

A successful failover test should produce measurable results.

Two useful measurements are:

  • Recovery Time Objective (RTO): The maximum acceptable time required to restore the service.
  • Recovery Point Objective (RPO): The acceptable amount of data loss measured in time.
  • For example, an application may have an RTO target of 60 seconds and an RPO target of zero for a particular business operation.

These targets depend on the business requirements and the database replication configuration. They should not be assumed simply because a secondary replica exists.

Record the following during each test:

Metric

What it tells you

Failover detection time

How quickly the database platform detects failure

Promotion time

How long the secondary takes to become primary

Application recovery time

How long until successful application queries resume

Failed request count

How many user requests were affected

Retry count

How often database operations needed another attempt

Data consistency

Whether committed business records remain correct

Duplicate operation count

Whether retries created duplicate work

Application logs should include correlation IDs and operation IDs so that failed requests can be traced across the API, database, and background workers.

Common Mistakes During Failover Testing
Testing only whether the database is online

A database health check may return success while the application still has broken connections or cannot complete transactions.
Test real application operations, not just database availability.

Assuming retries solve every failure

Retries help with transient errors, but they cannot resolve permanent failures or guarantee that a write was not already committed.
Use idempotency and transaction-aware recovery.

Ignoring connection pools

Existing connections may become invalid during failover. Verify that the application can establish new connections through the configured database endpoint.
Testing without production-like traffic

A failover that works with one request may behave differently under heavy traffic. Run controlled concurrent workloads and monitor database and application resource usage.
Restarting the application after every failover

If the application requires a manual restart to recover, the failover process is not working as intended. Identify the connection, retry, or configuration problem instead of treating a restart as the recovery strategy.

Build a Repeatable Failover Test Plan

Failover testing should be a regular operational activity, not something performed only after a database incident.

A practical test plan should include:

  • Verify replication health before starting.
  • Record the current primary and secondary instances.
  • Start representative application traffic.
  • Perform a planned failover.
  • Measure application recovery time.
  • Verify transaction outcomes and data consistency.
  • Test an unexpected primary failure in a controlled environment.
  • Test network interruption and connection recovery.
  • Review logs, metrics, and failed operations.
  • Document the results and repeat after major infrastructure or application changes.

Run tests in a non-production environment first. Production failover exercises should follow an approved change process with a clear rollback and recovery plan.

Summary
Database failover is not complete just because a secondary database can become the primary. The application must also reconnect, recover from transient failures, protect transactions, and avoid duplicate business operations. For ASP.NET Core applications, combine a failover-aware database endpoint, appropriate connection resiliency, idempotent operations, and transaction-safe retry handling.

Test planned failover, unexpected outages, network interruptions, and failures during transactions. Measure recovery time and verify data consistency after every test. A database failover configuration is only useful when the application can continue operating correctly after the change. Regular testing helps identify those gaps before users experience them in production.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2025 Hosting - HostForLIFE.eu :: Converting SQL Server to UTF-8 Collations Without Destroying Current Data

clock November 2, 2026 11:56 by author Peter

Changing a SQL Server database to use UTF-8 can make it easier to store multilingual text in varchar columns without converting every text column to nvarchar. However, changing a collation is not simply a matter of updating one database setting. Existing applications may depend on how strings are compared, sorted, indexed, or stored. A migration can introduce unexpected character conversions, change query results, or cause unique indexes to reject values that were previously accepted.

The safest approach is to understand how SQL Server collations work, identify the columns that need UTF-8 support, and test the migration before applying it to production.

What Is a UTF-8 Collation in SQL Server?

A collation defines how SQL Server compares and sorts character data. It can control characteristics such as case sensitivity, accent sensitivity, and linguistic sorting behavior.

SQL Server supports UTF-8 encoding for char and varchar data when a compatible collation is used. These collations have names ending in _UTF8.

For example:
SELECT
    SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
    SERVERPROPERTY('Edition') AS SqlServerEdition;

UTF-8 collations are available in SQL Server 2019 and later. Confirm that your SQL Server version and environment support the features you plan to use.

A UTF-8 collation allows a varchar column to store characters from across Unicode, provided the column has sufficient capacity and the application handles the data correctly.

Consider this example:
CREATE TABLE dbo.Customers
(
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(200)
        COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

The column uses a UTF-8 collation. It can store many languages without requiring an nvarchar column. However, VARCHAR(200) means a maximum of 200 bytes, not necessarily 200 characters. Some UTF-8 characters require multiple bytes, so the number of characters that fit depends on the data.

UTF-8 vs. NVARCHAR

SQL Server's nvarchar type uses UTF-16 encoding. The varchar type normally uses a code page determined by its collation, but a UTF-8 collation changes its encoding to UTF-8. 
The choice depends on your application's requirements and existing schema.

Feature

VARCHAR with a UTF-8 collation

NVARCHAR

Character encoding

UTF-8

UTF-16

Unicode support

Yes

Yes

Storage per character

Variable, depending on the character

Usually two bytes for a UTF-16 code unit

Storage for supplementary characters

Up to four bytes per character

Typically a surrogate pair using four bytes

Column size interpretation

Bytes

Byte pairs, generally corresponding to two bytes per UTF-16 code unit

Compatibility

Requires a compatible UTF-8 collation and application support

Widely used for Unicode text in SQL Server

UTF-8 can reduce storage requirements for data dominated by ASCII characters. However, it is not guaranteed to save space for every language or dataset.
For example, ordinary English letters generally use one byte in UTF-8, while many other characters require two, three, or four bytes. Measure the actual data before making storage or performance assumptions.

Why Changing a Collation Can Be Risky?
A collation migration can affect more than character encoding.
Existing data may require more bytes

Suppose an existing column uses a single-byte code page. A character that was previously represented by one byte may require multiple bytes in UTF-8.

A column that was previously large enough for a particular value may no longer have enough capacity for the same number of characters.

For example:
CREATE TABLE dbo.ProductDescriptions
(
    ProductId INT PRIMARY KEY,
    Description VARCHAR(100)
);


After moving to UTF-8, a description containing many multilingual characters may require more than 100 bytes.

The column definition might need to change:
ALTER TABLE dbo.ProductDescriptions
ALTER COLUMN Description VARCHAR(300)
    COLLATE Latin1_General_100_CI_AS_SC_UTF8;

This is only an illustrative example. Before changing a column, inspect its constraints, indexes, dependencies, and existing data. The new size must reflect actual requirements rather than an arbitrary increase.

String comparisons may change

Collations determine how strings compare. A change can affect case sensitivity, accent sensitivity, sorting, and equality comparisons.
For example, a case-insensitive collation can treat Admin and admin as equal for comparison purposes.
If a migration changes comparison behavior, queries and unique indexes may behave differently.
Before changing a collation, identify whether the existing application depends on a particular comparison rule.

Unique indexes may fail

Imagine a table contains two values that were considered distinct under the old collation but become equal under the new one.
A unique index may no longer be valid under the new comparison rules.
For this reason, test unique constraints and indexes before changing production data. Do not assume that an existing unique index will remain valid simply because the stored strings appear different.

Step 1: Inventory Existing String Columns

Start by identifying the character columns in the database.
The following query lists columns that use char, varchar, nchar, or nvarchar:
SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLengthBytes,
    c.collation_name AS CollationName,
    c.is_nullable AS IsNullable
FROM sys.columns AS c
INNER JOIN sys.tables AS t
    ON c.object_id = t.object_id
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
INNER JOIN sys.types AS ty
    ON c.user_type_id = ty.user_type_id
WHERE ty.name IN
(
    'char',
    'varchar',
    'nchar',
    'nvarchar'
)
ORDER BY
    s.name,
    t.name,
    c.column_id;


This inventory helps identify columns that may need attention.
Pay particular attention to columns containing names, addresses, product descriptions, customer messages, imported data, or text from external systems.
Do not convert every column automatically. Some columns may intentionally use a specific collation or may not need UTF-8 support.

Step 2: Check the Existing Collation

Before changing anything, inspect the database's current collation:
SELECT
    name AS DatabaseName,
    collation_name AS DatabaseCollation
FROM sys.databases
WHERE name = DB_NAME();


The database default collation affects new objects and certain operations, but it does not automatically change every existing column's collation.

Existing character columns can retain their own collation settings. Temporary tables, table variables, and expressions involving columns with different collations can also introduce compatibility problems.

A UTF-8 migration should therefore distinguish between changing the database default and changing individual columns.

Step 3: Identify Values That May Be Affected

Before changing encoding, inspect representative data and determine whether it can be represented correctly.

A query such as the following can help estimate the storage used by a varchar column:
SELECT
    ProductId,
    DATALENGTH(Description) AS StoredBytes,
    LEN(Description) AS CharacterCount
FROM dbo.ProductDescriptions;

DATALENGTH reports the number of bytes used by a value. LEN counts characters but excludes trailing spaces. For supplementary Unicode characters, interpretation also depends on the data type and collation.

For a more accurate migration assessment, copy representative data into a test environment and compare the stored values before and after conversion. Include accented characters, non-Latin scripts, supplementary characters, and values near the column's size limit.

Do not rely only on a few English-language records.

Step 4: Test a Column-Level Migration

A column-level change is often easier to control than changing the default collation for an entire database.

For example:
ALTER TABLE dbo.Customers
ALTER COLUMN CustomerName VARCHAR(200)
    COLLATE Latin1_General_100_CI_AS_SC_UTF8
    NOT NULL;

The example assumes that the column is defined as NOT NULL and that its existing constraints and dependencies permit the alteration.

Use the actual nullability and complete column definition from your schema. Some changes may require dropping and recreating dependent indexes or constraints, and large tables may need significant time and transaction-log space.

After the change, inspect the column definition:
SELECT
    c.name AS ColumnName,
    ty.name AS DataType,
    c.max_length AS MaxLengthBytes,
    c.collation_name AS CollationName
FROM sys.columns AS c
INNER JOIN sys.types AS ty
    ON c.user_type_id = ty.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Customers')
  AND c.name = 'CustomerName';

Then verify the data using known test cases and application-level queries.

Step 5: Test Queries, Indexes, and Application Behavior

A successful ALTER COLUMN statement does not prove that the application will continue working correctly.

Test the areas most likely to be affected:

  • Queries that compare strings or use ORDER BY.
  • Unique indexes and constraints.
  • Joins between character columns with different collations.
  • Stored procedures and functions that manipulate text.
  • Temporary tables and staging tables.
  • Search features that use LIKE, CHARINDEX, or other string operations.
  • Applications that assume a fixed number of bytes per character.

For example, the following query may expose a collation conflict when the two columns use incompatible collations:
SELECT
    c.CustomerId,
    o.OrderId
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON c.CustomerCode = o.CustomerCode;

If a conflict occurs, inspect the column collations and determine the correct comparison behavior. Applying COLLATE directly in a query can be useful in specific cases, but it should not become a substitute for a deliberate schema design.

Step 6: Plan the Production Migration

For a production database, treat a collation change as a schema migration with measurable operational risk.

A practical migration plan should include:

  • Back up the database and confirm that the backup can be restored.
  • Inventory affected columns, indexes, constraints, and dependent objects.
  • Test the proposed changes against a recent copy of production data.
  • Measure migration duration, transaction-log usage, and locking behavior.
  • Check application compatibility, including drivers and data-access libraries.
  • Schedule the migration according to the impact on users.
  • Validate stored values, query results, and application behavior after deployment.
  • Prepare a tested recovery plan.

Changing a column's collation can require schema changes and index maintenance. For large tables, consider a staged migration using a new column, data conversion, validation, and a controlled application cutover.

A staged migration requires extra care with writes during the transition. If applications continue updating the old column while data is being copied, the new column can become inconsistent unless the migration includes a reliable synchronization strategy.

Common Mistakes to Avoid
Assuming UTF-8 always reduces storage

UTF-8 can save space for many ASCII-heavy datasets, but characters from other scripts may require more bytes. Compare actual storage requirements before making decisions.

Changing only the database default

Existing columns retain their own collations. Review and migrate the columns that actually need to change.

Ignoring unique constraints

Different comparison rules can cause previously distinct values to compare as equal. Test unique indexes before migration.

Treating encoding and sorting as the same thing

UTF-8 describes how characters are encoded in varchar data. A collation also defines comparison and sorting behavior. Choose a collation that matches both encoding and application requirements.

Skipping application tests

Database storage may be correct while application code still makes incorrect assumptions about character lengths, byte counts, or string comparisons.

Best Practices
Use these practices when planning a UTF-8 migration:

  • Begin with a small set of columns that have a clear Unicode requirement.
  • Keep database backups and migration scripts under change control.
  • Use representative multilingual test data.
  • Check index and constraint compatibility before altering columns.
  • Keep encoding, comparison, and sorting requirements explicit.
  • Measure storage and query behavior rather than assuming performance improvements.
  • Validate the migration in a staging environment before production deployment.
  • Document any columns that intentionally retain a different collation.

Summary
Varchar columns may hold Unicode text using UTF-8 encoding thanks to SQL Server UTF-8 collations. This may save storage for particular datasets and be helpful for apps that require multilingual support. However, storage needs, string comparisons, unique indexes, and application behavior can all be impacted by altering a collation. The safest migration employs a staggered rollout where the risk justifies it, tests representative data, verifies dependent items, and begins with a schema inventory. Changing the name of a collation is not the only objective. It is to guaranty that current data is accurate and that queries and apps continue to function as they should.

HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Month List

Tag cloud

Sign in