SQL & Cloud Lab

"Should I use a CTE or a temp table?" is one of the most common SQL Server questions, and the answer is often misunderstood. The key fact: a CTE is just a named query, not a stored result. Once you know that, choosing becomes straightforward. The three options -- 1. Subquery (derived table) SELECT d.DepartmentId, d.TotalPay FROM (SELECT DepartmentId, SUM(Amount) AS TotalPay FROM dbo.Payment GROUP BY DepartmentId) AS d WHERE d.TotalPay > 100000; -- 2. Common table expression (CTE) WITH DeptPay AS ( SELECT DepartmentId, SUM(Amount) AS TotalPay FROM dbo.Payment GROUP BY DepartmentId ) SELECT DepartmentId, TotalPay FROM DeptPay WHERE TotalPay > 100000; -- 3. Temporary table SELECT DepartmentId, SUM(Amount) AS TotalPay INTO #DeptPay FROM dbo.Payment GROUP BY DepartmentId; SELECT DepartmentId, TotalPay FROM #DeptPay WHERE TotalPay > 100000; CTEs and subqueries perform the same SQL Server expands a non-recursive CTE into the main query before optimizing, ex...

Almost every application needs paging: order lists, search results, audit logs. SQL Server makes it easy with OFFSET ... FETCH , and that works fine for the first few pages. On large tables, though, page 5,000 can be hundreds of times slower than page 1. Keyset paging fixes that. OFFSET FETCH DECLARE @PageNumber INT = 3, @PageSize INT = 50; SELECT OrderId, OrderDate, CustomerId, Total FROM dbo.Orders ORDER BY OrderDate DESC, OrderId DESC OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; ORDER BY is required. Always include a unique column (such as the primary key) at the end of ORDER BY . Without it, rows with the same date can appear on two pages or on none. Why deep pages get slow To return page 1,000 with 50 rows per page, SQL Server must read the first 49,950 rows in order and throw them away, then return the next 50. The deeper the page, the more work, even with a perfect index. On a busy table, deep pages also take locks or read versions for all those s...

Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Removing them in SQL Server is straightforward once you have a repeatable method: define what a duplicate is, choose which copy to keep, delete the rest safely, and then make sure it cannot happen again. Step 1: define what a duplicate is Rows rarely match in every column, because each usually has its own ID and timestamp. Decide which columns make two rows "the same". For an employee import, that might be EmployeeCode alone; for payroll lines, perhaps PayrollRunId , EmployeeId and PayCode . Step 2: count the duplicates SELECT PayrollRunId, EmployeeId, PayCode, COUNT(*) AS copies FROM dbo.PayrollLine GROUP BY PayrollRunId, EmployeeId, PayCode HAVING COUNT(*) > 1 ORDER BY copies DESC; This tells you how big the problem is before you change anything. Step 3: see each duplicate with ROW_NUMBER ROW_NUMBER numbers the copies within each group. The ORDER BY decides which one coun...

"Insert the row if it does not exist, otherwise update it" is one of the most common tasks in any application. SQL Server's MERGE statement looks like the perfect tool, and it can be, but it has sharp edges that cause duplicate key errors, deadlocks and accidental deletes in busy systems. Here is what to watch for, and a simpler pattern that is safe under load. How MERGE works MERGE dbo.Employee AS target USING (SELECT @EmployeeCode AS EmployeeCode, @FullName AS FullName) AS source ON target.EmployeeCode = source.EmployeeCode WHEN MATCHED THEN UPDATE SET FullName = source.FullName WHEN NOT MATCHED BY TARGET THEN INSERT (EmployeeCode, FullName, DepartmentId, CountryCode, HireDate) VALUES (source.EmployeeCode, source.FullName, 1, 'IN', CAST(GETDATE() AS DATE)); It compares a source with a target and runs an update, insert or delete for each row, all in one statement. Pitfall 1: race conditions A single MERGE statement is not automatically safe agai...

Every SQL Server instance has one tempdb , and every database on that instance shares it. When tempdb is slow or full, everything slows down or fails at once. A few configuration choices and monitoring habits prevent most tempdb problems. What uses tempdb Your objects: #temp tables and table variables. Internal work: sorts, hash joins and aggregations that spill out of memory, spools, and some index operations such as SORT_IN_TEMPDB . The version store: row versions for read committed snapshot isolation (RCSI), snapshot isolation, online index rebuilds and triggers. (With Accelerated Database Recovery, versions are kept in the user database instead.) tempdb is recreated empty every time SQL Server restarts. Use multiple, equally sized data files With a single data file, many sessions creating temp objects at once compete for the same allocation pages, which shows up as PAGELATCH_* waits on tempdb pages. Multiple data files spread that work out. The widely used starting point is: O...

Some query operators need working memory: sorts, hash joins and hash aggregates. Before the query runs, SQL Server reserves that memory as a memory grant . If the grant is too small, the work spills to tempdb and slows down. If it is too large, other queries wait in line for memory. Both problems usually come back to the same root cause. How the grant is sized SQL Server estimates how much memory each memory-consuming operator needs, based on how many rows it expects and how wide those rows are . The grant is fixed when the query starts and cannot grow while it runs. So the accuracy of the estimate decides everything. Problem 1: grant too small, so it spills If the estimate is far too low, the sort or hash runs out of its memory and writes intermediate data to tempdb, then reads it back. That is far slower than memory. In an actual execution plan you will see a warning icon on the Sort or Hash Match operator, such as "Operator used tempdb to spill data during execution". Typ...

Backups are the one part of database work you cannot get wrong. The right strategy depends on two questions your business must answer: how much data can we afford to lose (recovery point objective, RPO) and how long can we be down (recovery time objective, RTO). SQL Server's backup types and recovery models are the tools you use to meet them. Recovery models SIMPLE: the transaction log is truncated automatically at checkpoints, so you cannot take log backups. You can only restore to the time of a full or differential backup. Good for development, test and databases that can be rebuilt easily. FULL: every change is kept in the log until a log backup. With regular log backups you can restore to any point in time . The standard choice for production. Without log backups, the log keeps growing. BULK_LOGGED: like FULL, but some bulk operations are minimally logged. Point-in-time restore is not possible within a log backup that contains bulk operations. Usually switched to temporari...

Someone ran an UPDATE without a WHERE clause at 14:32. With the right backups, you can bring the database back to 14:31 and lose only a minute of work. That is a point-in-time restore . Here is how to do it calmly and correctly. What you need The database in the FULL (or BULK_LOGGED) recovery model. A full backup taken before the mistake. An unbroken chain of transaction log backups from that full backup up to and past the mistake. In the SIMPLE recovery model there are no log backups, so you can only go back to the last full or differential backup. Step 1: find the exact time Pin down when the mistake happened as precisely as possible: application logs, the user's report, or job history. Restore to a moment just before it. All times in STOPAT are server local time. Step 2: take a tail-log backup If the database is still online, back up the part of the log not yet backed up. This captures everything up to now, including the mistake, so no later work is lost while you investi...

SQL Server 2022 added a set of small T-SQL features that quietly remove years of workarounds. None of them are headline features, but you will use them every week once you know they exist. Most need database compatibility level 160 and are also available in Azure SQL Database. GREATEST and LEAST SELECT EmployeeID, GREATEST(LastLogin, LastPayslipView, LastProfileEdit) AS LastActivity, LEAST(BasicPay, PayCap) AS CappedPay FROM dbo.EmployeeActivity; No more nested CASE expressions or VALUES tricks to find the max across columns. NULLs are ignored unless all arguments are NULL. IS [NOT] DISTINCT FROM SELECT * FROM dbo.Employees e JOIN staging.Employees s ON s.EmployeeID = e.EmployeeID WHERE e.Email IS DISTINCT FROM s.Email; A NULL-safe comparison: two NULLs are treated as equal, NULL vs a value as different. Perfect for change detection without ISNULL(col, '') hacks. DATETRUNC SELECT DATETRUNC(MONTH, OrderDate) AS MonthStart, SUM(Amount) FROM dbo.Orders GROUP...

Modern applications send and receive JSON everywhere, and SQL Server has built-in functions to read, write and query it, available since SQL Server 2016 and in Azure SQL. Used well, they let you store flexible data and exchange it with APIs without extra code. Used badly, they create slow, hard-to-query tables. Here is the practical toolkit. Storing JSON Traditionally JSON is stored in an NVARCHAR column, with a check constraint to keep it valid: CREATE TABLE dbo.EmployeeSettings ( EmployeeId INT PRIMARY KEY, Settings NVARCHAR(MAX) NOT NULL CONSTRAINT CK_EmployeeSettings_IsJson CHECK (ISJSON(Settings) = 1) ); INSERT INTO dbo.EmployeeSettings VALUES (1, N'{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"roles":["payroll","hr"]}'); Azure SQL Database and newer SQL Server versions also offer a native json data type that stores JSON mor...

Dapper and Entity Framework Core are the two most popular ways to talk to SQL Server from .NET. Debates about them often turn into speed contests, but the better question is: how much do you want to write SQL yourself, and how much do you want the framework to do for you? Dapper in one minute Dapper is a "micro-ORM": a small library of extension methods on IDbConnection . You write the SQL; Dapper runs it and maps the results to your objects. using var conn = new SqlConnection(connectionString); var employees = await conn.QueryAsync<Employee>( @"SELECT EmployeeId, FullName, DepartmentId FROM dbo.Employee WHERE DepartmentId = @DeptId AND IsActive = 1", new { DeptId = deptId }); Parameters are passed as an anonymous object, so queries stay safe from SQL injection. EF Core in one minute EF Core is a full ORM. You describe your model in C#, write LINQ, and EF Core generates the SQL, tracks changes and saves them, with migrations to evolve the ...

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool." If you have seen this error in a .NET application, the database is usually fine; the application has run out of pooled connections. Understanding how pooling works makes it easy to find the real cause. How connection pooling works Opening a physical connection to SQL Server is expensive: network round trips, authentication and session setup. So Microsoft.Data.SqlClient keeps a pool of open connections and reuses them. When you call Open() , you get an existing connection from the pool if one is free. When you call Close() or Dispose() , the connection goes back to the pool instead of being closed. There is a separate pool for each distinct connection string in each process. By default a pool holds up to 100 connections ( Max Pool Size ). When all 100 are in use, the next Open() waits, and after the connect timeout (15 seconds by default) throws the error above. Cause 1: connecti...

In the cloud, short interruptions are normal. Azure SQL Database occasionally moves a database to another node for maintenance, scaling or load balancing, and during those few seconds connections drop or new ones are refused. A well-built application shrugs these off with a short retry. A badly built one shows users an error page. Transient vs permanent errors Only transient errors should be retried: ones that will probably succeed if you try again in a moment. Examples commonly treated as transient for Azure SQL include: 40613 : database is not currently available (often during a failover). 40197 : the service encountered an error processing your request. 40501 : the service is currently busy. 49918, 49919, 49920 : cannot process request because of too many operations or requests. Connection-level failures such as timeouts while connecting or a forcibly closed connection. Errors like a syntax error, a missing table, a permission denied or a constraint violation are not transient. Re...