SQL SELECT TOP: Common Misuses and Best Practices

Just read through a piece on advanced SQL techniques and realised something: most developers I work with treat SQL like a blunt instrument when it's actually a precision tool. Window functions, CTEs, recursive queries... these aren't fancy tricks. They're the difference between a query that takes 30 seconds and one that takes 30 minutes. I've watched junior devs write absolute nightmares of nested loops in application code when a single SQL window function would've solved it in milliseconds. The thing that gets me is how many teams treat the database like a filing cabinet. They extract everything into the application layer, mangle it with loops and conditionals, then wonder why their systems grind to a halt at scale. I built a reporting dashboard for a client last year that was crawling. Turns out they were pulling 50,000 records into memory and filtering them with C# code. Rewrote it with CTEs and window functions. Same result, 1/100th the load on the server. SQL is boring. That's exactly why it works. And it's exactly why so many developers skip past it looking for something sexier. What's the worst SQL performance issue you've inherited? DM me, I collect these. https://lnkd.in/d3nd2ZU9

🛑Stop "guessing" why your SQL queries are slow. When I first started working in a corporate environment, SQL performance felt like a "black box." Between complex execution plans and the pressure to deliver results in a fast-paced office, it was a lot to take in—especially while navigating professional English as a second language. I’ve spent time documenting the "missing pieces" of the journey from Junior to Intermediate developer. I’ve compiled a series of deep-dive guides using the WideWorldImporters database to help you master the tools that the pros use. Here is what’s waiting for you on the blog: 🔹 Advanced Indexing: Why standard indexes aren't enough and how to use Filtered, Columnstore, and Indexed Views to handle millions of rows. 🔹 Performance Debugging: A step-by-step guide to using SET STATISTICS to see exactly how much I/O your query is "costing" the server. 🔹 Safe Data Operations: How to implement TRY...CATCH and the MERGE statement without the 2 AM "production down" panic. 🔹 Security Fundamentals: A practical look at preventing SQL Injection—because security is every developer's job. Whether you’re an aspiring DBA, a developer looking to optimize your app, or just someone trying to make sense of the corporate data landscape—these guides are built for you. Read the full guides here: 👇 https://lnkd.in/ePutjzxm #SQLServer #TSQL #DataEngineering #Database #WideWorldImporters #PerformanceTuning #TechLearning #CareerGrowth

If you’re learning data, databases can feel intimidating at first. Let’s break the basics down - no jargon, just clarity. A database schema is the formal blueprint or logical structure of a database, defining how data is organized, including tables, fields, relationships, and constraints (data types, keys). Popular relational databases you'll see in the real world - SQL Server - Enterprise-focused, widely used with Microsoft tools - Oracle Database – Large-scale enterprise systems - MySQL – Open-source, common in web apps - PostgreSQL – Open-source, powerful analytics & reliability SQL dialects similarities and differences: SQL is a standard - but each database adds its own flavor: i. T-SQL (SQL Server) – Extra features for enterprise environments ii. PL/SQL (Oracle) – Strong procedural capabilities iii. PostgreSQL SQL – Advanced functions and open-source extensions iv. MySQL SQL – Lightweight and web-focused The core commands like SELECT, FROM, WHERE are shared Advanced features and syntax can vary...Learn one dialect well, and switching others becomes much easier. What is SQL and why does it exist? SQL (Structured Query Language) is a standard language used to communicate with relational databases. Instead of clicking around, SQL lets you ask clear questions like: 👉 “Show me this data” or “Add new records” or “Update these values”. High Level view of SQL commands: DDL (Data Definition Language) - Defines structure (e.g., CREATE, ALTER) DML (Data Manipulation Language) - Works with data (e.g., INSERT, UPDATE) DCL (Data Control Language) - Controls access (e.g., GRANT, REVOKE) TCL (Transaction Control Language) - Manages transactions (e.g., COMMIT, ROLLBACK) Your first real query: SELECT is how you read data (pick these columns and show them to me) FROM is where the data lives (without it, SQL knows what you want - but not where to find it) WHERE is what filters the data (it filters the data before displaying it). SELECT column1, column2 FROM table_name WHERE column1 = value Together, they form the foundation of almost every query. Master these fundamentals and everything else builds on top. 📌 Save this for later ➕ Connect me if you're starting your data journey #SQL #Databases #DataAnalytics #DataFoundations #EarlyCareerData

Mastering SQL pattern matching is key for precise data filtering. The standard `LIKE` operator provides basic string matching with wildcards like `%` and `_`, though it's important to remember its case-insensitive nature in MySQL unless `BINARY` is used. For more sophisticated data interrogation, advanced regular expressions come into play via functions and operators like `REGEXP_LIKE()`, `REGEXP`, and `RLIKE`, offering granular control with special characters such as `^`, `$`, and `.`. These tools are indispensable for developers needing to extract specific data based on complex textual patterns. Explore the full spectrum of SQL pattern matching techniques: https://lnkd.in/gqga6AtF #SQL #Database #PatternMatching #DataEngineering #DeveloperTools

Do you know all the SQL clauses and the correct order they should appear in a query? When working with SQL, it's common to memorize commands like SELECT, WHERE, JOIN, and ORDER BY. But truly understanding the role of each clause and the correct order in which they appear makes a huge difference when writing clean, efficient, and maintainable queries. In general, a standard SQL query follows this structure: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT / OFFSET Each clause has a specific responsibility. The SELECT clause defines which columns or expressions will be returned in the result set. It represents what you want to retrieve. The FROM clause specifies the table (or tables) where the data comes from. It’s the foundation of the query. The JOIN clause is used to combine data from multiple tables. You can use INNER JOIN, LEFT JOIN, RIGHT JOIN, and others depending on your needs. The WHERE clause filters rows before any grouping happens. This is where conditions like comparisons, ranges, null checks, and logical expressions are applied. The GROUP BY clause groups rows that share the same values in specified columns. It is commonly used with aggregation functions like COUNT, SUM, AVG, MAX, and MIN. The HAVING clause filters grouped results. While WHERE filters rows, HAVING filters aggregated groups. The ORDER BY clause sorts the final result set, either ascending or descending. Finally, LIMIT and OFFSET (supported by many databases) are used to control how many rows are returned and to handle pagination. An interesting point is that there is a difference between the written order and the logical execution order of a query. Even though we start with SELECT, the database typically processes the query starting from FROM, then JOIN, applies WHERE, performs GROUP BY, applies HAVING, builds the SELECT, and only then executes ORDER BY and LIMIT. Understanding this helps avoid common mistakes, especially when dealing with aggregations and filters. Writing SQL is not just about making queries that “work.” It’s about understanding intent, improving performance, and making your code easier to read and maintain. The most common order when writing a SQL query is: SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY LIMIT / OFFSET Mastering this sequence is a key step toward writing more professional SQL queries. #SQL #Database #Backend #DataEngineering #SoftwareDevelopment #Programming #Developer

SQL Server functions are one of those things junior devs either love or completely avoid, and I've watched both camps waste enormous amounts of time for different reasons. I was helping a junior recently who'd written the same data transformation logic four times across different queries. Four times! When I asked why he didn't use a function, he went quiet. Turns out nobody had actually explained what they were for or when to use them. Here's the pattern I've seen repeatedly over 15+ years: 1. Scalar functions look easy but they're performance killers in loops. I've seen queries go from 2 seconds to 45 seconds because someone wrapped a calculation in a function without understanding execution plans 2. Table-valued functions (especially inline ones) are genuinely useful and way faster than scalar. But most people don't know the difference, so they default to scalar and wonder why their reporting queries are crawling 3. Built-in functions are your friend. Why write custom logic when SQL Server's already optimised SUM, DATEDIFF, STRING_AGG? The database engine understands them at compile time The real issue isn't that functions are complicated. It's that we throw junior devs at SQL without teaching them *why* performance matters or how to read an execution plan. So they build things that technically work but absolutely tank when you hit real data volumes. If you're mentoring someone new to SQL, take 20 minutes and show them the difference between a scalar function and an inline TVF using actual execution times. That moment when it clicks that their code can be 100x faster? That's when they start thinking like a proper developer. What's your take on SQL functions? Are you using them effectively in your projects, or do you find yourself avoiding them? 👇

SQL Server Query Optimization: 10 Techniques to Slash Response Times Slow queries are the #1 complaint we hear from clients — and most of them are fixable. After managing SQL Server environments for Fortune 500 companies and growing startups, here are the 10 techniques our team reaches for first. 1. Actually read your execution plan. Most developers glance at it looking for red flags; senior DBAs read it like a story. Enable SET STATISTICS IO ON and watch logical reads, not just CPU time. A query doing 50,000 logical reads when it should do 50 tells you everything. 2. Eliminate table scans with covering indexes. If your execution plan shows a table scan on a large table, your WHERE clause columns likely lack an index. A covering index that includes both filter and SELECT columns eliminates key lookups entirely and can cut query times dramatically. 3. Update statistics proactively. SQL Server's optimizer makes decisions based on statistics, and stale statistics produce terrible execution plans. Auto-update triggers at 20% rowcount change — far too slow for large tables. Schedule UPDATE STATISTICS dbo.YourTable WITH FULLSCAN during off-peak hours. 4. Fix parameter sniffing. A stored procedure that flies for one user and crawls for another is a classic sign. The plan compiled for a customer with 10 orders is wrong for one with 500,000. Use OPTION (RECOMPILE) for high-variance queries or OPTIMIZE FOR UNKNOWN for moderate skew. 5. Never wrap indexed columns in functions inside WHERE clauses. WHERE YEAR(OrderDate) = 2024 silently kills index usage. Rewrite it as a range: WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'. 6. Add SET NOCOUNT ON to every stored procedure. Each DML statement sends a "rows affected" message to the client. In a loop with 10,000 iterations, that's 10,000 unnecessary network round trips — eliminated with one line. 7. Batch large DELETE and UPDATE operations. Deleting millions of rows in a single statement locks the table and bloats the transaction log. Loop in chunks of 5,000–10,000 rows with a brief delay between batches to give other processes room to breathe. 8. Use missing index suggestions as a starting point, not gospel. sys.dm_db_missing_index_details is useful, but SQL Server will sometimes suggest 15 indexes when 2 well-designed ones do the same job. Always review impact scores alongside your existing index set before adding anything. 9. Rebuild or reorganize indexes based on actual fragmentation. Over 30%? Rebuild. Between 5–30%? Reorganize. Under 5%? Leave it. Most teams rebuild everything on a fixed schedule regardless of fragmentation — that's wasted I/O and unnecessary blocking. 10. Switch from Profiler to Extended Events. SQL Server Profiler is deprecated and carries significant overhead. Extended Events are lighter, more powerful, and let you target exactly what you're hunting — slow queries, blocking, deadlocks — without capturing a firehose of data.

𝐃𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐈𝐧𝐝𝐞𝐱𝐢𝐧𝐠 – 𝐂𝐨𝐧𝐜𝐞𝐩𝐭𝐬 𝐭𝐨 𝐤𝐧𝐨𝐰 𝐛𝐞𝐟𝐨𝐫𝐞 𝐨𝐩𝐭𝐢𝐦𝐢𝐳𝐢𝐧𝐠. 🔵𝐃𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐄𝐧𝐠𝐢𝐧𝐞:The core software (like InnoDB in MySQL) that manages how data is stored, updated, and retrieved. 🔵 𝐏𝐫𝐢𝐦𝐚𝐫𝐲 𝐊𝐞𝐲: A constraint that uniquely identifies each row. It cannot be NULL and works best as an integer for fast comparisons. 🔵 𝐈𝐧𝐝𝐞𝐱:A data structure (in-memory + on-disk) that maps keys to data locations, speeding up searches by avoiding full table scans. 🟢 𝐃𝐢𝐬𝐤 / 𝐃𝐚𝐭𝐚 𝐁𝐥𝐨𝐜𝐤: The smallest storage unit on disk where rows are stored. Data is read/written in blocks, not individual rows. 🟢 𝐃𝐢𝐬𝐤 𝐈/𝐎: The process of reading data blocks into memory. Indexing reduces the number of disk reads, improving performance. 🟢𝐃𝐌𝐋 𝐎𝐩𝐞𝐫𝐚𝐭𝐢𝐨𝐧𝐬 & 𝐏𝐚𝐠𝐞 𝐒𝐩𝐥𝐢𝐭𝐬: INSERT, UPDATE, DELETE can cause page splits, requiring indexes to update pointers, adding overhead. 🟡 𝐁+ 𝐓𝐫𝐞𝐞:A tree structure used for indexes. Keys are sorted, and leaf nodes store actual data pointers for fast traversal. 🟡𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐞𝐝 𝐈𝐧𝐝𝐞𝐱: Stores data rows in the order of the index key. Only one per table (usually primary key in MySQL). 🟡𝐒𝐞𝐜𝐨𝐧𝐝𝐚𝐫𝐲 𝐈𝐧𝐝𝐞𝐱:Separate structure storing primary key references to locate actual data rows. ⚪ 𝐔𝐧𝐢𝐪𝐮𝐞 𝐈𝐧𝐝𝐞𝐱: Ensures all values are unique but allows NULLs. ⚪ 𝐂𝐨𝐦𝐩𝐨𝐬𝐢𝐭𝐞 𝐈𝐧𝐝𝐞𝐱: Built on multiple columns. Works best when queried in left-to-right order. ⚪ 𝐂𝐨𝐯𝐞𝐫𝐢𝐧𝐠 𝐈𝐧𝐝𝐞𝐱:Contains all required query columns, avoiding extra disk reads. 🟠 𝐏𝐚𝐫𝐭𝐢𝐚𝐥 𝐈𝐧𝐝𝐞𝐱:Indexes only part of a column (e.g., first few characters) to save space. 🟠 𝐂𝐚𝐫𝐝𝐢𝐧𝐚𝐥𝐢𝐭𝐲:Number of unique values in a column. High cardinality = better indexing performance. #Database #DatabaseIndexing #SQL #MySQL #BackendDevelopment #SoftwareEngineering #DataStructures

📊 SQL Concepts I Revisited Recently — and Why They Matter More Than You Think! As someone working in the tech space, I always knew SQL was important. But recently when I sat down to truly practice it, I realised how many small but critical mistakes we make without even noticing. Here are 13 concepts that genuinely changed how I think about writing queries: 1️⃣ NULL is special Never compare NULL with = — it will always fail silently. Always use IS NULL or IS NOT NULL 2️⃣ WHERE vs HAVING WHERE filters rows before grouping. HAVING filters after grouping and only works with aggregate functions. Mixing these up is one of the most common SQL mistakes! 3️⃣ GROUP BY logic Always group by the column you want to summarise across — not the unique ID. Grouping by a unique ID defeats the entire purpose! 4️⃣ Finding duplicates GROUP BY + HAVING COUNT > 1 is your best friend here. Simple but powerful. 5️⃣ Finding missing records LEFT JOIN on the table you want all records from + IS NULL on the right table column — this pattern will save you in real investigations! 6️⃣ Aggregate functions belong in HAVING Never use SUM, COUNT or AVG in WHERE — they only work after grouping happens. 7️⃣ Subqueries When you can't answer something in one step — break it into two. Inner query runs first, outer query uses that result. 8️⃣ CASE WHEN SQL's version of IF/ELSE. Cleaner than you think and incredibly useful for handling NULLs and conditional logic. 9️⃣ SELECT with GROUP BY When using GROUP BY, your SELECT can only have the grouped column or aggregate functions. No individual columns allowed! 🔟 COUNT vs COUNT(DISTINCT) COUNT(column) counts everything including duplicates. COUNT(DISTINCT column) counts only unique values. Know when to use which! 1️⃣1️⃣ JOIN direction matters Always start your JOIN from the table you want all records from — the direction changes your entire result set! 1️⃣2️⃣ When to use which JOIN Want all records from one side → LEFT JOIN Want only matching records → INNER JOIN 1️⃣3️⃣ Finding missing records using JOINs LEFT JOIN on the main table + IS NULL on the right table column = powerful pattern to find records that don't exist in another table! SQL is one of those skills that seems simple on the surface but has so many nuances underneath. The best way to learn it? Practice with real world scenarios, not just textbook examples. What's the one SQL concept that took you the longest to truly understand? Drop it in the comments! 👇 #SQL #DataAnalysis #TechLearning #FinTech #CareerGrowth #LearningInPublic

🚨 Database Series #20 — Stored Procedures Are you still sending raw SQL queries from your application? 😬 That usually leads to: ❌ Repeated logic everywhere ❌ Hard-to-maintain code ❌ Security risks (SQL injection) ❌ Slower performance There’s a better way: Stored Procedures 👇 🧠 Core Concept A Stored Procedure is a pre-written SQL block stored inside the database. Instead of sending full queries every time… 👉 You just call it. Think: 📦 “Reusable database function” 💻 Code Example CREATE PROCEDURE TransferMoney @FromId INT, @ToId INT, @Amount DECIMAL(10,2) AS BEGIN BEGIN TRAN; UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromId; UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToId; COMMIT; END; Call it like this: EXEC TransferMoney 1, 2, 100; 📊 Visual Diagram (Concept) Application ⬇ 📞 Calls Stored Procedure ⬇ 🧠 Database executes pre-defined logic ⬇ ✅ Returns result Instead of: ❌ App sending raw SQL every time ⚡ Why Use Stored Procedures? 🔐 Security ✅ Prevent SQL injection 🚫 No direct table access needed 🔄 Parameterization ➡️ Pass inputs like @UserId, @Amount ➡️ Same logic, different data ⚡ Execution Plan Reuse 🧠 Database caches execution plan 🚀 Faster performance on repeated calls ⚠️ Common Mistake Using stored procedures… but: ❌ Writing complex, unreadable logic inside ❌ Treating them like a dumping ground ❌ Overusing dynamic SQL (kills performance benefits) Result: 💥 Hard debugging 💥 Lost optimization advantages 🎯 Practical Takeaway Use stored procedures when: ✅ Logic is reused frequently ✅ Security is critical ✅ Performance matters Avoid when: ❌ Logic is simple and one-time ❌ You need high flexibility at runtime Balance is key ⚖️ 💬 Question for developers Do you prefer business logic in the database (Stored Procedures) or in the application layer? Why? ➡️ Next in the series: Triggers — automating actions inside the database ⚙️