Nice explanation! Maybe just personal preference, but I feel like CTEs are so much easier for my brain to wrap my head around than derived tables. Flow moves top down and reduces indentation. Is there a perf or pref reason to go with inner subqueries?
As I read it, SQL server query planner isn't doing a good job and this hasn't been corrected even in the last version. Other databases do not have this problem as far as I know. And also the explanation doesn't really make sense, why would it generate a nonoptimal plan for that sample or for even the simplest queries with NO PARAMETERS at all. If you think about the sample, it is nonsense to use table scan in the first place. when you could use an index. If I have 100,000 rows and the query would return 99,000 of it well yeah table scan is better, really? How much SQL server would gain not using the index there? That would obviously hurt the searches that would return 10 or 1000 rows and SQL server still chooses this plan, great minds. This is a real query that was causing problem (only field and table names are altered): select Id, myId, myType, myJSON from myTable where IsProcessed=0 and IsValid = 1 order by processingDate asc offset 0 rows fetch next 1000 rows only; This query, returning only 20-30 rows, from a table of 2.5 million rows started to take over a minute, despite there were indexes that SQL server could utilize for this. I wasn't the original author and I fixed this, clearing the plan cache + creating a filtered index on processingDate. The issue is, creating and populating that index didn't even take a second. SQL server should and could do this behind the scenes instead of generating an insanely bad plan. Of course I could claim SQL server query planner is a bug in itself and I could be right, but who am I to question MS - looks like they fail to copy the good parts from open source :)
Extremely useful video! I've been leaning on window functions heavily since I first learned about them, only to now realise that I may have been slowing my queries down drastically. Whoopsie.
Very late to the game... Couldn't get the "animation" working at first. Had to go to Options Search for "Results to Text" Check "Scroll as results are recieved" Fiddle with the result pane size and it's working like a champ! Hope this helps someone.
Video này rất thú vị và hữu ích. Ý tưởng đề cập đến rất sáng tạo và giải thích rõ ràng. Nội dung được thể hiện rất tốt và gây ấn tượng mạnh. Tôi rất thích xem video này. 🌻🌹
hey Bert (or anyone else that knows :)) - at 6:50 you talk about viewing the cach plans by looking at the 'DMV' called dm exec etc etc. What is a DMV in this context? Just trying to understand what those commands are... where do they come from? What is this 'DMV' that you refer to?
Hi Bert, I didn't set up payment in the OpenAI settings but they still gave me an API key. Took me a minute to figure out why the Talkie wasn't working. Working now, thanks!
content that is worth its time :) credits for such valuable materials :) thx!!! btw why You have the Polish emblem (white eagle on red shield) on your laptop? :)
Hiya Bert; I don’t have an alternative/better/likely worse solution to your problem (though I do love these kinds of things!), nor do I even know if you ever come back and read the comments (hopefully so, I’ve watched quite a lot of them and everyone seems mostly positive - which is about the best you can hope for with RU-vid comments). I do just want to say THANK-YOU for taking the time to put these together; they’re VERY well done, concise, span and broad range of SQL topics, provide in-depth knowledge in an understandable fashion, and go far beyond the deluge of basic/beginner topics that so many channels seem to focus on. I’m a dev, and as you know it’s a lifelong commitment to learning about things that change every day and potentially won’t even be relevant in a decade or so (ᕕ( ᐛ )ᕗ), and part of that commitment is reading articles, SO, and watching RU-vid videos to try and keep my knowledge current, refreshed, and hopefully ever-growing. Your videos are phenomenal for that: I can’t find the mental bandwidth after a long day to watch a slow, dry, hour long video on SQL that MAY make some interesting points. The cost/benefit analysis just doesn’t check out. Your videos, on the other hand, are spectacular! They’re fast-paced, you don’t pontificate ad infinitum, and (as mentioned) the content is phenomenal. So, Bert: I say thank-you! I’ve already begun recommended your channel to the coworkers that I like :)
I like the 3rd option, but hate it too and not just for maintenance. The idea of having to put that through peer review and production approval would be lovely to explain to others as well as documenting everything so people know why you have duplicate code.
Temporal SQL and Complex Event Processing (CEP) serve different purposes and are not direct substitutes for each other. Temporal SQL, also known as "time-centric SQL," extends traditional SQL with features for handling temporal data, such as valid-time and transaction-time semantics. It allows querying data based on time intervals, historical snapshots, and temporal relationships. Temporal SQL is useful for scenarios where you need to track changes to data over time, analyze historical trends, or perform time-based queries. On the other hand, Complex Event Processing (CEP) is a technology used for analyzing and correlating streams of events in real-time to identify patterns, detect complex conditions, and trigger actions or alerts. CEP systems typically process high volumes of streaming data from various sources and apply rules or patterns to identify meaningful events or combinations of events. CEP is commonly used in applications such as fraud detection, monitoring IoT devices, and financial trading. While both Temporal SQL and CEP deal with time-related data, they address different aspects of data processing. Temporal SQL focuses on querying and managing temporal data stored in databases, while CEP focuses on real-time analysis and processing of streaming event data. In some cases, you may use both technologies together to handle different aspects of time-sensitive data processing in your applications. ChatGPT verified Used some versioning built in into databases
Thank you for the info, is it an issue if the execution plans are never reused? We have an EHR data base and the execution plans are showing 0 for reuse. Just wondering if that's normal.
this video is really helpful . I have a doubt. If I have defined clustered and non clustered index and we are inserting new row, it will add the newly added row in specific position and shift other rows as per clusterd index. So, how non clustered index is getting benefitted here on inserting new rows. And is that possible to have table without any clustered index.
