And the idea is the following, to cover all different date formats and for the given period

IsPalindromeDateRange <- function(date = Sys.Date(), 
                             end_date = NULL,
                             formats = "all",
                             verbose = TRUE) {
  
  
  
  ## Helper stuff
  is_palindrome <- function(x) {
    chars <- strsplit(x, "")[[1]]
    identical(chars, rev(chars))
  }
  
  reverse_string <- function(x) {
    paste(rev(strsplit(x, "")[[1]]), collapse = "")
  }
  
  date_to_digits <- function(date, format = "%Y%m%d") {
    format(date, format)
  }
  
  
  date_formats <- list(
    YYYYMMDD = list(
      name = "YYYYMMDD",
      format = "%Y%m%d",
      example = "20251202",
      regions = "International/ISO standard"
    ),
    MMDDYYYY = list(
      name = "MMDDYYYY",
      format = "%m%d%Y",
      example = "12022025",
      regions = "United States"
    ),
    DDMMYYYY = list(
      name = "DDMMYYYY",
      format = "%d%m%Y",
      example = "02122025",
      regions = "Europe"
    ),
    YYMMDD = list(
      name = "YYMMDD",
      format = "%y%m%d",
      example = "251202",
      regions = ""
    ),
    DDMMYY = list(
      name = "DDMMYY",
      format = "%d%m%y",
      example = "021225",
      regions = ""
    ),
    MMDDYY = list(
      name = "MMDDYY",
      format = "%m%d%y",
      example = "120225",
      regions = "United states - short"
    )
  )
  
  
  date <- as.Date(date)
   
  if (identical(formats, "all")) {
    check_formats <- names(date_formats)
  } else {
    check_formats <- formats
    invalid <- setdiff(formats, names(date_formats))
    if (length(invalid) > 0) {
      warning("Unknown formats ignored: ", paste(invalid, collapse = ", "))
      check_formats <- intersect(formats, names(date_formats))
    }
  }
  
  if (!is.null(end_date)) {
    
    end_date <- as.Date(end_date)
    
    # Hearlthy checker start < end
    if (date > end_date) {
      temp <- date
      date <- end_date
      end_date <- temp
    }
    
    total_days <- as.integer(end_date - date) + 1
    
    if (verbose) {
      cat("From:", format(date, "%Y-%m-%d"))
      cat("To:  ", format(end_date, "%Y-%m-%d"))
      cat("(", total_days, " days)", sep = "")
      cat("\n")
      cat("  Formats: ", paste(check_formats, collapse = ", "), "\n\n", sep = "")
    }
    
    # Emopyt data frame
    results <- data.frame(
      date = as.Date(character()),
      day_of_week = character(),
      format = character(),
      digits = character(),
      stringsAsFactors = FALSE
    )
    
 
    check_date <- date
    checked <- 0
    
    while (check_date <= end_date) {
      
      for (fmt in check_formats) {
        digits <- date_to_digits(check_date, date_formats[[fmt]]$format)
        
        if (is_palindrome(digits)) {
          results <- rbind(results, data.frame(
            date = check_date,
            day_of_week = format(check_date, "%A"),
            format = fmt,
            digits = digits,
            stringsAsFactors = FALSE
          ))
        }
      }
      
      check_date <- check_date + 1
      checked <- checked + 1
    }
    
    
    # Results
    if (verbose) {
      if (nrow(results) == 0) {
        cat("No date palindrome  found in this range.\n\n")
      } else {
        # Get uniques per date format
        unique_dates <- unique(results$date)
        
        cat(sprintf("Found %d palindrome occurrence%s (%d unique date%s):\n\n",
                    nrow(results),
                    if (nrow(results) > 1) "s" else "",
                    length(unique_dates),
                    if (length(unique_dates) > 1) "s" else ""))
        
        cat(strrep("─", 60), "\n")
        cat(sprintf("  %-12s  %-10s  %-12s  %s\n","Date", "Day", "Format", "Digits"))
        cat(strrep("─", 60), "\n")
        display_results <-  results
        
        for (i in seq_len(nrow(display_results))) {
          row <- display_results[i, ]
          cat(sprintf("  %-12s  %-10s  %-12s  %s ↔ %s\n",
                      format(row$date, "%Y-%m-%d"),
                      substr(row$day_of_week, 1, 10),
                      row$format,
                      row$digits,
                      reverse_string(row$digits)))
        }
  
        cat(strrep("─", 60), "\n\n")
        
        if (length(check_formats) > 1) {
          #funky stuff
          cat("Summary by format:\n")
          format_counts <- table(results$format)
          for (fmt in names(format_counts)) {
            cat(sprintf("%s: %d palindrome%s\n", fmt, format_counts[fmt],
                  if (format_counts[fmt] > 1) "s" else ""))
          }
          cat("\n")
        }
        
      }
    }
  }
  
  # Sanity Check 
  results <- sapply(check_formats, function(fmt) {
    digits <- date_to_digits(date, date_formats[[fmt]]$format)
    is_palindrome(digits)
  })
  
  if (verbose) {
    any_palindrome <- FALSE
    
    for (fmt in check_formats) {
      digits <- date_to_digits(date, date_formats[[fmt]]$format)
      is_pal <- results[fmt]
      
      status <- if (is_pal) "PALINDROME!" else "Not a palindrome"
      
      if (is_pal) any_palindrome <- TRUE
      
      cat(sprintf("  %s %-15s : %s  %s\n",
                  date_formats[[fmt]]$name,
                  digits,
                  if (is_pal) "<>" else " ",
                  status))
    }
    cat("\n")
  }
}

And to do some useless extraction of the palindrome dates, we have extracted all the dates from year 1100 to 2100.

And you can tell, that the most palindrome dates occurred between 1100 and 1200 and in recent years, if you look EU or ISO date formats (DDMMYYY). But if you look the YYMMDD or DDMMYY or MMDDYY, these have stable frequency per decade and are present equally in all these years.

As always, the complete code is available on GitHub in  Useless_R_function repository. The sample file in this repository is here (filename: Date_palindrome.R.R). Check the repository for future updates.

Happy R-coding and stay healthy!

For this instance, we will introduce some random pangrams, code samples and random strings sotrted by level of difficulty.

# list of phrases is generated with the help of LLM. Because ..lazy!!!!
phrases_db <- list(
  
  pangram = list(
    easy = c(
      "The quick brown fox jumps over the lazy dog",
      "Pack my box with five dozen liquor jugs",
      "How vexingly quick daft zebras jump",
      "The five boxing wizards jump quickly",
      "Sphinx of black quartz judge my vow"
    ),
    medium = c(
      "The quick brown fox jumps over the lazy dog while the cat watches",
      "A wizard's job is to vex chumps quickly in fog with zebra tricks",
      "Crazy Frederick bought many very exquisite opal jewels last week",
      "We promptly judged antique ivory buckles for the next prize winner",
      "Six big devils from Japan quickly forgot how to waltz in my room"
    ),
    hard = c(
      "The job requires extra pluck and zeal from every young wage earner, quickly!",
      "Grumpy wizards make a toxic brew for the jovial queen who lives in a palace",
      "Back in June we delivered oxygen equipment of the same size to a new complex",
      "A quivering Texas zombie fought republic linked jewelry and quartz watches",
      "The explorer was frozen in his survey before he made a quartz and jade box"
    )
  ),
  pangram_slo = list(
    easy = c(
      "Škrbast mož fizično dviguje hlapca", 
      "Iščem vzdolžen grb ptujskih fac", 
      "Črn šef Gambijk hupa vzdolž cest"
    ),
    medium =c(
      "Ščepec soli fajn zažge tobak v sodih ruma",
      "Cagav bledoličnež nima škafa za prst juhe",
      "Vzdržljivi fant mečka gobec hišnemu psu"
    ),
    hard = c(
      "Škodoželjno ga je zafrknil - ni mu pustil več hlebca",
      "Majceni krastači vrh polža se gabi študij fizike",
      "Prgišče klofute včasih bolj zaleže od močne brce",
      "Pretkani gofljač razmišlja o zgodbicah sužnjev",
      "Fantu, ki je zbrcal špičasto mevžo, hudo grozijo" 
    )
    
  ),
  rstats = list(
    easy = c(
      "The mean is sensitive to outliers",
      "Always visualize your data first",
      "Check for missing values early",
      "The sample size was too small",
      "R is a language for statistics"
    ),
    medium = c(
      "The p-value was significant at alpha 0.05",
      "Always check your residuals for normality",
      "Correlation does not imply causation",
      "The model explains 87 percent of variance",
      "Use cross-validation to prevent overfitting",
      "The confidence interval contains the true mean",
      "Random forests reduce variance through bagging"
    ),
    hard = c(
      "The heteroscedasticity in residuals violates OLS assumptions",
      "Apply Bonferroni correction for multiple hypothesis testing",
      "The likelihood ratio test compares nested model specifications",
      "Multicollinearity inflates standard errors of coefficients",
      "Use bootstrapping when parametric assumptions are violated"
    )
  ),
  
  code = list(
    easy = c(
      "x <- c(1, 2, 3, 4, 5)",
      "mean(x, na.rm = TRUE)",
      "print('Hello, World!')",
      "df <- data.frame(a = 1:5)",
      "sum(is.na(data))"
    ),
    medium = c(
      "df <- data.frame(x = 1:10, y = rnorm(10))",
      "result <- sapply(my_list, mean, na.rm = TRUE)",
      "filtered <- subset(df, value > 0 & group == 'A')",
      "model <- lm(y ~ x + z, data = train_set)",
      "apply(matrix_data, MARGIN = 2, FUN = sd)"
    ),
    hard = c(
      "result <- lapply(my_list, function(x) mean(x, na.rm = TRUE))",
      "ggplot(data, aes(x, y)) + geom_point() + theme_minimal()",
      "df %>% group_by(category) %>% summarise(avg = mean(value))",
      "tryCatch(expr = risky_function(), error = function(e) NULL)",
      "Map(function(x, y) x^2 + y, list_a, list_b)"
    )
  ),
  
  chaos = list(
    easy = c(
      "df$column[1:5]",
      "list[[1]]$value",
      "x <- y + z * 2",
      "a & b | !c",
      "func(x = 1, y = 2)"
    ),
    medium = c(
      "data[[1]]$results[[2]]$value",
      "df[df$x > 0 & !is.na(df$y), ]",
      "paste0('value_', 1:5, collapse = ', ')",
      "grepl('^[A-Z]{2,3}$', strings)",
      "sprintf('%s: %.2f%%', name, pct * 100)"
    ),
    hard = c(
      "purrr::map2_dfr(.x = list_a, .y = list_b, .f = ~.x + .y)",
      "regex <- '\\\\d{4}-\\\\d{2}-\\\\d{2}\\\\s\\\\d{2}:\\\\d{2}'",
      "eval(parse(text = paste0('func_', i, '(x = ', j, ')')))",
      "do.call(rbind, lapply(1:n, function(i) df[sample(nrow(df), 1), ]))",
      "Reduce(function(acc, x) c(acc, x^2), 1:10, init = numeric(0))"
    )
  )
)

Addition to list of sample strings, we have also couple of helper functions – calculating WPM, accuracy and handling inputs and results for user:

calculate_wpm <- function(text, time_seconds) {

  char_count <- nchar(text)
  word_count <- char_count / 5
  time_minutes <- time_seconds / 60
  wpm <- word_count / time_minutes
  
  return(round(wpm, 1))
}



calculate_accuracy <- function(original, typed) {
  
  orig_chars <- strsplit(original, "")[[1]]
  typed_chars <- strsplit(typed, "")[[1]]
  orig_len <- length(orig_chars)
  typed_len <- length(typed_chars)
  if (typed_len == 0) {
    return(0)
  }

  compare_len <- min(orig_len, typed_len)
  matches <- sum(orig_chars[1:compare_len] == typed_chars[1:compare_len])
  
  length_penalty <- abs(orig_len - typed_len)
  accuracy <- (matches / orig_len) * 100
  penalty_pct <- (length_penalty / orig_len) * 100
  accuracy <- max(0, accuracy - penalty_pct)
  
  return(round(accuracy, 1))
}


get_random_phrase <- function(mode = "pangram", difficulty = "medium") {
  
  valid_modes <- c("pangram", "rstats", "code", "chaos")
  if (!mode %in% valid_modes) {
    warning(paste("Invalid mode; use one of these modes:", 
                  paste(valid_modes, collapse = ", ")))
    mode <- "pangram"
  }

  valid_difficulties <- c("easy", "medium", "hard")
  if (!difficulty %in% valid_difficulties) {
    warning(paste("Invalid difficulty; use one of these difficulties:",
                  paste(valid_difficulties, collapse = ", ")))
    difficulty <- "medium"
  }
  
  phrase_pool <- phrases_db[[mode]][[difficulty]]
  phrase <- sample(phrase_pool, 1)
  
  return(phrase)
}


##### Diplay results
display_results <- function(wpm, accuracy, time_seconds) {
  
  cat("\n")
  cat(rep("\U00002550", 48), "\n", sep = "")
  cat("                      RESULTS\n")
  cat(rep("\U00002550", 48), "\n", sep = "")
  
  cat("\n")
  
  cat(sprintf("  Time:        %.2f seconds\n", time_seconds))
  cat(sprintf("  WPM:         %.1f \n", wpm))
  cat(sprintf("  Accuracy:    %.1f%% \n", accuracy))

  
  cat("\n")
  cat(rep("\U00002550", 48), "\n", sep = "")

}


show_error_details <- function(original, typed) {
  
  orig_chars <- strsplit(original, "")[[1]]
  typed_chars <- strsplit(typed, "")[[1]]
  
  max_len <- max(length(orig_chars), length(typed_chars))
  if (length(orig_chars) < max_len) {
    orig_chars <- c(orig_chars, rep("_", max_len - length(orig_chars)))
  }
  if (length(typed_chars) < max_len) {
    typed_chars <- c(typed_chars, rep("_", max_len - length(typed_chars)))
  }
  
  errors <- which(orig_chars != typed_chars)
  if (length(errors) == 0) {
    cat("\n  Perfetoooo! \n")
    return(invisible(NULL))
  }
  
  cat("\n  Errors:\n")
  cat("  --------------\n")
  show_errors <- head(errors, 5)
  for (pos in show_errors) {
    expected <- if (orig_chars[pos] == " ") "[space]" else orig_chars[pos]
    got <- if (typed_chars[pos] == " ") "[space]" else 
      if (typed_chars[pos] == "_") "[missing]" else typed_chars[pos]
    cat(sprintf("  Position %d: expected '%s', got '%s'\n", pos, expected, got))
  }
  
  if (length(errors) > 5) {
    cat(sprintf("  ... and %d more errors\n", length(errors) - 5))
  }
}



### Function itself

TypingTest <- function(mode = "code", 
                       difficulty = "easy",
                       show_errors = TRUE) {
  
  # Display header
  cat("\n")
  cat("\U00002554", rep("\U00002550", 52), "\U00002557\n", sep = "")
  cat("\U00002551           R TYPING SPEED TEST                      \U00002551\n")
  cat("\U00002551           Mode: ", sprintf("%-10s", mode), " | Difficulty: ", 
      sprintf("%-8s", difficulty), "\U00002551\n", sep = "")
  cat("\U0000255A", rep("\U00002550", 52), "\U0000255D\n", sep = "")
  cat("\n")
  
  phrase <- get_random_phrase(mode, difficulty)
  cat("Type the following:\n\n")
  cat("  \"", phrase, "\"\n\n", sep = "")
  cat("Press ENTER when ready to start...")
  invisible(readline())
  
  cat("\n----------------------------------------------------------\n")
  cat("  \"", phrase, "\"\n", sep = "")
  cat("----------------------------------------------------------\n\n")
  
  start_time <- Sys.time()
  typed <- readline(prompt = "> ")
  end_time <- Sys.time()
  time_seconds <- as.numeric(difftime(end_time, start_time, units = "secs"))
  wpm <- calculate_wpm(phrase, time_seconds)
  accuracy <- calculate_accuracy(phrase, typed)
  
  display_results(
    wpm = wpm,
    accuracy = accuracy,
    time_seconds = time_seconds
  )
  
  if (show_errors && accuracy < 100) {
    show_error_details(phrase, typed)
  }
  
  cat("\nPlay again? (y/n): ")
  play_again <- tolower(readline())
  
  if (play_again == "y" || play_again == "yes") {
    return(TypingTest(mode = mode, difficulty = difficulty,  show_errors = show_errors))
  }
  
}

At the end, all we need to do is to run the function itself:

#######
## Run the function!
######
TypingTest()

Results are displayed:

As always, the complete code is available on GitHub in  Useless_R_function repository. The sample file in this repository is here (filename: TypingSpeed_tester.R.R). Check the repository for future updates.

Happy R-coding and stay healthy!

For every R developer, R data scientists and all those everyday R users, that also need a break and do not want the computer to go into sleep mode.

To install the package, use:

install.packages("remotes")
remotes::install_github("tomaztk/LazyMouse")

The complete installation (with all dependencies and packages preinstalled), can be found in the details on my github repository LazyMouse and the file: Install_package.r.

For better convenience have RTools installed as well in your working environment and have Rcpp, remotes and devtools.

Happy R coding

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking
19. Dec 19: Microsoft SQL Server 2025 – New T-SQL functions – CURRENT_DATE
20. Dec 20: Microsoft SQL Server 2025 – Change Event Streaming
21. Dec 21: Microsoft SQL Server 2025 – Change Event Streaming – part 2
22. Dec 22: Microsoft SQL Server 2025 – T-SQL Features – UNISTR()
23. Dec 23: Microsoft SQL Server 2025 – T-SQL Features – String concatenation with || and compound assignment ||=

Microsoft website for SQL Server: https://www.microsoft.com/en-us/sql-server is always a good way to start. But if you would like to start more in depth documentation here is Microsoft documentation:

• What is new in MSSQL2025 on Microsoft Learn: https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2025?view=sql-server-ver17
• Educational videos on Youtube: https://www.youtube.com/@MicrosoftSQLServer
• Training videos: https://learn.microsoft.com/en-us/training/modules/introduction-to-sql-server-2022/
• Github repository: https://github.com/microsoft/sql-server-samples
• General Documentation on Microsoft Learn: https://learn.microsoft.com/en-us/sql/?view=sql-server-ver17
• Microsoft Tech Community site: https://techcommunity.microsoft.com/category/sql-server/blog/sqlserver

General Youtube videos:

• Channel Guy in The Cube (https://www.youtube.com/guyinacube)
• Check Bob Ward videos (e.g.: https://youtu.be/3RgncKX6K9A)

General Github repositories:

• Check Bob Ward’s Github SQL Server repositories
• Check Davide Mauri’s Github SQL Server repositories (did amazing job on AI part for SQL Server 2025)

Books:

• Bob Wards’s SQL Server 2025 book
• And you can always grab a book by Microsoft, Apress, O’Reilly, Packt, and many others. And you can always grab a book by the following authors: Bob Ward, Itzik Ben-Gan, Dejan Sarka, Pedro Lopes, Grant Fritchey, and many many others.)

Websites / Blogs:

• SQL Server Central posts and blogs (with editor Steve Jones)
• Brant Ozar and the blog
• Dave Pinal and the blog
• MSSQL Tips and blog
• and many more….

There are also Udemy, Coursera, edX, Harward and many other courses that covers a lot different topics from installation, to database administration, to development, to analytics, services and many other topics.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

With this blogpost we end the series of Advent of blog 2025! I wish you all Merry Xmas and Happy New Year 2026!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking
19. Dec 19: Microsoft SQL Server 2025 – New T-SQL functions – CURRENT_DATE
20. Dec 20: Microsoft SQL Server 2025 – Change Event Streaming
21. Dec 21: Microsoft SQL Server 2025 – Change Event Streaming – part 2
22. Dec 22: Microsoft SQL Server 2025 – T-SQL Features – UNISTR()

Two new features are available in SQL Server 2025 for string operations; both for string concatenation.

|| Operator

The string operator || concatenates two (or more) strings into a single expression. This operator follows the ANSI SQL Standard for concatenating strings. It is similar to CONCAT() function.

SELECT CONCAT('Hello' , ', ' , 'There!') as three_strings_concat
UNION ALL
SELECT 'Hello' || ', ' || 'There!'

We will test how the NULL and empty string behaves and what are the possible concatenations.

DROP TABLE IF EXISTS test
CREATE TABLE test (
tex1 VARCHAR(100)
,tex2 VARBINARY
,tex3 VARCHAR(10))


INSERT INTO test values
(NULL, NULL, NULL), ('Hello', NULL, ''), ('',0xA5, '$"#$"#$"'), (NULL, NULL, '')

SELECT
tex1
,tex2
,tex3
-- ,tex1 || tex2 -- will return error - can not concat varchar and varbinary
,tex1 || tex3 -- concat NULL and empty string will return NULL!
FROM test

We will see that the || operator will produce:

1. NULL value when concatenating NULL and empty string,
2. NULL with NULL will not return error but will concatenate to NULL
3. VARCHAR and VARBINARY are incompatible in the concat operator.

||= compound concatenation assignment

While going through documentation, I found that this little amazing nugget was also shipped with SQL Server 2025.

The ||= concatenation with compound assignment operator can be used to concatenate the string using expression and assigning the results expression to the variable.

It is similar behavior to the Append functionality with many programming languages

DECLARE @v1 varchar(20) = 'Hello'
SELECT @v1 as v1
SET @v1 ||= ' There!';
SELECT @v1 as v2

This is an amazing operator for working with intermediate results, loops or any kind of batch logic that you need to implement in T-SQL. But be aware with NULL

DECLARE @v1 varchar(20) = NULL
SET @v1 ||= ' There!';
SELECT @v1 

DECLARE @v2 varchar(20) = 'Hello, There!'
SET @v2 ||= NULL;
SELECT @v2

Since both results will yield NULL!

Tomorrow we will check the available documentation and learning paths for SQL Server 2025.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking
19. Dec 19: Microsoft SQL Server 2025 – New T-SQL functions – CURRENT_DATE
20. Dec 20: Microsoft SQL Server 2025 – Change Event Streaming
21. Dec 21: Microsoft SQL Server 2025 – Change Event Streaming – part 2

UNISTR() function is a new T-SQL function in SQL Server 2025. It will help you with unicode string literals (e.g.: special characters, emoji, special language alphabets and others) by letting you specify the unicode encoding value of characters in the string.

Difference between NCHAR and UNISTR is that latter will provide more flexibility and ways of handling multiple unicode characters and even escape sequences. You can also define a custom escape character to perform the necessary conversion of Unicode values into a string character set.

We can use the emoji in export (link to code of the emojis: link)

And for example the sleeping face is:

U+1F634

—

—

—

—

sleeping face

I can also get this into SQL Server, by copy/paste the code “U+1F634” and change it to “\+01F634”

SELECT UNISTR(N'Sleepy face: \+01F634')

As we can see the UNISTR removes the need for complex string concatenation.

We can also use it for escape characters that will not be included. Think for example we would like to say Thank you and add Japanese writing (arigatou gozaimasu but with ありがとう ございます) with literal unicode “\u3042\u308a\u304c\u3068\u3046\u0020\u3054\u3056\u3044\u307e\u3059”. I have converted Japanese to Unicode using this website.

And we can test the query with UNISTR function.

SELECT N'ありがとう ございます' as TY_alone
SELECT 'Thank you' AS T_Y
SELECT 'Thank you ' + N'ありがとう ございます' as TY_mixed
SELECT UNISTR(N'Thank you \3042\308a\304c\3068\3046\0020\3054\3056\3044\307e\3059', '\') as Thank_you

Tomorrow we will continue to explore the new T-SQL functions in SQL Server 2025.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking
19. Dec 19: Microsoft SQL Server 2025 – New T-SQL functions – CURRENT_DATE
20. Dec 20: Microsoft SQL Server 2025 – Change Event Streaming

We have looked into the settings of SQL server and generating SAS token. And now we will need to set the needed Azure services.

Create Event Hub in Azure Portal

In Portal create a new Event hub namespace.

Within this namespace, we will add an event hub. In the namespace, create an event hub (click on “+ Event hub”):

And specify the event hub:

Once we have event hub created, we need to add the SAS token and specify the policy. In the namespace we will go to settings > Shared access policies and click on “+ Add” to add new policy:

And give the policy name and we selected the Manage access!

Setup the SQL Server 2025

We will create a database, get the credentials, SAS token (we have generated SAS token using Powershell script in previous blogpost) and enable CES (!) As the time of writing this blogpost, the SET PREVIEW_FEATURES = ON for database scoped configuration had to be set to ON.

USE master
GO
 
CREATE DATABASE db_22_CES;
GO
 
USE db_22_CES
GO
 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'H@rd2Gue$$P@$$w0rd'

CREATE DATABASE SCOPED CREDENTIAL SqlCesCredential
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = '*****token_from_Powershell_Script********' 

And a snip for generating SAS token using Powershell:

And now we create tables where the CES will be getting the changes:

-- Create some demo tables
CREATE TABLE dbo.Customer (
  CustomerId    int IDENTITY PRIMARY KEY,
  CustomerName  varchar(50),
  CustomerCity   varchar(20)
);
GO
 

INSERT INTO dbo.Customer (CustomerName,   CustomerCity) VALUES
  ('Awesome bikes','Manila'),
  ('Fixed Gear bikes Co.','Capetown'),
  ('Red gears', 'London');
  GO

 
CREATE TABLE dbo.Products (
  ProductId     int IDENTITY PRIMARY KEY,
  ProductName          varchar(80),
  UnitPrice     decimal(8, 2)
);
GO
 
 
INSERT INTO dbo.Products
  (ProductName,  UnitPrice) VALUES
  ('Cinelli Verduro 1024', 3899.99),
  ('Leader A 524', 1996.95);
  GO
 
 
-- procedure to add new rows to table
CREATE PROCEDURE dbo.AddProduct
  @ProductName varchar(80)
  ,@UnitPrice decimal(8,2)
AS
BEGIN
  INSERT INTO dbo.Products(ProductName,  UnitPrice)
  VALUES (@ProductName, @UnitPrice)

END
GO

Once we have tables in place, we will enable the CES:

 -- Make sure to have preview feature enabled (for your database "db_22_CES")
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

EXEC sys.sp_enable_event_stream
-- OK

SELECT is_event_stream_enabled, * FROM sys.databases  WHERE is_event_stream_enabled = 1

Create event stream group and we register each of the table:

  -- Create event stream group

  EXEC sys.sp_create_event_stream_group
  @stream_group_name      = 'SqlCesGroup',
  @destination_location   = 'ces-demo-sqlserver2025-namespace.servicebus.windows.net/ces-hub',
  @destination_credential = SqlCesCredential2,
  @destination_type       = 'ces-demo-evhub'

EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Customer',
  @include_old_values = 1,    
  @include_all_columns = 1      
 
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Products',
  @include_old_values = 1,      
  @include_all_columns = 0      


-- verify CES on tables:
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Customer'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Products'

And finally, we are ready to do some inserts or updates or deletes or any change (DDL, DML) on our tables.

All the changes are present in the event hub:

For the purpose of storing the logs, we create an Stream Analytics job to hold and store the results. We have the results stored the logs into the Azure Data lake ( ADLS in parquet format).

Tomorrow we will continue to explore the new T-SQL functions in SQL Server 2025.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking
19. Dec 19: Microsoft SQL Server 2025 – New T-SQL functions – CURRENT_DATE

Change event streaming (CES) is data integration capability that streams SQL Server data changes directly into Azure Event hubs. It captures and publishes incremental changes of data to an Azure Event Hubs destination in almost near real-time. Captured changes are insert, updates and deleted (DML) and are sent to Azure Event hubs as a serialized JSON (CloudEvent) and streamed to Azure event hub.

CES can be used for multiple different use-cases, like monitoring, auditing, event-driven system on top of your on-prem database with minimal overhead and changes to database, for synchronising data across systems (platforms, on-prem and cloud solutions, etc.) and many more.

We will cover scenario, where SQL Server 2025 will be installed on prem and we will use Azure event hub. Before we start, we will need to do some of the configurations.

Here are differences between change tracking (CT), Change Data Capture (CDC) and Change Event Streaming (CES):

Configure your SQL Server 2025

We will need to:
– a login in the db_owner role or that has CONTROL_DATABASE permission for the database where you intend to enable CES,
– enable the preview feature database scoped configuration,
– prepare Powershell modules for working with Azure and Azure eventhubs (Az, Az.EventHub)

Run the Powershell scripts for installation:

Install-Module -Name Az -AllowClobber -Scope CurrentUser -Repository PSGallery -Force
Install-Module -Name Az.EventHub -Scope CurrentUser -Force

We will need to create a SAS token for authorization and connection to Azure. Replace must be the ResourceName, Namespace, Eventhub, PolicyName, and SubscriptionID.

function Generate-SasToken {
$subscriptionId = "<Azure-Subscription-ID>"
$resourceGroupName = "<Resource-group-name>"
$namespaceName = "<Azure-Event-Hub-Namespace-name>"
$eventHubName = "<Azure-Event-Hubs-instance-name>"
$policyName = "<Policy-name>"
$tokenExpireInDays = "<number-of-days-token-will-be-valid>"

# Modifying the rest of the script is not necessary.

# Login to Azure and set Azure Subscription.
Connect-AzAccount

# Get current context and check subscription
$currentContext = Get-AzContext
if ($currentContext.Subscription.Id -ne $subscriptionId) {
    Write-Host "Current subscription is $($currentContext.Subscription.Id), switching to $subscriptionId..."
    Set-AzContext -SubscriptionId $subscriptionId | Out-Null
} else {
    Write-Host "Already using subscription $subscriptionId."
}

# Try to get the authorization policy (it should have Send rights)
$rights = @("Send")
$policy = Get-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -ErrorAction SilentlyContinue

# If the policy does not exist, create it
if (-not $policy) {
    Write-Output "Policy '$policyName' does not exist. Creating it now..."

# Create a new policy with the Manage, Send and Listen rights
    $policy = New-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -Rights $rights
    if (-not $policy) {
        throw "Error. Policy was not created."
    }
    Write-Output "Policy '$policyName' created successfully."
} else {
    Write-Output "Policy '$policyName' already exists."
}

if ("Send" -in $policy.Rights) {
    Write-Host "Authorization rule has required right: Send."
} else {
    throw "Authorization rule is missing Send right."
}

$keys = Get-AzEventHubKey -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName

if (-not $keys) {
    throw "Could not obtain Azure Event Hub Key. Script failed and will end now."
}
if (-not $keys.PrimaryKey) {
    throw "Could not obtain Primary Key. Script failed and will end now."
}

# Get the Primary Key of the Shared Access Policy
$primaryKey = ($keys.PrimaryKey)
Write-Host $primaryKey

## Check that the primary key is not empty.

# Define a function to create a SAS token (similar to the C# code provided)
function Create-SasToken {
    param (
        [string]$resourceUri, [string]$keyName, [string]$key
    )

$sinceEpoch = [datetime]::UtcNow - [datetime]"1970-01-01"
    $expiry = [int]$sinceEpoch.TotalSeconds + ((60 * 60 * 24) * [int]$tokenExpireInDays) # seconds since Unix epoch
    $stringToSign = [System.Web.HttpUtility]::UrlEncode($resourceUri) + "`n" + $expiry
    $hmac = New-Object System.Security.Cryptography.HMACSHA256
    $hmac.Key = [Text.Encoding]::UTF8.GetBytes($key)
    $signature = [Convert]::ToBase64String($hmac.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign)))
    $sasToken = "SharedAccessSignature sr=$([System.Web.HttpUtility]::UrlEncode($resourceUri))&sig=$([System.Web.HttpUtility]::UrlEncode($signature))&se=$expiry&skn=$keyName"
    return $sasToken
}

# Construct the resource URI for the SAS token
$resourceUri = "https://$namespaceName.servicebus.windows.net/$eventHubName"

# Generate the SAS token using the primary key from the new policy
$sasToken = Create-SasToken -resourceUri $resourceUri -keyName $policyName -key $primaryKey

# Output the SAS token
Write-Output @"
-- Generated SAS Token --
$sasToken
-- End of generated SAS Token --
"@
}

Generate-SasToken

Available on Microsoft learn website.

With a SAS token available, we will configure the SQL Server:

USE db_20_CES;
GO

-- Create the Master Key with a password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>';

CREATE DATABASE SCOPED CREDENTIAL <CredentialName>
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'xxxxxxxxxxxx' -- Be sure to copy the entire token. The SAS token starts with "SharedAccessSignature sr="

EXEC sys.sp_enable_event_stream

EXEC sys.sp_create_event_stream_group
    @stream_group_name =      N'<EventStreamGroupName>',
    @destination_type =       N'AzureEventHubsAmqp',
    @destination_location =   N'<AzureEventHubsHostName>/<EventHubsInstance>',
    @destination_credential = <CredentialName>,
    @max_message_size_kb =    <MaxMessageSize>,
    @partition_key_scheme =   N'<PartitionKeyScheme>'

EXEC sys.sp_add_object_to_event_stream_group
    N'<EventStreamGroupName>',
    N'<SchemaName>.<TableName>'

Tomorrow we will continue to explore Change event streaming and setup everything in Azure .

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION
18. Dec 18: Microsoft SQL Server 2025 – Optimized locking

This function returns the current database system date as a date value, without the database time and time zone offset. CURRENT_DATE derives this value from the underlying operating system on the Database Engine runs. This function is eqvivalent to CAST(GETDATE() AS DATE).

Let’s check the precisions to see, where we will lose the precision based on the system date/datetime functions.

SELECT CURRENT_DATE;
GO

-- precisions
SELECT 
		 SYSDATETIME() AS Sys_datetime
		,SYSDATETIMEOFFSET() AS sys_DatetimeOffset
		,SYSUTCDATETIME() AS SysUTC_Datetime
		,CURRENT_TIMESTAMP AS Curr_Timestamp
		,GETDATE() AS Get_date
		,GETUTCDATE() as Get_UTCDate
		,CURRENT_DATE AS current_d
		,CAST(GETDATE() AS DATE) as current_d_cast

Function current_date can be used also as default when creating a table:

DROP TABLE IF EXISTS dbo.TEST;
GO

CREATE TABLE dbo.test
(ID INT IDENTITY(1,1) NOT NULL
,tt CHAR(10) NULL
,dd DATE NOT NULL DEFAULT CURRENT_DATE
,ddtt SMALLDATETIME NOT NULL DEFAULT DATEADD(DAY,1,CURRENT_DATE)
)

INSERT INTO dbo.test (tt, dd)
SELECT 'aaa','2025-12-19' UNION ALL
SELECT 'bbb','2025-12-20 05:26:46.947' UNION ALL
SELECT 'ccc',GETDATE() UNION ALL
SELECT 'ddd',DATEADD(DAY, 8,CAST(GETDATE() AS DATE)) union all
select 'eee',CURRENT_TIMESTAMP 

SELECT * FROM dbo.TEST

where current date and time (of making this test was: 19th December 2025, 06.20 AM CET).

Tomorrow we will look into the new CES feature in SQL Server 2025.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!

1. Dec 01: Microsoft SQL Server 2025 – Introduction and installation
2. Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
3. Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
4. Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
5. Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
6. Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE()
7. Dec 07: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_SUBSTR() and REGEXP_REPLACE()
8. Dec 08: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_INSTR() and REGEXP_COUNT()
9. Dec 09: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE()
10. Dec 10: Microsoft SQL Server 2025 – External REST endpoint invocation
11. Dec 11: Microsoft SQL Server 2025 – External REST endpoint invocation using LLM
12. Dec 12: Microsoft SQL Server 2025 – New vector data type and Vector functions
13. Dec 13: Microsoft SQL Server 2025 – Vector functions
14. Dec 14: Microsoft SQL Server 2025 – Vector arrays or embeddings with function AI_GENERATE_EMBEDDINGS
15. Dec 15: Microsoft SQL Server 2025 – T-SQL functions for AI External_Models, Chunks and Embeddings
16. Dec 16: Microsoft SQL Server 2025 – Optional parameter plan optimization (OPPO)
17. Dec 17: Microsoft SQL Server 2025 – Query hint ABORT_QUERY_EXECUTION

Optimized locking is a new feature in SQL Server 2025. It helps to reduce lock memory as very few locks are held even for large transactions. In addition, optimized locking avoids lock escalations and can avoid certain types of deadlocks. This allows more concurrent access to the table.

Optimized locking is composed of two primary components:
– transaction ID (TID) locking and
– lock after qualification (LAQ).

The logic behind is that

• Without optimized locking, updating 1,000 rows in a table might require 1,000 exclusive (X) row locks held until the end of the transaction.
• With optimized locking, updating 1,000 rows in a table might require 1,000 X row locks but each lock is released as soon as each row is updated, and only one X TID lock is held until the end of the transaction. Because locks are released quickly, lock memory usage is reduced and lock escalation is much less likely to occur, improving workload concurrency.

Check for optimized locking

First we need to check if the optimized locking is set to on:

CREATE DATABASE db_18_OptimizedLocking;
GO

USE db_18_OptimizedLocking;
GO

SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();

And it is turned off! It si enabled per database and is it by default disabled!

Let’s prepare the sample data:

DROP TABLE IF EXISTS dbo.TestTable
GO
CREATE TABLE dbo.TestTable
(
ID INT NOT NULL,
Val INT
);

INSERT INTO dbo.TestTable (ID, Val) 
VALUES (1,10),(2,20),(3,30);
GO

SELECT * FROM dbo.TestTable
GO

Test without optimized locking

We will open three NEW queries and in each new file, we will copy / paste two concurrent update statements, the first will change the row with the ID = 1 (session 1) and the second one tries to change the row with the ID = 2 (session 2). In the last one (right pane – session 3) we placed the statement to show you the locks.

The second statement is blocked, because it can not acquire the necessary Update Lock which is needed to update the row:

-- file 1 (Session 1)
USE db_18_OptimizedLocking;
go

BEGIN transaction
UPDATE TestTable
SET val = val + 10
where id = 1

-- COMMIT TRANSACTION
ROLLBACK;
GO


-- file 2 (Session 2)
USE db_18_OptimizedLocking;
go

begin transaction
update TestTable
SET  val = val + 30
WHERE id = 2

-- COMMIT TRANSACTION
ROLLBACK
GO


-- file 3 (Session 3)
select 
    resource_type,
    resource_database_id,
    resource_associated_entity_id,
   -- resource_description,
    request_mode,
    request_session_id,
    request_status

 from sys.dm_tran_locks;
GO

We see the lock

Test with optimized locking on (ADR on and RSCI off)

Now we will enable the optimized locking by enabling ADR before hand.

USE [master]
GO
ALTER DATABASE [db_18_OptimizedLocking] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
ALTER DATABASE [db_18_OptimizedLocking] SET OPTIMIZED_LOCKING = ON;
GO

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS IsOptimizedLockingOn
GO

We get the enabled everything except RSCI (which is off).

And we will repeat the same test again:

And we see the new resource type of request mode = X was granted

TEst with optimized locking on

Test with optimized locking on (ADR on and RSCI on!)

We will turn on the RSCI:

ALTER DATABASE [db_18_OptimizedLocking] 
SET READ_COMMITTED_SNAPSHOT ON;
GO

With all the features turned on:

And with the READ COMMITTED SNAPSHOT ON we get the resource type XACT with the request mode = X, which is update immediatelly and ready for new updates, without any locks (both updates are executed)

and for both updates we get granted transaction lock (resource_type = XACT and request_mode = X):

When Optimized Locking is enabled, the query in session 2 is not blocked because update (U) locks are not acquired. Thanks to the Transactions ID (TID) and Lock After Qualifications (LAQ), Optimized locking reduces the memory consumption and reduced locks between concurrent transactions.

Tomorrow we will look into the new CURRENT_DATE in SQL Server 2025.

As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025

Happy coding!