Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs) (2024-04-03)

SQLServerCentral Newsletter for April 3, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)]( - [Use Fabric Data Factory to Load Data From a CSV File in Azure Blob Storage to Azure SQL DB]( - [From the SQL Server Central Blogs - Spring 2024 Speaking Engagements]( - [From the SQL Server Central Blogs - SQL Compare Force Column Order in a Table]( - [SQL Server 2022 Administration Inside Out]( Question of the Day - [SQL Server Standard Always On Availability Groups]( Featured Script - [Running Mistral AI model on AWS Bedrock using Python]( The Voice of the DBA  Under the Bus I've had a good career in database work. I've had success, and I've had some failures, fortunately the former far outpacing the latter. In my career across many companies, the code I've written has tended to work well, or at least well enough. I've managed systems and ensured a high uptime, and solved issues quickly. I have left quite a few jobs in technology, some because I was unhappy, some for better opportunities. I was asked to leave one job. I disagreed with my boss, thought he was a jerk, and our CTO told me this person was more valuable than I was at that time. The CTO suggested I move on, so I did. That day. I've been a manager of both development and operations groups at different positions. I learned as a manager that I praised my staff publicly and criticized them privately. That included taking blame for issues, but passing our kudos for success. A leader is responsible for the team, and that includes accepting the failures of individuals below them. That's what I believe. In the last few years, there has been a bit of a trend where managers blame individual contributors. [The Equifax ex-CEO blamed a single person]( for not patching their servers prior to the attack. [Solarwinds CEO blamed an intern]( for an issue with posting a password to GitHub. There are other examples, but in many cases, senior management is blaming someone far below them for a mistake. I know technical people sometimes make decisions that are poor, they click the wrong thing, adjust the wrong server, or make some other mistake. However, in many cases, managers know about the work their people are doing. If they don't, then isn't that a management failure? While a manager might not know about patches, they know patching is important. It's a manager's job to place a priority on patching systems if this is important, and then ensuring someone verifies patches. I don't expect managers to check repos for passwords, but certainly there are tools to help detect his. I certainly get alerts about a few passwords in my test scripts posted to some repos. Again, a manager should ask that controls, checks, verifications, etc. are a part of any processes that need security. I know that often the paychecks of senior managers are far above those of technical staff. I know it's easy to blame someone making $60k a year and not accepting blame as a VP being paid $400k a year. I know that manure rolls downhill, but it's disturbing that these executives aren't being held accountable for the mistakes of their staff. It's up to them to ensure that staff prioritizes what's important, security, maintenance, whatever. As an individual contributor, I find this behavior is a symptom of a poor culture. We're not a team when upper management throws people under the bus. To me, it's a sign I need to seek new employment. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 3: Understanding Common Table Expressions (CTEs)]( Greg Larsen from SQLServerCentral A CTE is a temporary result set defined by a simple query, and is used within the execution scope of a single INSERT, UPDATE, DELETE, or SELECT statement. In this article we will explore how to define and use CTE's. [Technical Article]( [Use Fabric Data Factory to Load Data From a CSV File in Azure Blob Storage to Azure SQL DB]( Additional Articles from SQLServerCentral Andy Leonard discusses and demonstrates using Fabric Data Factory to load a CSV file stored in Azure Blob Storage to Azure SQL DB. [Blog Post]( From the SQL Server Central Blogs - [Spring 2024 Speaking Engagements]( alevyinroc from FLX SQL I will be presenting Answering the Auditor’s Call with Automation at two upcoming events, one virtual and one in-person. DBA Fundamentals Virtual User Group Tuesday, April 9 2024 at Noon... [Blog Post]( From the SQL Server Central Blogs - [SQL Compare Force Column Order in a Table]( Steve Jones - SSC Editor from The Voice of the DBA I had a client that was concerned about SQL Compare behavior when a developer adds a column to the middle of a table. I wanted to reassure them, so... [SQL Server 2022 Administration Inside Out]( Site Owners from SQLServerCentral Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.   Question of the Day Today's question (by Stewart "Arturius" Campbell):  SQL Server Standard Always On Availability Groups My environment is running SQL Server Standard. I have 3 instances of SQL Server for the application, the first located at the Head Office, the second at a regional office and the 3rd at the DR site. I plan to implement Basic Always On Availability groups for the application, with the Head office instance as the Primary Replica, the regional office as a Synchronized Secondary Replica, and the DR site as an Asynchronous Secondary Replica. Can this be done? 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) Wargames In the movie Wargames, what game does the high school student get the computer to play in order to learn about no-win scenarios? Answer: tic-tac-toe Explanation: Tic-tac-toe is the game. Ref: Wargames - [( [Discuss this question and answer on the forums](  Featured Script [Running Mistral AI model on AWS Bedrock using Python]( Abhinav Pandey from SQLServerCentral Mistral 7B is the first foundation model from Mistral AI, supporting English text generation tasks with natural coding capabilities. import boto3 import json bedrock = boto3.client(service_name="bedrock-runtime") prompt = "[INST] INSERT YOUR PROMPT HERE [/INST]" body = json.dumps({ "prompt": prompt, "max_tokens": 512, "top_p": 0.8, "temperature": 0.5, }) modelId = "mistral.mistral-7b-instruct-v0:2" accept = "application/json" contentType = "application/json" response = bedrock.invoke_model( body=body, modelId=modelId, accept=accept, contentType=contentType ) [More »](  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 [Downgrade from Enterprise to Standard (incl. SSAS, SSIS, SSRS)]( - Hi there, thanks for listening! We are preparing to downgrade one or our Instance from Enterprise to Standard. Thankfully, this is not a production server, so some downtime is not an issue. We are planning to make a snapshot of the VM so that we can rollback in case something goes wrong. I want to […] SQL Server 2017 - Development [Query help please]( - I have a history table that tracks changes to client records, im having trouble writing a query that will capture BOTH of these records based on (Where changedcolumns = 'Paid'). I need the OLD and NEW history in the result. [SQL agent help]( - Hi Guys, I have an SQL Agent job. It is called an SSIS Package and is stored in SSIS DB. I can see the SSIS Package completed without any problem. Job has only one step. However, the SQL Agent job never ends. Just for my testing, I created an SQL Agent job and used SQL […] SQL Server 2016 - Development and T-SQL [Tracking Database Growth - Script Automation & Reporting]( - Hello,  I found this script that helps me in populate a table with information about my database; specifically sizes. I am a looking for a way to automate the process wherein the data can be extracted to an excel spreadsheet for us to do reporting against to track our database size growth overtime. Your […] Development - SQL Server 2014 [Outer join table where there is 2 foreign keys to join]( - I am asked to provide a date in a query from a table that may or may not have a record. Thus the outer join. The issue is the joined table may have many records for the ID (foreign key) being joined, and the query is returning the first result not the last.  There […] SQL Server 2019 - Administration [Optimizing Always On failover alert]( - Hello, We are trying to optimize the no. of alerts generating while Availability Group fails over. Event ID: 1480: Database Replica Role Changed Event ID: 19406: Availability Group Replica Role changed. As per my understanding Event 1480 can never happen without 19406. Hence it is ok to disable alert for 1480. Please let me know […] SQL Server 2019 - Development [how to do this in SQL?]( - Hi everyone I am working on a query and I am totally stumped. I don't even know where to begin. I will give test data, expected outcome, and explanation of logic. Test Data: create table #test_table ( company char(4), trade_date date, type char(1), level int, price decimal(6,2) ) insert into #test_table values ('ABC','2024-03-21','L','100','23.45'), ('ABC','2024-03-21','L','200','33.12'), ('ABC','2024-03-21','L','300','21.05'), […] [Need Help: Polybase external table on Parquet file stored in Azure StorageV2]( - My goal is to create a Polybase external table from a Parquet file located in an Azure storage container. SQL Server 2019 (RTM-CU22-GDR) (KB5029378) - 15.0.4326.1 (X64) StorageV2 (general purpose v2) (LRS) File was exported from SQL Server using: parquet-cpp-arrow version 4.0.0 I know that the SAS token I'm using is probably good in terms […] [Help with reading from JSON array]( - Hi all, I am trying to read the following JSON code in SQL Server: declare @json nvarchar(max) set @json = ' { "WorkId":121, "Code":"UK_AAT", "UpdateAttributes": { "TriggerDateMapping": { "3N":"N/A", "10N": "N/A" }, "OverrideRules": [ {"Busket Rule":"A1", "Exit_Window": 15, "Strategy": "STE Triggered", "Start_Date": "Month 2" }, {"Busket Rule":"C2", "Exit_Window": 44, "Strategy": "STE", "Start_Date": "Month 2" }, […] [test sql servers]( - Hi we run 2019 standard. I'm torn between asking my dept head for 3 test sql servers for use by everybody so that we can test 3 projects at once vs enough horsepower on my local to run 3 instances.  I'm leaning toward one that is shared and whatever it takes to run 3 instances […] [error using #temp tables]( - Hi I am getting this error message when I try to re-use a temp table. The code is a bit long so I won't post it here. Here is a template on how the temp tables are being used. What am I doing wrong? Code: DROP TABLE IF EXISTS #TEMP1 DROP TABLE IF EXISTS #TEMP2 […] SQL Azure - Administration [Database Snapshot Alternative in SQL MI]( - Database snapshots are not supported in SQL Managed Instance, Is there a workaround for Database snapshot feature. Restore from point in time backups will take a lot of time for us. I want to create a readonly copy of a database from a specific point, how can this be achieved in SQL MI? Analysis Services [Dates are rendered in multiple formats in Excel against Cube]( - We upgraded a SQL2014 environment to a sql2016 one recently with a customer. This customer still uses the traditional SSAS Multidimensional cubes. After the upgrade the dates no longer present themselves in the singular same format within Excel reports. A value of 14 march 2024 is represented as '2024-03-14' correctly , but 4 march 2024 […] SQL Server 2022 - Administration [Post Upgrade Access issues - Databases in 120 Compatibility and Read_Only Status]( - I recently did an in place upgrade from 2014 to 2022, and couldn't connect to any of the databases that were in Read_Only state AND still in 120 compatibility mode, with the following error: "Cannot open database 'DBName' version 782. Upgrade the database to the latest version." After setting all the databases to 160 mode […] [SQL2022 CU12 Question]( - Hi. I installed SQL2022 CU12 and in the notes it says:  2937584 Fixes an issue in which the sp_server_diagnostics stored procedure doesn't respond to the Always On availability group (AG) resource DLL within the HealthCheckTimeout when the I/O takes a long time, which causes unnecessary restart and failover. For example, when the sp_server_diagnostics stored procedure is waiting for the PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE wait type. […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -