Common Table Expression Basics (2023-05-19)

SQLServerCentral Newsletter for May 19, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Common Table Expression Basics]( - [The Journey to Team-based Development with Flyway]( - [REPLACE versus TRANSLATE for SQL Server Text Data Manipulation]( - [From the SQL Server Central Blogs - Reflections on My First-Ever Presentation, Fifteen Years Later]( - [From the SQL Server Central Blogs - Azure Storage Account]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Connecting to Localhost with the new sqlcmd]( The Voice of the DBA  Watashiato [watashiato]( - n. curiosity about the impact you’ve had on the lives of the people you know, wondering which of your harmless actions or long-forgotten words might have altered the plot of their stories in ways you’ll never get to see. I found this word in the [Dictionary of Obscure Sorrows]( which is excerpted at this site. [This book]( (I purchased it) includes new words that describe emotions in great depth. One of the descriptions was the word above, watashiato. Of the few dozen I read, this was the one that stood out to me, and it's something I've experienced. Maybe more than just being curious, though. As someone that does a lot of writing and speaking, I know that my words and actions have an impact on others. I've had many people write to me or tell me in person that I've affected them. Usually, this is very positive, and I'm grateful for the kind words. I am proud and happy for the positive impacts I've had in people's lives. However, I know I must have had negative impacts. I'm sure I've written or said something incorrect that upset someone, or messed up their day at work, or perhaps caused an outage and they had to work late. I hope I haven't gotten anyone terminated, or even chastised from management for something they learned at SQL Server Central, but I don't know. I try not to dwell on this, but at times I think about whether or not I've changed someone's life for the worse. I might have treated someone poorly. I know I did early in my 20s, as an arrogant young man, but I'm sure I've done things that meant nothing to me but affected others. Perhaps even deeply. My life has gone well, my career is a success, and I am grateful for that. I try to appreciate the small moments in life, and embrace dés vu (the awareness that this moment will become a memory). My wife had trumspringa and made it come true, leaving her corporate job to train horses. I've watched others do the same thing. These days my goal is to live my life in a way that makes the world better. I work, I need a paycheck, but I try not to be driven by that. I appreciate time with loved ones and experiences. I appreciate the conversations, the handshakes, and the hugs. I look to enjoy the laughter with others and the hard times, appreciating them as a part of life. I try to leave the world a little better than I found it, and hopefully, lessen the chances of future sorrowful watashiato. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Common Table Expression Basics]( sabyda from SQLServerCentral Learn the basics of how CTEs work with a few examples in this article. [External Article]( [The Journey to Team-based Development with Flyway]( Additional Articles from Redgate This article explains why Flyway is fundamentally well-suited to the task of bringing control and automation to database development work and then the features of Flyway Teams edition that become necessary when a team of developers need to work concurrently on a database. [External Article]( [REPLACE versus TRANSLATE for SQL Server Text Data Manipulation]( Additional Articles from MSSQLTips.com Learn how to replace text in SQL Server strings using the REPLACE and TRANSLATE functions and how these could be used for data manipulation. [Blog Post]( From the SQL Server Central Blogs - [Reflections on My First-Ever Presentation, Fifteen Years Later]( Tim Mitchell from Tim Mitchell May 3rd represents a small but significant milestone in my career. It was 15 years ago today, on May 3, 2008, when I delivered my first public technical presentation.... [Blog Post]( From the SQL Server Central Blogs - [Azure Storage Account]( dharmendra.keshari from SQL Geek This blog post aims to provide an overview of Azure Storage Accounts. It will explain how to create them and use them effectively. Azure Storage Account Azure Storage Account... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Connecting to Localhost with the new sqlcmd I want to use the new sqlcmd, written in Go, to connect to my local SQL Server instance using localhost. I do not want this to fall back to the old sqlcmd. How can I do this and query AdventureWorks. 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) The MI Startup Time I can stop and start an Azure SQL Managed Instance. As of Mar 2023, with this feature, what is the estimated startup time for a Managed Instance that is stopped? Answer: 20 minutes Explanation: The estimated time to start a stopped instance is 20 minutes. Ref: Stop and start an instance (preview) - Azure SQL Managed Instance - [ [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 - Development [Populate fields where CSV Sting has data matching given Column name]( - Given multiple ROWs comprised of varying combinations of Comma delineated values and an ID. Given a TABLE whose COLUMNS names are a comprehensive compilation of the (SPERATED/PARCSED) delineated values existing between all ROWs. Populate the given TABLE by looping through the COLUMN names and PARCING a given ROW for a delineated value that MATCHES a […] [sql time zone conversions]( - hi folks i need to convert a date/time field between two time zones the date/time stamp is in GMT Standard Time +01:00 which is correct but i want to convert it to UTC +00:00 - as the app we are using wants to manage the time series data and zones so 2023-04-05 23:59:43.410 GMT should […] SQL Server 2016 - Administration [How to view actual execution plan, for long running jobs?]( - I have a couple of very long running jobs which are scheduled by SQL Agent. These jobs are doing archiving task & runs to several hours. I would want to view the execution plan for the jobs to see what can be improve. May I know how can I view the actual execution plans for […] SQL Server 2016 - Development and T-SQL [Index Spool Eager Pool issue]( - I have an archiving job running on our production database which will move old datas from an existing production DB to another archiving DB running on the same box. The job utilize on MERGE INTO operations to do the archiving. This job isn't a new job and has been running for quite some time. Users […] Development - SQL Server 2014 [Dirty Reads with Read Committed?]( - Hey everyone! I have a SQL agent job that deletes data then inserts data into the same table hourly. Both the delete and insert command are wrapped in the same Begin/Commit/Rollback Transaction statement and the process takes about 30 seconds to complete. I have another job that on occasion reads data during this update and […] SQL 2012 - General [SSL Certificate]( - Hi.. SSL certificate has been installed in our standalone and cluster servers . and from Registry Editor , I can see the Certificate number and force encryption is zero ( screenshot is attached) Yesterday after the restart our standalone server , I can see Message in error Log as below : The certificate [Cert Hash(sha1) […] SQL Server 2019 - Administration [Configure replication with Always-On (Basic Availability group)]( - Hi Folks We have configured Basic Availabilty group and now we need to configure replication . Scenario is this sev 1 and sev 2 are in basic availability group. Database is sev1 would initially act as publisher .sev3 would acts as subscriber and our distributor database would be on remoter server sev4. So i would […] [How to run SSMS as non Admin and restart Services]( - I have a user that needs full access to a specific instance on our SQL Server (2019). I do not want this user to have local or domain admin prividleges. I have gotten SSMS on his pc and have given him a domain account (localAdmin1) to use that does have local admin prividleges on his […] [Move SQL Server users from one RDS for SQL Server instance to another]( - Hi, How can we move SQL Server logins from one RDS for SQL Server instance to another ? [Restore further T-Log Backups]( - Hi, is it possible, to restore more t-log backups, after I restored the database? We got a problem, and we had to restore the database, then check the table in the database. Maybe we did the point in time restore to early, and I'd like to restore some more t-log backups, without the restore of […] [Blocking alert]( - Hi All, I am trying to capture and set up alert for blocking when it goes more than 30 seconds. Is the following correct OR maybe i need to do with wait time. select @@servername [SERVER],sp.spid,sp.blocked, (CONVERT(DATETIME,CAST(sp.last_batch AS CHAR(8)),101)) as last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, sp.dbid, sp.loginame,sp.hostname,sp.cpu from master.dbo.sysprocesses sp where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(Second,last_batch,GETDATE())>30 0 […] SQL Server 2019 - Development [Index help - Performance issue]( - This table contains contains 127 M rows and growing. We are starting to see some performance issues on any query that touch this table.  There is a columnsotore index on it including clustered and several non-clustered indexes. Do you notice duplicate indexes and/or t wide indexes? Any suggestions on what can be removed or […] [Employee count based on target date]( - I have table for employee activity with the following fields: [Emp ID] , [Hire Date] , [Term Date] , [Mod Date] , [Old Div] , [New Div] When an employee is hired, [Emp ID] is filled, [Hire Date] is filled with the current date and [Old Div] and [New Div] are filled with the division […] [how to get previous year and current year data in coma separated values in sql]( - Hi Team, I have data like below and expected output is like this. --Expected o/p --Jan-2022,Jan-2023,January-2022,01-01-2023,Jan-2023,Jan-2023 Can you please help me on this. USE [Tempdb] GO /****** Object: Table [dbo].[tempIssueRange] Script Date: 09-05-2023 18:53:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tempIssueRange]( [IssueID] [int] NULL, [IssueName] [varchar](110) NULL, [IssueDate] [date] NULL […] Azure Data Factory [Monitoring ADF best practices]( - All,  I know that within Azure Data Factory, there is a great monitoring section to see what was successful, what failed, and you can get super granular with this and dig into individual runs. My question here is, can we integrate these logs with Azure Application Insights? If so, has anyone done this and […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -