Erwin Ramburger, CFA

🔍 Understanding Clustered and Non-Clustered Indexes in SQL Server 🔍 Indexes are crucial for improving the performance of database queries, but not all indexes are created equal. Two fundamental types are clustered indexes and non-clustered indexes. Let’s dive into what they are, their syntax, and when to use them! 🌲 Clustered Index Definition: A clustered index determines the physical order of data in a table. In fact, a table can have only one clustered index because the data rows themselves can only be sorted in one way. Characteristics: The clustered index key is the same as the data in the table. It can significantly speed up range queries and retrieval of data since the rows are stored in order. Commonly used on primary keys. Example: Imagine a table of employee records sorted by EmployeeID. A clustered index on EmployeeID means that the data rows are physically stored in order based on this ID. 📑 Non-Clustered Index Definition: A non-clustered index creates a separate structure that references the data rows in the table. This means a table can have multiple non-clustered indexes. Characteristics: Non-clustered indexes are stored separately from the data, containing pointers to the data rows. They improve query performance for specific queries, especially those involving filtering and searching. Ideal for columns frequently used in WHERE clauses or JOIN operations. Example: In the same employee records table, if you create a non-clustered index on the LastName column, SQL Server creates a separate index that allows quick lookups of employees by their last names, but it doesn’t affect the physical order of the actual table rows. 🚀 When to Use Which? Use a clustered index when: The table is frequently queried in a sorted order. You want to optimize range queries. Use a non-clustered index when: You need to optimize queries involving specific columns for searching or filtering. You have multiple queries that benefit from different indexing strategies. 💡 Conclusion Understanding the differences between clustered and non-clustered indexes can lead to better performance tuning of your SQL Server databases. Always analyze your query patterns to decide the best indexing strategy for your tables! Do you have any experiences or tips regarding indexes? Share your thoughts in the comments! 💬

5

1 Kommentar

How good are you in SQL ??? 🤔 best way to master SQL is solving good questions! Lets try a simple yet interesting problem… I have mentioned the problem statement in simple way with 2 different solution method in attachment. 📎 Try to solve first by your own (honestly it worth trying) let me know your approaches in the comments. 🚀 Table creation and Data Insert statements are metioned in my first comment. 👇 Thanks to Ankit Bansal for such great content!! 🔥 For better understanding checkout the video on his YouTube channel (link is in comments) 🔗 Follow Yash Bhawsar for more insights, tips, and resources to help you on your data engineering journey. Let’s build the future with data! 🌟 Keep Learning 😊 #sql #sqlquesries #knowledgesharing #practice

60

3 Kommentare

Comparison operators in SQL: SQL Comparison Operators are used to compare two values and check if they meet the specific criteria. Some comparison operators are = Equal to, > Greater than, < Less than, etc. Equal to (=) Operator: It returns the rows/tuples which have the value of the attribute equal to the given value. Equal to (=) Operator Example: SELECT * FROM MATHS WHERE MARKS=50; Greater than (>) Operator: It returns the rows/tuples which have the value of the attribute greater than the given value. Greater than (>) Operator Example: SELECT * FROM MATHS WHERE MARKS>60; Less than (<) Operator: It returns the rows/tuples which have the value of the attribute lesser than the given value. Less than (<) Operator Example: SELECT * FROM MATHS WHERE MARKS<40; Greater than or equal to (>=) Operator: It returns the rows/tuples which have the value of the attribute greater or equal to the given value. >= Greater than or equal to Operator Example: SELECT * FROM MATHS WHERE MARKS>=80; Less than or equal to (<=) Operator: It returns the rows/tuples which have the value of the attribute less or equal to the given value. <= Less than or equal to Operator Example: SELECT * FROM MATHS WHERE MARKS<=30; Not equal to (<>) Operator: It returns the rows/tuples which have the value of the attribute that is not equal to the given value. Not (<>) equal to Operator Example: SELECT * FROM MATHS WHERE MARKS<>70;

2

Delta Lake uses a Multi-Version Concurrency Control (MVCC) approach to ensure data consistency and isolation during concurrent write operations. This is a fundamental concept that underpins Delta Lake's transactional guarantees. Here's how it works: 1. Transaction Logs: Delta Lake stores all the changes to a table in a transaction log, which is a series of JSON files in the `_delta_log` directory. Each write operation creates a new transaction file. 2. Isolation Levels: Delta Lake supports different isolation levels, similar to traditional databases: - Serializable: The highest isolation level, where concurrent writes are completely isolated from each other. - Snapshot Isolation: Readers see a consistent snapshot of the data, while writers are isolated from each other. - Read Committed: Readers see only committed data, while writers are isolated from each other. 3. Commit Protocol: Delta Lake uses a two-phase commit protocol to ensure atomicity and consistency during writes: - Phase 1: The writer appends a new transaction to the transaction log, but the data files are not yet committed. - Phase 2: The writer atomically commits the data files and the transaction log entry. 4. Conflict Resolution: If a concurrent writer attempts to modify the same data as an ongoing write, Delta Lake uses a conflict resolution strategy: - Serializable: The second write fails with a `ConcurrentAppendException`. - Snapshot Isolation: The second write creates a new version of the data, which can be read by future transactions. - **Read Committed**: The second write overwrites the first write. 5. Metadata Management: Delta Lake maintains consistent metadata about the table, including the list of data files, their location, and their schema. This metadata is also managed transactionally.

6

 Leveraging Indexes for Faster Data Retrieval ⚡ Indexes are essential for enhancing the speed and efficiency of data retrieval in SQL databases. By creating indexes on frequently queried columns, you can significantly reduce query execution times and improve overall database performance. However, it's important to balance indexing with storage considerations and update overhead to maintain optimal system performance.

2

Are you a TOP or LIMIT kind of person? TOP is another way that SQL Server deviates from most other databases, but it has some neat options that LIMIT often doesn’t have The syntax for SQL Server’s TOP is: TOP (expr) [ PERCENT ] [ WITH TIES ] This implementation makes it easy to select a percentage of rows, and to keep ties when you use TOP with an ORDER BY Note that not all DBs with TOP support these options. Snowflake, for example, supports both TOP and LIMIT, but its TOP doesn’t have the PERCENT or WITH TIES options In the other databases that I work with (listed below), the LIMIT clause doesn’t have the same options: you can only specify an expression that returns a number after LIMIT The only exception is DuckDB, which supports the PERCENT option too The TOP/LIMIT support for the DBs I work with is noted below • 𝗦𝗻𝗼𝘄𝗳𝗹𝗮𝗸𝗲: supports TOP (without the SQL Server options) and LIMIT • 𝗗𝘂𝗰𝗸𝗗𝗕: supports LIMIT with the PERCENT option • 𝗣𝗼𝘀𝘁𝗴𝗿𝗲𝗦𝗤𝗟: supports LIMIT only • 𝗦𝗤𝗟 𝗦𝗲𝗿𝘃𝗲𝗿: supports TOP with the options mentioned above • 𝗦𝗤𝗟𝗶𝘁𝗲: supports LIMIT only It’s not common to need a percentage of rows and it’s easy to resolve ties using a window function like DENSE_RANK, but these can be convenient options in the rare case you do need them Since some DBs only support TOP, some only support LIMIT, and some support both, which one do you think is the SQL standard? The answer might surprise you — and is the subject of my next post (link in the comments) 😉 Have a guess at what you think the SQL standard is for keeping a selection of rows #sqlwithbill #sql

25

1 Kommentar

Hey, I made a vid about > How to write in SQL < . In this vid, I'll be talking bout how to insert data into a table in a database. I'll also go over how to delete, how to update, and how to import data into a database using SQLite. Like, if ya wanna import CSV data into your database as a table, I got you covered. So yeah, check it out here >> https://lnkd.in/eTbv8rgS `#SQLBasics #SQLite #DataImport #DatabaseTutorial`

1

INTERVAL should be your go-to for any kind of date/time manipulation It’s not in all SQL databases, but for the ones that it is, you can usually use it a few ways (depending on the DB): 1. In “normal” datetime manipulation 2. In a window frame 3. As a column type As an opinionated individual, I’ve always found it more intuitive to use INTERVAL over something like SQL Server’s DATEADD function. Compare: SELECT CURRENT_TIMESTAMP + INTERVAL '1 DAY 12 HOURS 5 MINUTES' …to: SELECT DATEADD(MINUTE, 5, DATEADD(HOUR, 12, DATEADD(DAY, 1, CURRENT_TIMESTAMP))) INTERVAL is even more helpful when it comes to window frames, as you can specify INTERVAL in a RANGE frame: SELECT COUNT(*) OVER (ORDER BY EVENT_DATETIME RANGE INTERVAL '1 HOUR' PRECEDING) There are workarounds for databases without INTERVAL, but they’re generally not as simple or intuitive Using INTERVAL as a column type is, for me, helpful for semantics and flexibility — you could just use a TIMESTAMP column for a lot of values, but INTERVAL implies that the column content is a delta and not a point in time It’s also easy to fit multiple units into a single INTERVAL value, like “2 days” (how would you represent that as a TIMESTAMP?) I liken it to the difference between datetime.datetime and datetime.timedelta in Python I’d recommend generally using INTERVAL rather than datetime functions, if you can — it’s cleaner, you can whack it into a window frame, and you can use it as a column type INTERVAL has the following availability for the DBs I work with: • Snowflake ✅ • DuckDB ✅ • PostgreSQL ✅ • SQL Server ❌ • SQLite ❌ Where have you used INTERVAL before? #sqlwithbill #sql

22

2 Kommentare

Datawarehouse Vs Datalake!! Interviewer: let's discuss the differences between data warehouses and data lakes. Are you familiar with these concepts?" Candidate: "Yes, I've worked with both data warehouses and data lakes. They serve as repositories for storing and analyzing large volumes of data, but they have different architectures and use cases." Interviewer: "That's correct! Let's start with data warehouses. How would you define a data warehouse, and what are its key characteristics?" Candidate: "A data warehouse is a centralized repository that stores structured and processed data from various sources. It's optimized for querying and analysis, typically using a schema-on-write approach, where data is structured and organized before being loaded into the warehouse. Data warehouses are designed for supporting business intelligence (BI) and analytics applications, providing fast and reliable access to historical data." Interviewer: " How do data lakes differ from data warehouses, and what are their primary characteristics?" Candidate: "Unlike data warehouses, data lakes store raw, unstructured, or semi-structured data in its native format. They use a schema-on-read approach, where data is ingested without prior structuring, allowing for flexible exploration and analysis. Data lakes are designed to store vast amounts of data at a low cost and support a wide range of data processing and analytics use cases, including data exploration, machine learning, and advanced analytics." Interviewer: "Spot on! Now, let's discuss some key differences between data warehouses and data lakes. What are some factors to consider when deciding between these two architectures?" Candidate: "One significant difference is their approach to data organization and schema management. Data warehouses enforce structure and schema upfront, making them suitable for well-defined, structured data. On the other hand, data lakes offer flexibility and agility, enabling organizations to store diverse data types and schemaless data. Interviewer: "Excellent points! Both data warehouses and data lakes have their strengths and use cases. Now, let's consider a hypothetical scenario: Suppose a company wants to implement a data platform to support both real-time analytics and ad-hoc exploration of raw data. Which approach—data warehouse or data lake—would you recommend, and why?" Candidate: "In this scenario, I would recommend leveraging a data lake architecture. A data lake would provide the flexibility and scalability needed to handle real-time streaming data and accommodate ad-hoc exploration of raw data. With a data lake, the company can ingest data from various sources without worrying about upfront schema definition, enabling faster data ingestion and exploration. Additionally, the company can leverage technologies like Apache Spark or Apache Flink for real-time analytics and processing of streaming data." #dwh #datalake

78

3 Kommentare

🔥 📢 New Article alert "Mastering Coroutine Scopes in Kotlin: A Beginner’s Guide (Part 5)" 📢 🔥 https://lnkd.in/dv-dnzYt #backtocoding #kotlin #android #programming #techinterviews #KotlinInterviewPrep #kotlindevelopers #coroutines #kotlincoroutines

1

💻 Antares SQL Client: A cutting-edge, productivity-focused SQL client designed with a strong emphasis on UX. 🔗 https://lnkd.in/eVv5VxCw Key features include: - Multi-database connections - Full database management (add/edit/delete) - Comprehensive table management (indexes & foreign keys) - Handling views, triggers, procedures, functions, and schedulers - Modern tab system for a clutter-free workspace - Test data generation - Query autocomplete & history (search up to 1000 queries) - Save queries, notes, tasks - SSH tunnel support - Manual transaction execution - Import/export database dumps - Customizable hotkeys - Light & dark themes, and editor themes #SQL #DatabaseManagement #TechTools

2

How to build a strong data projects portfolio? In the last few months, I have seen so many people putting in a lot of work to showcase their data skills with portfolio projects. Here are a few observations and feedback that might help. Common mistakes: - No clear problem statement - Projects with just lists of SQL queries or Python code (great for learning, but not resume-worthy) - Dashboards full of charts and colours, but no narrative - Insights that are not actionable and recommendations that are too generic - Using extremely common datasets - Answering ready questions Some tips to improve the quality of your projects: - Start with a real problem - Define it, break it down and make recommendations. Make it truly end-to-end. - Come up with questions to answer yourself - Show how you translated business questions into data questions. - Show your range of skills - Combine ad-hoc analysis, modelling and dashboards. - Present data in a way that tells a story - Code is just a part of your project. The story is what creates impact. It’s hard to do all of this together especially if you are just starting out. But once you grasp the easy stuff, go beyond the basics to stand out!

34

6 Kommentare

How SQL Aggregations Work Internally to Efficiently Process Millions of Rows? In the attached image, 2.9M users have rated "The Shawshank Redemption" on IMDb. When I rated the movie, my rating updated instantly in the "Your Rating" section, but the average rating in the "IMDb Rating" section remained unchanged. This suggests that the rating update is processed asynchronously, possibly using a Kafka event or a task scheduler like Celery. In a previous post, I shared how to design the schema for calculating the rating of an IMDb movie rated by millions of users, achieving aggregation for 1M users in 470ms. https://lnkd.in/g_Sg3dmp Consider the following SQL statement: SELECT SQL_NO_CACHE AVG(rating) AS average_rating FROM Ratings WHERE movie_id = 1; Using SQL_NO_CACHE prevents the results of the query from being stored in the cache. This query executes in 470ms despite fetching millions of ratings. Here's a detailed look at how this process works: 1. Index Lookup: - The database uses the index on movie_id to quickly find all rows where movie_id = 1. - The index acts like a map, allowing the database to locate relevant rows without scanning the entire table. Learn more about indexing: https://lnkd.in/gVJqrzx3 2. Row Fetching: - After locating the relevant rows, the database fetches the rating values. - This involves reading data from disk (if not already in memory) and loading it into memory for processing. 3. Aggregation Calculation: - The database engine sums up the rating values and keeps a count of the number of rows processed. - This is done iteratively but efficiently due to indexing and database optimizations. 4. Final Computation: - Once all relevant rows are processed, the database divides the total sum of ratings by the count of rows to compute the average. Efficiency Considerations: - Batch Processing: The database processes data in batches, reading and processing multiple rows together to reduce overhead and improve performance. - Index Efficiency: Using an index minimizes the number of rows examined by quickly narrowing down to the relevant rows. - In-Memory Operations: Much of the aggregation calculation happens in memory, which is significantly faster than disk operations. Using EXPLAIN to Understand Query Execution: EXPLAIN SELECT AVG(rating) AS average_rating FROM Ratings WHERE movie_id = 1; This will show: - Whether the index is being used (idx_movie_id). - The number of rows expected to be scanned. - The type of operations performed (index scan, table scan, etc.). While the database conceptually fetches data one row at a time, it optimizes this process through batch processing, indexing, and in-memory operations. Redis can be used to cache frequently accessed data. This ensures that even with large datasets, aggregation queries like calculating the average rating remain efficient and performant. #softwaredevelopment #softwareengineering #applicationdevelopment #database #design #mysql #aggregations #indexing

2

A simple SQL question that can catch you off guard. What is the difference between a 𝗝𝗢𝗜𝗡 and a 𝗨𝗡𝗜𝗢𝗡? A JOIN and a UNION are both used to combine data from multiple tables in SQL. But they serve different purposes and have distinct ways of working. 𝗝𝗢𝗜𝗡 𝗖𝗼𝗺𝗯𝗶𝗻𝗲𝘀 𝗰𝗼𝗹𝘂𝗺𝗻𝘀 from two or more tables based on a related column (typically a primary-foreign key relationship). Rows are combined side-by-side. Example types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN. The resulting dataset has columns from both tables. Use JOIN when you want to add more columns by connecting related rows across tables. 𝗨𝗡𝗜𝗢𝗡 𝗖𝗼𝗺𝗯𝗶𝗻𝗲𝘀 𝗿𝗼𝘄𝘀 from two or more queries into a single result set. Rows are added vertically. The queries combined must have the same number of columns with compatible data types and similar column names. UNION removes duplicate rows by default, while UNION ALL keeps all rows, including duplicates. Use UNION when you want to append rows from two or more result sets that share the same structure. 𝗧𝗟;𝗗𝗥 • 𝗝𝗢𝗜𝗡: Combines tables by adding columns, linking data from multiple tables based on relationships. • 𝗨𝗡𝗜𝗢𝗡: Combines result sets by adding rows, merging data with the same structure from multiple queries. 𝗖𝗼𝗺𝗺𝗼𝗻 𝗚𝗼𝘁𝗰𝗵𝗮: If you're asked to merge data from different tables, it's easy to think of JOIN when you need a UNION. • If it's about relationships between rows, use a JOIN. • If it's about merging similar data sets, use a UNION.

293

58 Kommentare

For queries with multi-field range conditions, Firestore may perform a partial index scan affecting performance and cost. 💡 Some SDKs now can explain how a query *will be* executed, and show a detailed analysis when it *has been* executed. Learn more → https://goo.gle/3Uqsqoc

13