Voice of the DBA

Author: way0utwest

  • 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

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • A New Word: Mornden

    mornden – n. the self-container pajama universe shared by two people on a long weekend morning, withdrawing from the world and letting the hours slow to a crawl, coming as close as they’ll ever get to pausing the flow of time, even as they know it’ll eventually rush back in all the faster.

    My wife and I used to enjoy mornden early in our relationship. We’d get the Sunday paper, make breakfast, and lay around, reading, relaxing, enjoying the morning. These days, no paper, but we do try and get a couple mornings a week where we sleep in a bit, lay in bed, chat, read, just relax.

    We are both (too) type-A and are very busy. Taking a some time to relax each week, pausing time for a few hours, is good.

    Life certainly rushes in fast when we get back to it.

    From the Dictionary of Obscure Sorrows

  • Republish: Boring or Scripting

    I’m off to Live 360 today in Orlando, flying early (I should be somewhere over Texas or Louisiana by now), and busy at the event.

    So you get to re-read Boring or Scripting, which describes a bit about my career. I don’t get bored, I find ways to keep busy and make my job better.

  • Using SQL Compare with Read-only Access

    Recently a customer asked if SQL Compare and SQL Data Compare can be used with a read-only database as a source. It’s a good questions as I’ve seen some tools that create temp tables or do some other work in a source database, which might cause problems. Certainly someone running SQL Compare against production would want to ensure it works as a read-only application.

    This post will look at SQL Compare with a read-write database but a user account with read-only access. My previous post looked at a read-only database.

    This is part of a series of posts on SQL Compare.

    Setup

    I’ve got a couple of databases that I use for Compare demos. In this case, compare5_prod and compare1. The compare5_prod is set to read write, which is normal.

    2024-10_0110

    I created a new login, which will default to the Compare5_prod database.

    2024-10_0107

    This login maps to a user, which has read only rights in the database.

    2024-10_0109

    In my compare setup, I’ll use this login to connect to the database.

    2024-10_0111

    As you can see below, Compare works fine, even reading the various system metadata tables:

    2024-10_0113

    This works because SQL Compare is not writing anything to the database. We read metadata and then process that in-memory on the client before returning the results.

    You can see this also works in SQL Data Compare. Here’s the connection:

    2024-10_0115

    And here are the results

    2024-10_0114

    Summary

    This was a very simple example, but I find that clients always would prefer to see examples already completed and proof that something works when they are evaluating software. Hopefully this helps answer this question.

    SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.