Tag: T-SQL

  • T-SQL Tuesday #198–Change Detection

    This month we have a new host, Meagan Longoria, who graciously agreed to help me this month. I’ve known Meagan for a number of years and she’s been a person whom I’ve asked questions about data visualization and analysis in the past. I was slightly surprised by the topic this month, but only slightly. I’ll write my answer below, but if you want to try blogging and host a future month, let me know.

    Change Detection

    The topic this month is change detection, which is important for efficient ETL work, but also for other areas, such as auditing. I haven’t tended to work in high volume systems where we did a lot of ETL and needed to very efficiently detect changes. Most of the time I’ve had ETL pipelines they were busy, but not excessively so.

    As a result, in the past, I’ve often used a roll-your-own approach in the SQL 6.5->2008 era. As I’ve worked on those systems, we’ve usually used a simple update or modified date in the table that tracked when something was altered. By knowing the last time a pipeline ran, we could gather all data from that point forward and extract it.

    We used a similar approach to send a lot of emails from SQL Server Central years ago. That can work well, and as long as you track the last execution of your pipeline process, whatever that is, you minimize the data being transferred.

    A Modern Approach

    I ran into dbt a few years ago and did a one day class on how it works. It was interesting to me, and I could see the appeal. Recently I had a conversation with John Miner, who’s used it in his Fabric Modern Data Platform series. I would be very tempted to use dbt, in conjunction with a modified date as described above, to ETL data around today.

    However.

    I have been seeing that Change Data Capture (CDC) is being used by lots of products these days. It’s behind the Fabric mirroring, Oracle has used it for years, when you look to move data into Databricks, CDC is common, it seems like it’s everywhere. 

    If I were going to be regularly moving data in 2026, CDC seems like something I’d experiment with and test, since it’s a known technology that works across many platforms and there is a lot of knowledge out there on how it works. AI can certainly help with experiments, and with understanding the overhead on your system, because there is some overhead.

    I haven’t looked at the new Change Event Streaming, though I’m always wary of anything that limits me to one cloud. The more generic CDC, with AI assisted configuration and maintenance, seems like a better approach.

  • Get a Range of Sequence Values: #SQLNewBlogger

    I discovered a procedure recently that I wasn’t aware of: sp_sequence_get_range. This post looks at how the proc works.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Setup

    I have a sequence object, IDCounter, that is an integer with an increment of 2. The next value that is returned is shown here:

    2026-04_0249

    The next value returned will be 99 (increment by 2).

    However, imagine that I know I need 10 new values. I don’t want a loop to get these values. Instead, I want to move the sequence to 10 values ahead.

    These ten values will be 99, 101, 103, 105, 107, 109, 111, 113, 115, 117. The current value should then be 117 if we get 10 rows.

    Let’s use sys.sp_sequence_get_range to do this. I’ll use this code:

    DECLARE @i SQL_VARIANT
    EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.IDCounter', @range_size = 10, @range_first_value = @i OUTPUT
    SELECT @i AS RangeStart

    I need to use a SQL_VARIANT as the output, though I can cast this to anything once I have the value.

    When I run this code, notice the output.

    2026-04_0251

    Now if I check the metadata, I’ll see the current value below as 117.

    2026-04_0252

    There are client side applications that gather a bunch of data and know they need to insert xx rows. This helps them both update the sequence and also reserve these values for themselves. Of course, if the application fails, these values might be lost.

    SQL New Blogger

    A quick post. This took me about 5 minutes to test and about 10 minutes to structure a quick post on something I learned.

    As a follow-up, I’ll use another post to show how this works in an application that reserves these values and then another application performs an insert.

    You could easily do this on your blog and show some knowledge.

  • JSON_OBJECTAGG is an Aggregate: #SQLNewBlogger

    I wrote an article recently on the JSON_OBJECTAGG function, but neglected to include an example of why it can behave weirdly. This post looks at something you might not realize unless you remember this is an aggregate function.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A Strange Result

    I have some data, which I show below. Some teams, cities, and years.

    2026-04_0232

    If I ask for this data in a JSON_OBJECTAGG query, I get one document back. Which is fine. You can see this below

    2026-04_0233

    However, if I were to add the year separately as a column, I get an error without a GROUP BY. This shows me this is an aggregate, which I read and knew, but wasn’t thinking about in my testing:

    2026-04_0234

    If I add a GROUP BY, then I see these results. Notice that I get documents for each section of the GROUP BY with just that data. Each of these documents is 1 team, except for the 1960 year, where three teams started.

    2026-04_0235

    If you are using this to assemble simple JSON documents with a WHERE clause, you might not realize this is an aggregate and behaves that way.

    Make sure you test your code in a few ways and treat this function as an aggregate.

    SQL New Blogger

    This post took a little longer to write (20 minutes). I took the code from my previous article, but I restructured a few things once I realized how this works as an aggregate. It’s a simple look at the function, but it also points out something I learned, which isn’t necessarily obvious. Talking about that shows you have some knowledge besides just copy/pasting code to create JSON documents.

    You could do this as well, and leverage the idea of building off one post to show something in another.

  • Finding the Next Sequence Value: #SQLNewBlogger

    I saw a question asking about the next sequence value and decided to try and answer it myself. I assumed this would be easy, and it was, but I used some AI help to make it very quick to get the value and learn something.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Checking for Sequences

    I know I’ve done some testing lately with sequences for a customer, so I decided to ask Prompt AI to help. I connected to a database and asked this question after clicking ALT+Z.

    2026-03_0133

    I got this response, which I executed before accepting the code. One thing I like about SQL Prompt is I can execute code and then adjust my prompt (or paste in an error). As you can see, I have 3 sequences in this database. I get a bunch of the meta data, including the current_value.

    2026-03_0134

    However, is that the next value or the last one? It could be interpreted multiple ways. I could look this up, but I decided to ask again. I accepted the code, then wrote a quick SQL statement to get the next value. If you’ve never done this, you might not know how sequences work, which is a different issue. In my case, a “se” tab, “ne” tab “i” got e this code. I then asked to find this.

    2026-03_0136

    UPDATE: From this article, the current_value has an issue. For new sequences, it shows the starting value, even if that value hasn’t been used, so the last_used_value is a better choice.

    SQL Prompt AI returned this query, which gets me the current_value, but I know that. I decided to ask for an explanation rather than Google or work my way through MSLearn.

    2026-03_0137

    The explanation is good. It’s not the current value + 1 it’s the current value + increment, which is a subtlety that some people might miss.

    2026-03_0138

    Let’s test this. I wrote some code and then executed it. I get 17, which makes sense. The current value (seen above) is 15 and the increment is 2.

    2026-03_0139

    Problem solved. This short session likely will help me remember this detail for future code (or prompts).

    SQL New Blogger

    This post took me about 8 minutes to setup the code, capture the images, and write this up. I’ve done a lot of these, but I showed how to investigate a question, use AI for help, and then make sure the AI is actually giving me something good.

    You could write these types of posts and show your fluency with both data engineering and AI assistance. Take a minute and start writing some blogs that showcase how your skills and career are growing.