Comparing Two Execution Plans (2023-11-24)

SQLServerCentral Newsletter for November 24, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Comparing Two Execution Plans]( - [Black Friday returns to SQLServerFast]( - [Unpivoting Multiple Sets of Columns in SQL Server using CROSS APPLY]( - [From the SQL Server Central Blogs - Query Store Reports Time Intervals]( - [From the SQL Server Central Blogs - Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger]( - [Microsoft Power Platform Up and Running: Learn to Analyze Data, Create Solutions, Automate Processes, and Develop Virtual Agents with Low Code Programming]( Question of the Day - [Which Thursday is the Holiday?]( The Voice of the DBA  Black Friday 2023 It's Black Friday, the day after Thanksgiving when many people kick off their Christmas shopping with big sales at various retailers. That's seemed to get [quite crazy at times]( and a number of companies are not running big sales or events on Thursday. Most are still open on Friday, but hopefully, they aren't running too many early morning, low prices that get people to behave poorly. I'm not a big shopper, and many of my Fridays after the Thanksgiving holiday have been spent running volleyball tournaments for the 10-15-year-old kids. I'm hoping that's the plan today, as it is a day off from work. However, it is the Christmas season, and no matter how you feel about gifts and shopping, there are often presents to be bought and exchanged. In the last few years there hasn't been much tech that I've been looking to purchase for myself, as it seems the quality of many devices has them lasting a long time and I don't find myself craving many of the new devices. I did get a new pair of [Bluetooth headphones]( earbuds recently as I lost a previous pair (that were a gift). Other than that, I haven't purchased any tech in quite some time (other than charging cables). Is that the case with any of you? Or are there devices, laptops, or other technology items that you are hoping to receive this year, or maybe something you'll purchase for another. I find myself a little jaded at the state of technology these days and not too excited by any products, but if there is something that's on your list, let me know. Maybe you'll help me get excited about a new advance in some type of technology. The only thing I'm looking at is a new Nest camera. We'd like to get a little more coverage around the property, so I'm debating adding in more [Camrera]( cameras, maybe even a [battery-powered one]( one. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Comparing Two Execution Plans]( Emmitt Albright from SQLServerCentral This is a short look at a technique that I discovered recently in Management Studio (SSMS). The technique is to compare two graphical execution plans in the tool to understand what they are doing and how two different queries might affect a particular system, both with the image and with the properties' data behind the […] [Technical Article]( [Black Friday returns to SQLServerFast]( Additional Articles from SQLServerFast November approaches again. For Europeans, that is a month like any other, for Americans, November means: Black Friday, with huge discounts! [External Article]( [Unpivoting Multiple Sets of Columns in SQL Server using CROSS APPLY]( Additional Articles from MSSQLTips.com Learn how to unpivot data or sets of data with SQL Server queries using CROSS APPLY. [Blog Post]( From the SQL Server Central Blogs - [Query Store Reports Time Intervals]( Grant Fritchey from The Scary DBA A great question came up over at DBA.StackExchange regarding the query store reports time intervals: How can SQL Server’s Query Store Reports show data for minute-length intervals, when “Statistics... [Blog Post]( From the SQL Server Central Blogs - [Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this... [Microsoft Power Platform Up and Running: Learn to Analyze Data, Create Solutions, Automate Processes, and Develop Virtual Agents with Low Code Programming]( Site Owners from SQLServerCentral The book begins with the basics, explaining what low-code and no-code are and showing how to maximize efficiency in creating business applications for one's organization. Next, the book describes the Microsoft Power Platform's foundation, as well as all its components and services. Readers will begin with practical exercises right away, beginning with provisioning a Power Platform environment. Next, the book delves deeper into the Power Platform components such as Power Apps, Power Automate, Power BI, and Power Virtual Agents. Towards the end, the book explains practical exercises for each feature or service where you will gradually build a small business solution for a fictitious organization, Project Wizards, Inc.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Which Thursday is the Holiday? Yesterday was Thanksgiving in the US, which is a major holiday. This holiday is always a Thursday in November. If you were writing code to determine which day is Thanksgiving, which number Thursday in November would you code for? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Further Defining the Window Clause I have defined a window in SQL Server 2022 as follows: SELECT SUM(sod.OrderQty) OVER() AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) In the SUM() aggregate, I want to partition the data. Which of these statements is the way to make a more detailed window over() clause? A: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) B: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID), pb_SalesOrderID_ob_OrderID_ProductID AS (PARTITION BY sod.SalesOrderID ORDER BY sod.SalesOrderID, sod.ProductID) C: SELECT SUM(sod.OrderQty) OVER(partition BY sod.SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) D: SELECT SUM(sod.OrderQty) OVER(partition BY pb_SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (sod.SalesOrderID, sod.ProductID), pb_SalesOrderID AS (sod.SalesOrderID) Answer: A Explanation: A is the correct syntax. Once a window is defined, any parts not included use the defaults. However, after using the window name in the OVER() clause, you can define additional overrides for the defaults. Ref: SELECT - WINDOW - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [UPDATE query is running slow and is causing blocking]( - Hi All, We have an UPDATE query which is causing a lot of blocking in QA env. Its taking more than 4 hours and keep running and we see a waittype as "(151ms)PAGEIOLATCH_SH:QA4_ORS:3(*)". Worst part is, the app team has scheduled this stmt at multiple times of the day. Even before the 1st UPDATE completes, […] SQL Server 2016 - Administration [SQLPS (and SQL Agent PowerShell steps) don't work after Windows upgrade to 2019]( - We were running SQL2016 on Windows 2012R2 without issues. We've just had an OS upgrade to Windows 2019 and now we can't run SQLPS.exe or any SQL Agent PowerShell steps. SQLPS.exe returns: import-module : File E:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see […] [How to solve "The operating system returned error 21"]( - Starting point was a power break with open files etc. in windows. I really do not know where to start.  TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for Server 'DESKTOP-MB6NMGV\EDVARD'. (Microsoft.SqlServer.SmoExtended) For help, click: ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The operating […] SQL Server 2016 - Development and T-SQL [The DATETIME data type question]( - I know that the DATETIME data type is no longer an ANSI/ISO Standard and I have heard the Microsoft plans to eventually stop supporting it but I cannot find any mention of this out on the internet, besides the few comments I came across which I cannot find now. So does anyone know if Microsoft […] SQL Server 2012 - T-SQL [AttachDbFileName in Connection String in Vb.Net code gives errors.]( - While using AttachDbFileName in the connection string to attach database to localdb,The following error occurs Error: The logical database file 'Common WM_log' cannot be found. Specify the full path for the file. Could not open new database 'C:\USERS\SOURCE\REPOS\WINDOWSAPP20\WINDOWSAPP20\BIN\DEBUG\COMMON.MDF'. CREATE DATABASE is aborted. An attempt to attach an auto-named database for file C:\Users\source\repos\WindowsApp20\WindowsApp20\bin\Debug\common.mdf failed. A database […] SQL Server 2019 - Administration [Concerns about database access after migrating to new server - CNAME record]( - Hi all, I'm planning to migrate a database from SQL Server 2008 (Windows Server 2008 R2) to SQL Server 2019 (Windows Server 2019), I've ran the Data Migration Assistant to assess the move and it came back without any issues, I'm also planning on using this tool for the actual migration. There are currently multiple […] [Recovery Point Objective (RPO)]( - Does the following backup strategy satisfy the RPO of 24 hours. these are SIMPLE recovery. no transaction FULL every sunday at 12 am. DIFF daily at 2am  [SPID 0 is blocking some SPID.]( - This is the 1st time I have seen some sessions blocked by SPID 0. Does anyone know what this zero session is? SQL Server 2019 - Development [How to copy from one to another almost identical db]( - I have inherited a demo db called demo_1 and a live db with +300 tables. They are almost identical, but there has been done some work on the live db, which has not been done on the demo. Its missing primary fields, some keys, a few identity fields.  Its pretty easy to copy the […] [Split json column]( - I have a column which has the following data in it for example {"changedAttributes":[{"logicalName":"line1","oldValue":"street1","newValue":"street2"},{"logicalName":"city","oldValue":"City1","newValue":"City2"},{"logicalName":"phone","oldValue":"123","newValue":"345"}]} There maybe 100 logicaname values not just the three here How do I write in sql a query to extract the logicalname, old value and new value for each logical name that exists in the column Lets presume the table is […] Integration Services [How to add a variable to Data Flow task?]( - We have a Data Flow task to load a table from one server to another. But the destination has one extra column which I have to pass from a package variable. How I can add it? In a Mapping tab it has an drop-down option against this column, but how I can insert my […] SQLServerCentral.com Website Issues [HTML on the published article is broken]( - Hey team, I need help with fixing the HTML on my published article. SQL Window Functions Series: RANK() and DENSE_RANK() It is all broken and is providing no value to the reader. Please fix it ASAP SQL Server 2022 - Administration [Table with high unused space]( - I have a table where over 50% has unused space  How can I reclaim this space back? Index maintenance? SQL Server 2022 - Development [Cannot able to login to mysql as localhost]( - I have a query in mysql I have created a account with test@localhost and i want to access the particular database where I have given access to the db like all necessary permissions and all these my sql accounts are hosted in a server and now when i try to login in local mysql workbench […] [Update CSV string with lookup table]( - I need to replace word(s) in CSV string in table tblA, in the [original] column, by [word] from lookup table tblB. The new CSV string will be stored in tblA table, in the column [modified]. If the [tblB].[synonyms] column doesn't contain the word from CSV in tblA, then the original word will be kept. For […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -