Hey guys!

I'm going to participate in a live TODAY, on Canal .NET, with my friend Renato Groffe, with the theme of “Database Tips for Developers”. We're going to talk about various topics and I want to show a demo using AI and SQL Server, regular expression and some other very interesting things.

When:03
TODAY – 09/03/2025 – from 9:00 pm – Brasília time

Register at:
https://bit.ly/dicas-databases-devs-11aed

 

Stream link:

 

The New JSON Data Type: Performance and Integrity

Until SQL Server 2022, we stored JSON as a string (NVARCHAR). Did it work? Yes. But we didn't have native validation at the engine level and storage performance wasn't ideal. In SQL Server 2025 we have the type JSON native.

Observation: The JSON type is not just an “alias” for string. It stores data in an optimized binary format, which reduces parsing overhead in repetitive queries.

Example: Creating and Validating JSON

IF (OBJECT_ID('tempdb..#Tb_Vendas') IS NOT NULL)
    DROP TABLE [#Tb_Vendas];

CREATE TABLE [#Tb_Vendas]
(
    [Nr_Id] INT IDENTITY(1, 1) PRIMARY KEY,
    [Ds_Dados_Venda] json NOT NULL -- Novo tipo nativo

)
WITH (DATA_COMPRESSION = PAGE);

-- ADICIONA DADO VÁLIDO
INSERT INTO [#Tb_Vendas]
(
    [Ds_Dados_Venda]
)
VALUES
('{"Nm_Produto": "SQL Server 2025", "Nr_Valor": 5000.00, "Fl_Ativo": 1}');

-- TENTATIVA DE INSERIR JSON INVÁLIDO (VAI GERAR ERRO DE ENGINE)
UPDATE [#Tb_Vendas] SET [Ds_Dados_Venda] = 'Texto comum' WHERE [Nr_Id] = 1;

External Calls: sp_invoke_external_rest_endpoint

This is the feature that separates the boys from the men. Now, SQL Server can “talk” to the outside world without relying on custom CLR or Integration Services packages for simple tasks. We can consume translation APIs, sentiment analysis or even the Azure OpenAI.

CRITICAL ALERT: Network calls from within the database can introduce latency and increase wait times. Always monitor the Wait Type EXTERNAL_SCRIPT_NETWORK_IO or equivalents to ensure that the network is not killing its transaction throughput.

Security with Database Scoped Credentials

Never put your API Key directly in the script. Use database scope credentials to anonymize access.

-- CRIANDO A CREDENCIAL PARA ACESSO À API
IF NOT EXISTS
(
    SELECT *
    FROM [sys].[database_scoped_credentials]
    WHERE [name] = 'https://api.openai.com'
)
BEGIN

    CREATE DATABASE SCOPED CREDENTIAL [https://api.openai.com]
    WITH IDENTITY = 'API_KEY',
         SECRET = 'Sua_Chave_Aqui';

END;

Native Regular Expressions (Regex)

After decades of asking, SQL Server has finally delivered Regex functions. This puts an end to those endless workarounds of LIKE and SUBSTRING to validate emails or mask sensitive data.

The new main functions are:

  • REGEXP_LIKE: Checks if a string follows a pattern.
  • REGEXP_REPLACE: Replaces parts of the string based on patterns.
  • REGEXP_INSTR: Returns the position of a pattern.

Example: Email Validation and PII Masking

IF (OBJECT_ID('tempdb..#Tb_Usuarios') IS NOT NULL)
    DROP TABLE [#Tb_Usuarios];

CREATE TABLE [#Tb_Usuarios]
(
    [Nr_Id] TINYINT IDENTITY(1, 1),
    [Nm_Usuario] NVARCHAR(100) COLLATE Latin1_General_CI_AS,
    [Ds_Email] NVARCHAR(150) COLLATE Latin1_General_CI_AS,
    [Ds_Cartao] NVARCHAR(20) COLLATE Latin1_General_CI_AS
)
WITH (DATA_COMPRESSION = PAGE);

INSERT INTO [#Tb_Usuarios]
(
    [Nm_Usuario],
    [Ds_Email],
    [Ds_Cartao]
)
VALUES
('Dirceu Resende', 'contato@dirceuresende.com', '1234-5678-9012-3456'),
('Thiago Bertuzzi', 'bertuzzi.java@whatever.com', '9876-5432-1098-7654');

-- VALIDANDO E-MAILS COM REGEXP_LIKE
SELECT
	[Nm_Usuario],
	[Ds_Email]
FROM
	[#Tb_Usuarios]
WHERE
	REGEXP_LIKE([Ds_Email], '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$')

-- MASCARANDO CARTÃO COM REGEXP_REPLACE
SELECT
	[Nm_Usuario],
	REGEXP_REPLACE([Ds_Cartao], '\d{4}-\d{4}-\d{4}-', '--****-') AS [Ds_Cartao_Mascarado]
FROM
	[#Tb_Usuarios];

SQL Server 2025 is bringing the database into the age of AI and tight integration. As DBAs, our role is to understand how these tools work internally to ensure that flexibility does not come with slowness.

I hope you liked this tip, a big hug and see you next time!