T-SQL Language Changes in SQL Server 2022 Part 2 (2022-09-30)

SQLServerCentral Newsletter for September 30, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [T-SQL Language Changes in SQL Server 2022 Part 2]( - [The Redgate 100: Top Influencers List]( - [How to Build a Data Science Time Series Model with SQL Server]( - [From the SQL Server Central Blogs - DELETE TOP x rows with an ORDER BY]( - [From the SQL Server Central Blogs - The Redgate 100]( Question of the Day - [Idempotent Column Drops]( The Voice of the DBA  Daily Coping Tip Find a new way to use one of your strengths or talents I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Cloud Databases Most of us are used to a database that lives on a server somewhere. It might be in our data center or a VM that exists somewhere, but it's really an on-premises type of infrastructure. Even if the VM is in AWS or Azure, this is a single system on a server that we control. We can add HA capabilities to this system, but the model is the same as if the database were on our development workstation. Note: this doesn't matter if this is an RDBMS like SQL Server or [PostgreSQL]( or a NoSQL type system, such as [MongoDB]( or [Neo4J](. That's a comfortable system, and it's how we've built many applications over the years that support our business processes. Many of us default to architecting and thinking about our applications with this model. These days there are cloud databases, which can be used in this model, but which also have other capabilities. I ran across a piece that noted [cloud databases ought to be a part of our modern tech stack](, and I tend to agree. These days the need to be more flexible, available, and secure are important for applications. An on-premises database might not meet these needs. A cloud database is typically a system designed to be more of a [PaaS]( service, with replication, built-in HA, scaling, and more. [Azure SQL Database]( be considered cloud-native, but I think it lacks some of the features that we might take advantage of in [CosmosDB](, [Couchbase]( or [CockroachDB](. Things like the availability from various types of connections, quick and easy failover and sync from different regions around the world, and low latency for users anywhere. A few examples are in the article, and I see these types of requirements coming about more and more often as our clients start connecting from different devices in different locales, and at scales that are harder to handle with a single on-premises (or clustered) database system. While you might be able to handle the workload, is it worth it for you to manage some of the infrastructure and administration that a cloud vendor can do instead? That is why I think you ought to learn about and understand where a cloud database might be useful. While not all of us have clients all over the world needing real-time access from various devices and applications, we might grow into that need. Whether it's clients that are employees or customers, there are increasing demands for flexible ways to handle the growing workloads on our databases. You want to be prepared for when you might exceed the capabilities of the platform you're used to, that way you can decide when adopting a cloud database might make sense. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [T-SQL Language Changes in SQL Server 2022 Part 2]( Frank Dolan from SQLServerCentral Learn about more of the T-SQL language changes in SQL Server 2022 to LTRIM, RTRIM, TRIM, GET_BIT, SET_BIT, LEFT_SHIFT, RIGHT_SHIFT, BIT_COUNT and more. [External Article]( [The Redgate 100: Top Influencers List]( Additional Articles from Redgate We're recognizing the individuals in the database community for their work, online influence and thought leadership in our inaugural list of influencers: The Redgate 100. [External Article]( [How to Build a Data Science Time Series Model with SQL Server]( Additional Articles from MSSQLTips.com From the SQL Server Central Blogs - [DELETE TOP x rows with an ORDER BY]( david.fowler 42596 from SQL Undercover This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based... [Blog Post]( From the SQL Server Central Blogs - [The Redgate 100]( Steve Jones - SSC Editor from The Voice of the DBA The Redgate 100 is a list of 100 people that are influential in the database world in a number of categories.I made the list in a few places, which...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Idempotent Column Drops I want to easily ensure that I can run this script idempotently on any of my SQL Server 2019 databases. ALTER TABLE dbo.Summit2022 DROP COLUMN QuoteByURL What is the easiest way to make this idempotent? 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 Migration Tool to Use III Which migration tool is suited to analyze my SQL Server instance in preparation for an upgrade and look for compatibility issues? Answer: DEA Explanation: From BOL: [The] Database Experimentation Assistant (DEA) is an experimentation solution for SQL Server upgrades. DEA can help you evaluate a targeted version of SQL Server for a specific workload. Ref: Compare SQL data migration tools - [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 [Query plans]( - There is more than 1 plan for a particular query. I suspect it could be auto stats is sampling too low so enabling the persistent sampling. Any thoughts? [SQL Server Profiler - trace data file]( - Hi all, On the disk noticed some files type SQL Server Profiler - trace data file. It has increasing the file for every 3 hrs. The path location is not matching with the path locations of the traces from the query select * from sys.traces. How can we identify the traces that not showing from […] SQL Server 2016 - Administration [How to load Extended Events results from memory directly to table, in a script?]( - Hi all, We created Extended Events sessions that dump results into a file. Then, with a scheduled job we load it into a database table. We want to eliminate the part dumping to a file, and load it directly from buffer ring (which is memory) to a table. We don't use GUI built-in into SSMS, […] SQL Server 2016 - Development and T-SQL [Left Outer Join Check]( - Hi Community, Can someone please check my T-SQL code and let me know if I'm getting the right result. I'm doing a left outer join on the following tables and I'm getting 18 records back, but no missing data. This is confusing, because in the circuits table there are 77 records and in the races […] Development - SQL Server 2014 [Error handling with a called stored procedure]( - Stored procedure OUTER_TEST calls stored procedure INNER_TEST. If an error in INNER_TEST, how do I capture the error message (to store in a table). When I have a try-catch in OUTER_TEST, I can only access a later error message and not the initial one from INNER_TEST? SQL Server 2019 - Administration [create SPN]( - how to create SPN for sql named instance . SQL service account is configured with NT service account ? [Where are SQL Client Tools located?]( - Hello experts, I did an installation of just the following tools on a server (that is, no actual SQL Server or Agent etc.) Client Tools Connectivity Client Tools Backwards Compatibility Client Tools SDK Does anyone know where those tools are located on the server? I also installed SSMS but I don't know where the above […] [Patching failover cluster]( - Hi I'm looking at the process for patching our failover clusters. I always thought it was best practice to pause the node you were patching to stop unexpected failover. However this Microsoft article seems to say that whilst the setup.exe is running for patching the node is removed from the preferred owners. To upgrade […] SQL Server 2019 - Development [Selecting State from table based on a Begin and End Date]( - Greetings, I have a Report that prompts for a begin and End Date and I need to select a state based on the values entered. I've The table I am comparing the begin and end dates to looks like this: ID    InitialState   NewState     Date     […] [Getting the rolling weekly average based on the preceding 12 months]( - Hello, If someone can help me out with the below problem I'm having I would really appreciate it. Thank you! I have one order table and one calendar table; Orders Table: USE [Northwind] GO /****** Object: Table [dbo].[Orders] Script Date: 20/09/2022 22:43:51 ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Orders]( [OrderID] [int] […] [How to export 20 milion rows to only one file ?]( - I work on sql server 2019 i have table have 20 milion rows . i need to export data Exist on table to only one file by using sql server 2019 . so what best tool and faster tool can help me to extract 20 milions rows from sql server to csv file (only one […] [Conversion of currency stored as varchar to decimal]( - Hi all, I am trying to convert currency values stored as a string to decimal. In my dataset, there is a currency column with values like $15,000.50 stored as varchar. I am trying to convert it to 15000.50 I tried SELECT CONVERT (decimal , '$15,000.50 ') but end up with the dreaded conversion type error. […] SQL Server 2008 High Availability [Storage migration validation]( - Hi All, our storage team is planning to do San migration(from old to new). From application point of view what all steps should I follow to perform complete validity post migration ? We use SQL 2008 DB servers. Reporting Services [Removing Number from String in SSRS]( - Hello, New to SSRS 2016 here, and appreciate any help. Field value coming in with "( " in front of a number, and I was able to remove it using Mid function, but now there are other values where there are values where it is the 2nd "(" that enclose the number. For example Field […] Integration Services [Unable to execute SSIS package thru command line]( - I have SSIS packages which were recently upgraded from 2012 to 2019. I was testing it to see if it would run successfully without any issues. Pretty straightforward package. I have a source and 2 different destination(2 DBs on 2 different DB servers). So when I run the package thru VS, it runs fine. When […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -