What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples (2023-10-20)

SQLServerCentral Newsletter for October 20, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples]( - [Query processing architecture guide]( - [Azure Databricks Tables - Delta Lake, Hive Metastore, TempViews, Managed, External]( - [From the SQL Server Central Blogs - How to Downgrade Or Recover a SQL Server Database To A Previous Version Using SqlPackage.exe?]( - [From the SQL Server Central Blogs - SQL Prompt Quick Access to Helper Code]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( The Voice of the DBA  Declining Work for Mental Health This year has been a busy one for me. I had 30 trips on airplanes away from home and around 100 nights not in my own bed. It's been quite a year, and a little overloaded. There have been a few months were I traveled little, but more than a few where I was gone more than I was home. I spent 19 nights away from home in September. A few weeks back, I realized that I had another long trip coming up in October (9 days) and the PASS Data Community Summit in November. My body is a little worn out, but my mind is also distracted and struggling to focus. I recognized the beginnings of burnout when I realized that I was slightly dreading the Summit trip. While I want to see friends and catch up, I wasn't looking for a long trip. On the spur of the moment, actually while sitting in an airport and waiting for a flight, I sent a note to the SQL Saturday Oregon organizers asking them to withdraw my speaking submission and not select me. I felt bad for doing so as I try not to create work for others or submit to events without the intention to go, but the extra 2 days in Seattle would make lift hard, and I thought this was the best choice for my mental health. I also chatted with my boss a bit about some work opportunities that were coming up and decided to decline a few other things, as well as skip conference submissions in Q1 2024. I'm not burned out, but I do recognize that I'm overloading my body a bit. Physically it's a bit worn out and I can see that in my frequency and effort at the gym. Mentally I'm slightly dropping the ball on a few things, working slower than I often do because I find myself slightly distracted and looking for more breaks. One of the big learnings for me over the pandemic was that I need to ensure I take care of myself and treat myself as I'd treat someone else in similar circumstances. I've had coworkers and friends struggle at times in life, where I've made an effort to work more and help them work less. I need to remember that for myself and ensure I can lean on others where needed. Redgate is an amazing employer and everyone is willing to flex with each other to ensure work gets re-distributed or we move deadlines. My manager and I have discussed this, and we are monitoring my workload moving forward in our regular meetings. One of the best things I've learned over the years is to say no. I say no a lot to various queries on my time. Often when I feel I can't deliver on time. This year I was looking forward to these opportunities, perhaps a bit too much, and ran myself down a bit with over-excitement. I recognized that and made an effort to take steps to help myself before I burned out or started missing commitments. The last month (and half of the next one) has been a bit rough, but I've mentally prepared myself for the work, and seeing relief coming in a couple of weeks is helpful. I've learned a bit more about myself this year, especially in relation to my slightly changing job and hopefully, I'll do a better job managing myself next year (or my boss will). Learning to balance work and life is important. Remembering that life, family, friends, hobbies, faith, etc. are more valuable than work, and we need to ensure we don't overwork ourselves makes us better employees and better people. Any of us can get overloaded in the short term, but don't let that become an accepted part of your life. Your employer may push, but learn to push back and protect your physical and mental health. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples]( Parthprajapati from SQLServerCentral Learn how to use CTEs through the use of a number of examples. [External Article]( [Query processing architecture guide]( Additional Articles from Microsoft MSDN The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following sections cover how SQL Server processes queries and optimizes query reuse through execution plan caching. [External Article]( [Azure Databricks Tables - Delta Lake, Hive Metastore, TempViews, Managed, External]( Additional Articles from MSSQLTips.com Learn about the different tables that can be created using Azure Databricks with a dive deep into the importance of Delta Lake tables. [Blog Post]( From the SQL Server Central Blogs - [How to Downgrade Or Recover a SQL Server Database To A Previous Version Using SqlPackage.exe?]( SQLPals from Mission: SQL Homeostasis How to Downgrade a SQL Server Database To A Previous Version? Upgrading a SQL Server database to a newer version is one of the core duties... [Blog Post]( From the SQL Server Central Blogs - [SQL Prompt Quick Access to Helper Code]( Steve Jones - SSC Editor from The Voice of the DBA We’ve been doing some events as part of the Redgate Roadshow, and at one of the events, we had a customer ask about something that we demo’d. This post... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.      Yesterday's Question of the Day (by Steve Jones - SSC Editor) The Basic Symmetric Key Which of these options (multiple) are needed to create a symmetric key with the CREATE SYMMETRIC KEY command in SQL Server 2022? Answer: ALGORITHM, ENCRYPTION BY Explanation: The two options needed for CREATE SYMMETRIC KEY are the algorithm and the encryption by. There is no default algorithm, and the key must be encrypted by some method. Ref: CREATE SYMMETRIC KEY - [ [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 2016 - Administration [Monitoring Transaction Log usage?]( - I'm curious is there any way for me to monitor the TLog generation in my database? I am trying to pin point which sessions/SQL queries which generated huge amount of transaction log. I have alerts to notify if log space is running out, but I'm trying to find out which particular session or which query […] SQL Server 2016 - Development and T-SQL [Can LEAD and LAG skip rows or can we add a where clause?]( - Hello, I have a dataset of route data (this is just a single route) like below: CREATE TABLE [dbo].[___routeSampleData]( [costCenterCode] [varchar](12) NULL, [scheduleDate] [date] NULL, [tripid] [int] NOT NULL, [routeId] [int] NULL, [routename] [varchar](254) NULL, [activityId] [int] NULL, [isFirstStop] [int] NOT NULL, [isLastStop] [int] NOT NULL, [eventOrder] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[___routeSampleData] […] Administration - SQL Server 2014 [The Certificate specified for backup encryption has expired]( - Hi Trying to backup Database encrypting the bak file with a certificate. The certificate has expired by looking at SELECT * FROM sys.certificates the certificate expired almost 3 years ago. It has been running this back using the expired certificate for almost 3 years! I read on numerous posts SQL server ignores certificate expiry_date. Before […] SQL Server 2012 - T-SQL [query to find the date/s that each user missing to signed in his attendance]( - Good Day Everybody, I've missing something in this query but I can't catch it, it keeps give me error: Msg 102, Level 15, State 1, Line 36 Incorrect syntax near ')'. the query to find the date/s that each user not signed in his attendance and not requested as leave moreover not a holiday nor […] SQL Server 2019 - Development [Is it always better to redo a database, when upgrading an application?]( - Today's editorial (Modern Development) got me thinking about a related topic, but I didn't want to detract from that discussion, so I'm asking here. I work in a large state department. We've got hundreds of applications and I think hundreds of databases. Over the last 6 years I've seen 8 projects started, all using an […] [Splitting delimited values across multiple columns on same row.]( - Hi All, Not sure If I'm over complicating the process but I'm struggling to write or find a solution on line that works exactly how I expect to see the results. I have found one solution but there are limitations on the PARSENAME when over 4 values. Below is just copy pasted a solution from […] [SQL Server converting "IN" to "OR" condition internally]( - I have a TFS script with Check constraint " Check Col1 in (1,2)" when deployed to database its changing the script to "Check Col1 =1 or Col1 =2 " which is correct as well but everytime i want to generate delta for any deployments its coming up all the time. Any setting which can help […] SQL Azure - Development [What's the connection string I should use for SSMS?]( - Now that I've got an AdventureWorks database in an Azure SQL database, I want to connect to it with SSMS and eventually with an ASP.NET web app using Entity Framework. But for the moment, let's keep it to SSMS. I've gone to the Azure portal, opened the SQL resource and found the page for various […] Reporting Services [SSRS server randomly get disconnected (sql server 2016)]( - I have a problem with my SSRS server that randomly can't connect to my data source. It works all the time but sometimes I get an error in my reports that indicate that the server failed to connect. Any tips or leads to identify the source of this problem? Here is my connection string: Data […] General [Fetch API in Node.js: Making HTTP Requests]( -  I'm working on a Node.js application where I need to make HTTP requests to external APIs. While I'm familiar with using the Fetch API in browsers, I'm not sure how to use it in a Node.js environment. Here's a simplified example of what I want to do: const fetch = require('node-fetch'); async function fetchData() […] SQL Server 2022 - Administration [Replication]( - My prod is already part of the Always On Availability Group in On-Prem then Azure SQL Managed instance link to SQL MI will not work. So what would be the other options to work something like similar to Azure SQL Managed instance link which is close to real time and it has feature from SQL […] SQL Server 2022 - Development [SSRS Report Performance issue after Upgrade]( - We Upgraded db from MSSQL 2008 to Microsoft SQL Server 2019 & SSRS Server 2008 to 2014 While running reports , we are facing performance issues report not loading after 30 min also , We did Query optimization it is taking 1 - 5 min in SSMS Based on Parameters. In the report server , we […] [No Data Being Inserted]( - I have a table that i populate using joins.One of the tables don't insert its data. Tables are:-- Table: model.IMETA_ZTRB_BRACS_Model_TA_BW -- DROP TABLE IF EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW"; CREATE TABLE IF NOT EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW" (   "ZTBR_TransactionCode" integer NOT NULL,   "Company_Code" character varying COLLATE pg_catalog."default",   "Posting_Period" text COLLATE pg_catalog."default",   "Fiscal_Year" […] [Query web API and return JSON data]( - curl -X GET " \ -H 'Authorization: Bearer ' \ -H 'Accept: application/json' How do i run this Rest Json API in sql server directly ? I believe its a combination of using he below, but i could not figure out the last syntax. sp_OACreate, sp_OAMethod sp_OAGetProperty Python version is here : # Version 3.6.1 […] [How to get 2nd max value if 1st max is null]( - Hello All, I have written below query to get agent data who completed max tickets for each group. But some times the agent email address is null or blank. So In that case I need to consider the 2nd max tickets completed agentname. If this email also null then 3rd max tickets completed agent. Could […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -