Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

New SQL Server 2022 Functions

At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022.  Before the talk, I made a joke with him that none of these were new because it's 2024. They'd been out for 2 years.

Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don't have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What's New in the Language.

I'm curious if any of you are using these new functions? There are a few time series ones, though I think GENERATE_SERIES is the one many of you might have used. Are any of you using DATE_BUCKET? That looks interesting, but I have to admit I haven't played with it. STRING_SPLIT with the ordinal is my guess for the most popular function people use.

There are also some new JSON and bit manipulation functions, which might be of use in some situations. GREATEST/LEAST are there, but I'll have to say these functions haven't come to mind as solutions in any of my queries or answers I write for questions. I do use the trim functions, though still only with spaces. I guess that some of you might find ways to incorporate trimming with other characters and possibly change old code.

I do think that many of you can likely refactor code and make it cleaner with these functions, but you should test them extensively. As we've seen with some language changes, performance isn't always better, and some changes (like FORMAT), can cause you resource issues. At the same time, if the code performs well, using cleaner code is a good way to perhaps update your codebase and gain some skill with new techniques.

One warning. While I like refactoring code, make sure you do some testing, preferably automated, to be sure you aren't introducing bugs or missing edge cases that your old code covers well. A cleaner codebase is nice, but having working code is more important.

If you're using any language features, leave a comment today.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
SQLServerCentral Article

Announcing SQL Server 2025

Steve Jones - SSC Editor from SQLServerCentral

Bob Ward announces the next version of SQL Server, now in private preview.

SQLServerCentral Article

Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle)

Jeff Moden from SQLServerCentral.com

BULK INSERT and BCP are powerful, high performance tools for importing text files. SQL Server MVP Jeff Moden gives us several nifty tips in this introduction to BULK INSERT and BCP Format Files including "Skipping Columns" and the sequestration of bad rows while still loading all the good data.

External Article

Transactions

Additional Articles from SimpleTalk

By now, pretty much everyone has heard the stories about the first computers. They were huge, cost a fortune, required incredible amounts of air conditioning, and perhaps more importantly, ran batch jobs submitted on punched cards and magnetic tape. But even before we had these dinosaurs, there were E.A.M (electric accounting machine) units.

Blog Post

From the SQL Server Central Blogs - T-SQL Tuesday #180: Avoid Perfect for Good Enough

K. Brian Kelley from Databases – Infrastructure – Security

I'm listening to Effortless by Greg McKeon (link to author's page) through Audible.com. He points out that often times, a quest for perfection means we don't move forward. We...

Blog Post

From the SQL Server Central Blogs - Book Review – Humanizing Data Strategy by Tiankai Feng

Koen Verbeeck from Koen Verbeeck

This book was making its rounds on social media, and the concept seems interesting enough to make me want to read it. It’s a very short book (only 106...

Practical Database Auditing for Microsoft SQL Server and Azure SQL

Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance

Site Owners from SQLServerCentral

Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Running Steve's Code

Can you run this code in any of your SQL Server 2019 databases without error? 
CREATE OR ALTER PROCEDURE [dbo].[StevesAmazingProc]
AS
    
        SELECT Consumer_ID ,
               Trend_Category ,
               Bit_Trace
        FROM    NewWorldDB.dbo.MarketTrend;
    
GO

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)

A Strange Result

What does this code return in SSMS 20 from SQL Server 2019?

select '|' + CHAR(0)+'abc' + '|';

Answer: It depends on whether results are in the grid or text

Explanation: It depends. The grid format returns |, and has trouble with the char(0). The text format shows | ABC|. ;)

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
Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing - First time here...hoping I can find some direction and answers so I can devise a proper solution. Novice Basic SQL experience over the years while being the "jack of all trades' IT support, not a DB admin by any means. Issue: After vendor Application upgrade and SQL upgrade from 2014 to 2016 we cannot process […]
Issue with DBMail - Hi all   We've got 3 servers with the same setup for dbmail. They use the same account (just with a different display name as replies are ignored) and every was working perfectly until this weekend. I've now only got 1 server out of the 3 that is correctly sending emails automatically. Server1 is running […]
SQL Server 2016 - Development and T-SQL
Counting values with hysteresis in Report Builder - Hi, I have a set of data (for instance sine wave) and I need to count, how many times have value reached above lets say 75%. Is there a way to do that in Report builder ? Thank you JK
SQL Server 2019 - Administration
Agent job security account - Hi All, I have few sql server agent scheduled jobs which are supposed to run as NT Service\mssqlserveragent account however at times it runs using different account. Being novice in this world , I am not able to figure out where to make the change in agent job security to ensure that it only and […]
Export database and tables no table data - Trying to move a database from one sql server instance to another on separate servers . I need to only move it like a skeleton no data and looking for steps to export the database and tables. Is this possible?
SQL Server Newbies
normal role member to be able to view list of other role members in his DB - Is this even possible ? Tried with grant but to no avail. [sys].[database_role_members] and [sys].[database_principals] can not be accessed even with grant .
SQL Azure - Development
How to manage Time zone changes for countries - Hello Everyone, I am using Microsoft SQL Azure (RTM) - 12.0.2000.8, as of October 2, 2024. This software is copyrighted by Microsoft Corporation in 2022. We have developed a ticketing system currently used by around 30 countries, and we expect this number to grow in the coming days. To maintain each country's local time, we […]
SQL Azure - Administration
Azure elastic job issue authenticating to Azure SQL database - Azure elastic agent jobs: I’m getting this error “The server principal “ac1971e9-381b-449b-9e1a-9cc276fc2985@b5313c9d-fb0d-47af-bc87-7c050bffbdc3” is not able to access the database” when an elastic agent job tries to run against a new database that has been added to an elastic pool.   The elastic pool is configured as a target group member. For all other databases in […]
Connecting
SSMS 20.2 on Windows11 not able to connect to SQL 2005 ?? - We still have a couple of dino's. What should I check to re-enable access to ye good old SQL 2005 on Windows 2003 for my SSMS 20.2? I have already accepted "use server certificate" and "optional encryption" Still doesn't work; "SSL Connection forcebly closed by server" btw: When using SSMS 18.12.1 it works fine
Integration Services
Package to Project model conversion - Hi All, With previous customer range we were always required to work in the traditional package model approach were individual packages were stored in deployment folders and combined with configuration files. (Usually old 2008R2 SQL server versions) For my current employee I have the liberty to setup everything from scratch using newest versions of everything. […]
SQL Server 2022 - Administration
Converting job_id to join to another table. - I need help, please! I have a monitoring table that pulls in information about program name (plus sp_who2 does as well), and I need to join it to msdb.dbo.sysjobs to get the job name where the program name starts with SQLAgent (like SQLAgent - TSQL JobStep (Job 0x8FA89775AAF135499FA4CC1621B639FB : Step 1)). Nothing I try is […]
How much transaction logs generated by hourly/daily - For MSSQL is there any queries or methods for me to pull historical data of transaction logs generation? I know that for Oracle database we can run a query to extract the amount of archivelogs generated hourly/daily. Is it possible for MSSQL? I have an hourly TLog backups running on the database, so currently I'm […]
Export Data-tier Application vs Generate Script: Why Does SQL Server Have Both? - Hey everyone, SQL Server has two mechanisms for deploying and moving a database to another platform: Export Data-tier Application and Generate Script (with the "schema and data" option enabled). I'm a bit puzzled because these are two different tools that seem to tackle the same task. From what I've read and tried, they appear almost […]
SQL Server 2022 - Development
A Design Question using a fictious example - Background: I have two tables. Tests and Grades. For each Tests record there are many Grades; one for each student. The Grades table has a field called: StudentsGrade. Each time a Tests record is inserted into the database, an associated Grades record is inserted into the database, one for each student where the value of […]
Generating (semi-)fake purchase data - I know how to generate fake data (thanks to Jeff Moden's articles!)... the problem is that I'm trying to generate purchase data where the unit price of a product increases on some products over time.  Normally, I'd get the changing price information from all the purchase invoices, but I don't have those. So I have […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -