SQL Hints: NOLOCK and NOWAIT Explained

As we know SQL Server uses pessimistic locking to protect data inconsistency. As part of its pessimistic locking strategy, it ensures data can be accessed safely, without the risk of data corruption, dirty reads or unrepeatable reads using locks. e.g. when you update any row within a transaction, locks are applied at row level which... Continue Reading →

Pagination in SQL Server 2012 and above

Prior to SQL Server 2012, implementing pagination with millions of records was a big challenge for any developer. A commonly used workaround to implement pagination in SQL Server  was the ROW_NUMBER() function. The ROW_NUMBER function, like many of its siblings (i.e. RANK etc.), is a window function that returns a sequential integer value per row... Continue Reading →

SQL Server Physical Joins

SQL Server uses following physical joins to retrieve the data from tables: Nested Loops Nested Loop joins the tables by making the one with least rows as an outer table to optimize performance. For each row in this outer table a one-by-one comparison is done to all the rows in the inner table. If there... Continue Reading →

SQL Server – OLTP vs OLAP

In this blog post I will explain the differences between OLTP & OLAP and how should we configure SQL Server and its workload in both the cases. OLTP (Online Transaction Processing) OLTP is designed to serve as a persistent data store for business or front-end applications. OLTP administers day to day transaction of an organization.... Continue Reading →

Database Migration from on-prem to Azure SQL

Why should you migrate your on-prem database to cloud:  Here are following factors, you would consider when migrating an on-prem database to cloud: You want to modernize current IT asset base. You want to prepare your database platform for future needs. You want to lower your database infrastructure costs. You want to increase Business Agility.... Continue Reading →

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: