Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
Deadlocks happen when queries lock different resources in different orders. ✔ The Fix Always lock tables in the same sequence. Example: BEGIN TRAN SELECT … FROM Users WITH (UPDLOCK) SELECT … FROM Orders WITH (UPDLOCK) COMMIT If every stored procedure follows this sequence →deadlocks drop to near zero.
LIKE ‘%abc%’ is un-indexable — always full scan. ✔ Rarely Known Fix Use a computed column + index: ALTER TABLE Products ADD NameIndex AS LOWER(Name); CREATE INDEX IX_Products_NameIndex ON Products(NameIndex); Query: WHERE NameIndex LIKE ‘%abc%’ 💡 Result Massive speed boost without full scans.
Many devs think more indexes = faster queries.Nope. ⚠ Real Risks Inserts slow down Updates slow down Deletes slow down Database size explodes Memory grants skyrocket Locking increases ✔ The Life-Saving Rule Every index must support a query.If the query does NOT exist → the index should NOT exist. ✔ How To Check Query sys.dm_db_index_usage_stats: […]
Most “solutions” online are myths.Here’s the real fix. 🧨 The Problem SQL Server caches a plan using the first parameter value. If first call is small dataset → plan optimized for smallIf next call is huge → BAD performance ✔ The Correct Fix (Rarely Understood) Use OPTIMIZE FOR UNKNOWN: SELECT * FROM Orders WHERE CustomerId […]
🔒 Blocking Queries wait for each other Slow performance Not fatal ✔ How to Fix Blocking Correct indexing Reduce long transactions Avoid row locks with better filters 💥 Deadlocks Two queries wait on each other SQL kills one session Returns error 1205 ✔ Fix Deadlocks Always access tables in SAME ORDER Keep transactions very […]
SARG (Search ARGument Able) is the biggest reason queries go from 20ms → 10 seconds. ❌ Non-SARGable WHERE YEAR(OrderDate) = 2024 This forces a full scan. ✔ SARGable Version WHERE OrderDate >= ‘2024-01-01’ AND OrderDate < ‘2025-01-01’ 💡 Why It Matters Enables index seeks Saves CPU Reduces IO dramatically
Window functions eliminate messy subqueries and temp tables. SELECT UserId, Amount, RANK() OVER (ORDER BY Amount DESC) AS RankNo FROM Orders; ⚡ Why They’re Amazing 🚀 Blazingly fast 🧮 Ideal for analytics 🔍 Cleaner than GROUP BY hacks ✂ Replaces many procedural patterns
Most devs rebuild indexes far too often. ✔ What Matters 📉 Fragmentation < 30% → ignore 🔄 30–80% → reorganize 🔨 > 80% → rebuild ⚡ Better Approach Use sys.dm_db_index_physical_stats to target heavy tables only. SELECT index_type_desc, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘LIMITED’); 💡 Pro Tip “Rebuild everything nightly” is anti-pattern — kills IO.
CROSS APPLY is the Ferrari of SQL join techniques.It looks fancy, and it really is. 🏎️ ⚡ Why CROSS APPLY Is Amazing 🧠 Lets you join custom row-by-row logic 🚀 Runs faster than correlated subqueries 🔍 Works great with TOP, aggregates & ranking 🪄 Cleaner than temp tables SELECT u.Id, x.LastOrder FROM Users u CROSS […]
Indexes aren’t magic —…but they often look like magic when your query goes 40 sec → 20 ms. ⚡ 🏆 5 Index Types Everyone Should Know 🔹 Clustered Index — defines table order 🔹 Nonclustered Index — fast lookup 🔹 Include Columns — covering index booster 🔹 Filtered Index — tiny but powerful 🔹 Columnstore […]
Parameter sniffing is NOT a bug.It’s SQL Server being too smart… sometimes. 😈 The Problem SQL caches a plan for one parameter and reuses it: EXEC GetOrdersByCustomer @customerId = 1; — cached plan EXEC GetOrdersByCustomer @customerId = 9999; — wrong plan reused ⚠ Symptoms Extreme slowdowns Terrible cardinality estimates “It’s fast for me but slow […]
SELECT * feels convenient……but costs you RAM, CPU, network IO, and index usage. ❌ Why SELECT * Is Dangerous 🧱 Prevents covering index usage 🚚 Sends unnecessary columns over network 🔄 Breaks when columns change 🐢 Slows down joins & scans — ❌ Bad SELECT * FROM Orders; — ✔ Good SELECT Id, OrderDate, Amount […]
Scalar functions look innocent……until your query goes from 50 ms → 12 seconds. — ❌ Never do this inside SELECT SELECT Id, dbo.GetUserScore(Id) FROM Users; 💥 Why They’re Slow ⛓ Executed row-by-row 🚫 Breaks parallelism 📉 Kills execution plans 🐌 Turns queries into RBAR (“Row By Agonizing Row”) ✔ The Fix Use Inline Table-Valued Functions […]
SELECT DISTINCT schema_name(fk_tab.schema_id) + ‘.’ + fk_tab.name as foreign_table, ‘>-‘ as rel, schema_name(pk_tab.schema_id) + ‘.’ + pk_tab.name as primary_table FROM sys.foreign_keys fk INNER JOIN sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id INNER JOIN sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id WHERE pk_tab.[name] = ‘Your table’ — enter table name here — and schema_name(pk_tab.schema_id) = ‘Your table schema […]
When called multiple times in a single batch, rand() returns the same number. So it is better to use convert(varbinary,newid()) as the seed argument: SELECT 1.0 + floor(110 * RAND(convert(varbinary, newid()))) AS random_number newid() is guaranteed to return a different value each time it’s called, even within the same batch, so using it as a seed […]
There are two methods to find a day name from a date in SQL Server: DATENAME() Function FORMAT() Function Using DATENAME DECLARE @Date DATE = ‘2024-07-27’; SELECT @Date As [TDate], DATENAME(WEEKDAY, @Date) AS [Day_Name]; Using FORMAT DECLARE @Date DATE = ‘2024-07-27’; SELECT @Date As [TDate], FORMAT(@Date, ‘dddd’) AS [Day_Name]
In MS SQL Server, we can achieve the pagination functionality by using OFFSET and FETCH clauses with ORDER BY in a SELECT statement. OFFSET: Represents the number of rows to be skipped from the result set. It should be 0 or greater than 0. FETCH: Represents the number of rows to be displayed in the result. Notes: ORDER BY is mandatory for the use OFFSET […]
— Set Identity insert on so that value can be inserted into this column SET IDENTITY_INSERT YourTable ON GO — Insert the record which you want to update with new value in the identity column INSERT INTO YourTable(IdentityCol, otherCol) VALUES(5, ‘myValue’) GO — Delete the old row of which you have inserted a copy (above) […]
–View the current value: DBCC CHECKIDENT (“{table name}”, NORESEED) –Set it to the max value plus one: DBCC CHECKIDENT (“{table name}”, RESEED) –Set it to a spcefic value: DBCC CHECKIDENT (“{table name}”, RESEED, {New Seed Value})
— Applicable for SQL 2005 and later versions USE [Your_DB]; SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like ‘%your_text_to_search%’
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) –First day of previous month SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) –Last Day of previous month
CONCAT function ignores NULLs: SELECT CONCAT(LEFT(LastName, 1), ‘,’ , LEFT(FirstName, 1), ‘ ‘ + LEFT(MiddleName, 1)) theNameWithInitials FROM myTable If you use CONCAT, you will not need to worry about whether LEFT(X) is null or not.
CREATE TABLE #tmpTable ( COL1 int, COL2 int, COL3 nvarchar(max), COL4 nvarchar(max), COL5 bit ) INSERT INTO #tmpTable exec SpGetRecords ‘Params’ NOTE: The columns of #tmpTable must be the same as SpGetRecords. Otherwise, there will be a problem. If there are no parameters in the stored procedure, you don’t need to use ‘Params’.
The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version. The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED […]
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear, DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear Thanks to Jamie F for the answer.
If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]
to fix this problem: remove temp files from %temp% folder (C:\Users<user name>\AppData\Local\Temp) open the register and remove “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” or execute via cmd (with admin rights): reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32 run install with admin rights NOTE: Just for those who don’t know register just open CMD as an […]
The DBCC CHECKIDENT management command is used to reset the identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT (‘[TestTable]’, RESEED, 0); IMPORTANT: The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value […]
To create a single string from multiple rows in MySQL, you can use the GROUP_CONCAT function. This function allows you to concatenate values from multiple rows into a single string, separated by a specified delimiter. Here is an example of how you can use this function: SELECT GROUP_CONCAT(column_name ORDER BY column_name ASC SEPARATOR ‘,’) FROM […]
@@IDENTITY returns the last identity column value created on a connection, regardless of the scope. That means it could return the last identity value you produced, or it could return a value generated by a user-defined function or trigger, possibly one fired because of your insert. In order to access the last identity value created […]