SQL Server | Lenni's Technology Blog

Multiplicative Aggregates with the PRODUCT Function in SQL Server 2025

August 30, 2026 — Leonard Lobel

This blog post explores the new PRODUCT function in SQL Server 2025, which calculates the product of a set of numeric values — similar to how SUM and AVG work for addition and averaging, but for multiplication.

Prior to SQL Server 2025, SQL Server lacked a built-in way to compute the product of values in a set. You had to use workarounds like looping or user-defined aggregates. With PRODUCT, this is now a simple one-line expression.

PRODUCT supports both aggregate and analytic (windowed) forms and works with both ALL values (default) and DISTINCT values. Nulls are ignored, and the function is compatible with all numeric types except bit.

Compute Product of Prices for Each Product

The first example illustrates how to use the new PRODUCT aggregate function in SQL Server 2025 to calculate the cumulative product of prices for each product across multiple orders. It also shows how to compute the product considering only distinct price values.

			
CREATE TABLE OrderDetail (
    OrderId     int,
    ProductId   int,
    Price       decimal(10, 4)
)
INSERT INTO OrderDetail
  (OrderId, ProductId,  Price) VALUES
  (1,       101,        136.87),
  (1,       102,        29.57),
  (1,       103,        396.85),
  (2,       101,        136.87),
  (2,       102,        29.57),
  (3,       101,        136.87),
  (3,       102,        29.57),
  (4,       101,        149.22),
  (4,       102,        29.57)
-- Compute product of all prices and distinct prices for each ProductId
SELECT
    ProductId,
    ProductOfPrices = PRODUCT(Price),
    ProductOfDistinctPrices = PRODUCT(DISTINCT Price)
FROM
    OrderDetail
GROUP BY
    ProductId

		

Result:

ProductId	ProductOfPrices	ProductOfDistinctPrices
101	382606053.829162	20423.741400
102	764548.953348	29.570000
103	396.850000	396.850000

Alternative using `OVER (PARTITION BY ...)`

This version computes the product for each row using a windowed aggregate (that is, using OVER rather than GROUP BY). This allows you to retain the detail rows (which were lost in the previous GROUP BY query) while also showing the total product per partition.

			
SELECT
    ProductId,
    OrderId,
    Price,
    ProductOfPrices = PRODUCT(Price) OVER (PARTITION BY ProductId)
FROM
    OrderDetail
ORDER BY
    ProductId,
    OrderId

		

Result:

ProductId	OrderId	Price	ProductOfPrices
101	1	136.8700	382606053.829162
101	2	136.8700	382606053.829162
101	3	136.8700	382606053.829162
101	4	149.2200	382606053.829162
102	1	29.5700	764548.953348
102	2	29.5700	764548.953348
102	3	29.5700	764548.953348
102	4	29.5700	764548.953348
103	1	396.8500	396.850000

Compounded Return from Periodic Rates

The next example uses PRODUCT to compute the compounded return for financial instruments over multiple time periods.

			
CREATE TABLE Instrument (
    InstrumentId    varchar(10),
    Period          tinyint,
    RateOfReturn    decimal(10, 4)
)
INSERT INTO Instrument
  (InstrumentId,    Period, RateOfReturn) VALUES
  ('BOND1',         1,      0.035),
  ('BOND1',         2,      0.0275),
  ('BOND1',         3,      0.0325),
  ('ETF1',          1,      0.08),
  ('ETF1',          2,      -0.045),
  ('ETF1',          3,      0.06),
  ('STOCK1',        1,      0.125),
  ('STOCK1',        2,      0.095),
  ('STOCK1',        3,      0.113)
-- Compute compounded return for each instrument
SELECT
  InstrumentId,
  CompoundedReturn = PRODUCT(1 + RateOfReturn) - 1,
  CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) - 1) * 100, 'N1') || '%'
FROM
  Instrument
GROUP BY
  InstrumentId

		

Result:

InstrumentId	CompoundedReturn	CompoundedReturnPercentage
BOND1	0.098026	9.8%
ETF1	0.093284	9.3%
STOCK1	0.371077	37.1%

The above query calculates the compounded return for each instrument by taking the product of (1 + RateOfReturn) for all periods and then subtracting 1 to return the CompoundedReturn column. The CompoundedReturnPercentage column shows the same value formatted for display as a percentage with one decimal place.

Using BOND1 as an example, the calculation would be:

(1 + 0.035) = 1.035	*	Period 1 return
(1 + 0.0275) = 1.0275	*	Period 2 return
(1 + 0.0325) = 1.0325	=	Period 3 return
1.098026	– 1 =	Growth factor (includes the original principal $1)
0.098026	=	Compounded return (i.e., the percentage gain)
9.8%		Isolated profit/loss percentage

Alternative using `OVER (PARTITION BY ...)`

Like the first example, this version uses windowing with OVER to calculate the compounded return for each individual row.

			
SELECT
  InstrumentId,
  Period,
  RateOfReturn,
  CompoundedReturn = PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1,
  CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1) * 100,'N1') || '%'
FROM
  Instrument
ORDER BY
  InstrumentId,
  Period

		

Result:

InstrumentId	Period	RateOfReturn	CompoundedReturn	CompoundedReturnPercentage
BOND1	1	0.0350	0.098026	9.8%
BOND1	2	0.0275	0.098026	9.8%
BOND1	3	0.0325	0.098026	9.8%
ETF1	1	0.0800	0.093284	9.3%
ETF1	2	-0.0450	0.093284	9.3%
ETF1	3	0.0600	0.093284	9.3%
STOCK1	1	0.1250	0.371077	37.1%
STOCK1	2	0.0950	0.371077	37.1%
STOCK1	3	0.1130	0.371077	37.1%

Summary

The new PRODUCT function in SQL Server 2025 brings native multiplicative aggregation to T-SQL, eliminating the need for workarounds when calculating the product of a set of numeric values. It supports standard aggregation with GROUP BY, including DISTINCT, as well as analytic calculations using OVER (PARTITION BY ...) to preserve individual detail rows. As we demonstrated with product prices and compounded investment returns, PRODUCT makes calculations that depend on multiplying values across a set simpler and more expressive.

Happy coding!

Posted in Azure SQL Database, SQL Server, SQL Server 2025. Leave a Comment »

Base64 Encoding and Decoding in SQL Server 2025 and Azure SQL Database

June 5, 2026 — Leonard Lobel

Base64 encoding is a method of converting binary data into an ASCII string format by translating it into a radix-64 representation. Base64 decoding is the reverse process, converting an ASCII string back into binary data. Encoding is often used to safely transmit binary data over text-based protocols, such as HTTP or email, though note that encoding binary data increases its size by approximately 33%.

This blog post demonstrates how to use the new BASE64_ENCODE and BASE64_DECODE functions in T-SQL. The examples include both standard Base64 encoding (which may include characters like + and / that are not URL-safe) and URL-safe Base64 encoding (which replaces + with - and / with _).

Encoding with `BASE64_ENCODE` and Decoding with `BASE64_DECODE`

First let’s perform standard Base64 encoding and decoding. Run this code snippet to encode a binary value into a string using BASE64_ENCODE:

			
DECLARE @Binary varbinary(max) = 0xCAFECAFE
SELECT StandardEncoded = BASE64_ENCODE(@Binary)

Result:

yv7K/g==

Now use BASE64_DECODE to convert the encoded string back to its original binary form:

			
DECLARE @StandardEncoded varchar(max) = 'yv7K/g=='
SELECT Binary = BASE64_DECODE(@StandardEncoded)

Result:

0xCAFECAFE

Notice how the encoded string for the same binary content contains characters like / and +, which are not safe for use in URLs.

URL-Safe Base64 Encoding

To create a URL-safe version, use the second parameter of BASE64_ENCODE (where 1 means to perform URL-safe encoding):

			
DECLARE @Binary varbinary(max) = 0xCAFECAFE
SELECT UrlSafeEncoded = BASE64_ENCODE(@Binary, 1)

Result:

yv7K_g

Notice that the URL-safe encoded string replaces / with _ (it would also replace + with -). And now, use BASE64_DECODE to convert the URL-safe encoded string back to its original binary form:

			
DECLARE @UrlSafeDecoded varchar(max) = 'yv7K_g'
SELECT Binary = BASE64_DECODE(@UrlSafeDecoded)

Result:

0xCAFECAFE

You can see that both the standard and URL-safe encoded strings decode back to the same original binary data.

So then, why not always use URL-safe Base64 encoding? Although BASE64_DECODE can handle both standard and URL-safe Base64 formats in SQL Server, standard Base64 remains the default because it aligns with established standards (RFC 4648) and ensures compatibility with most systems, libraries, and protocols that expect the traditional +, /, and = characters. The URL-safe variant should be used only when the encoded data will appear in contexts like URLs, filenames, or cookies that restrict those characters. In short, use standard Base64 for interoperability and URL-safe Base64 only when required by the target environment.

Practical Examples

Here is a more practical example of using Base64 encoding and decoding in T-SQL. In this case, we will construct a JSON object that includes an embedded image. With the binary image content embedded as a Base64-encoded string, the JSON object can be easily transmitted or stored in text-based formats. In this case, the JSON object could be used in web applications, REST APIs, or other scenarios where images need to be included in JSON responses. Note that the same approach can be used for embedding images in HTML or XML documents, all using T-SQL.

			
DECLARE @ProductId int = 6
DECLARE @ProductImage varbinary(max) = 0xCAFECAFE89504E470D0A1A0A0000000D49484452
SELECT
  ProductJson = JSON_OBJECT(
    'productId' : @ProductId,
    'thumbnail' : 'data:image/png;base64,' || BASE64_ENCODE(@ProductImage)
  )

		

Result:

			
{"productId":6,"thumbnail":"data:image/png;base64,yv7K/olQTkcNChoKAAAADUlIRFI="}

Here is another example, where we construct a binary token for use in a URL. The token is first created as a binary value, then encoded using URL-safe Base64 encoding, and finally embedded into a URL string. This approach is useful for securely transmitting tokens in web applications via a URL, such as for authentication or authorization purposes.

			
DECLARE @Token varbinary(max) = CAST('user1|expiry=2025-12-31' AS varbinary)
SELECT @Token
DECLARE @UrlSafeEncodedToken varchar(max) = BASE64_ENCODE(@Token, 1)
SELECT @UrlSafeEncodedToken
DECLARE @Url varchar(max) = 'https://api.myapp.com/download?token=' || @UrlSafeEncodedToken
SELECT @Url

		

First, the string token is represented as binary data:

0x75736572317C6578706972793D323032352D31322D3331

Then it is encoded using URL-safe Base64:

dXNlcjF8ZXhwaXJ5PTIwMjUtMTItMzE

Finally, that encoded token is placed directly in the URL:

https://api.myapp.com/download?token=dXNlcjF8ZXhwaXJ5PTIwMjUtMTItMzE

Observe that the resulting URL contains a token that is safe for inclusion in URLs, thanks to the URL-safe Base64 encoding. Also notice the use of the ANSI SQL standard concatenation operator (||) to build the URL string, as you learned about in the previous lab.

Summary

The new BASE64_ENCODE and BASE64_DECODE functions are small additions that solve a very practical problem. They make it easy to convert binary values into portable text and back again, directly in T-SQL.

Use standard Base64 when you need conventional encoded output, and use URL-safe Base64 when the encoded value will appear in a URL, token, route segment, or query string. Just remember that Base64 is not encryption, and that encoding increases the size of the data.

Posted in Azure SQL Database, SQL Server, SQL Server 2025. Leave a Comment »

Regular Expressions Have Finally Arrived in SQL Server 2025 and Azure SQL Database

April 13, 2026 — Leonard Lobel

Regular expression support has been one of those “how do we still not have this?” features in T-SQL for a very long time. SQL Server developers have always been able to do basic pattern matching with LIKE, and slightly more advanced wildcard searches with PATINDEX, but neither of those features provides true regex capabilities.

That limitation has historically forced developers into awkward workarounds: chains of CHARINDEX and SUBSTRING, complex LIKE predicates, CLR functions, application-side validation, ETL cleanup steps, or other approaches that were harder to read, harder to maintain, and often less expressive than a regular expression.

SQL Server 2025 and Azure SQL Database now include native regular expression functions for matching, counting, extracting, locating, replacing, returning matches as rows, and splitting text into rows. Microsoft’s documentation lists these functions as applying to SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric.

The examples below use a simple Review table to demonstrate the new regex support. I’ll keep the regex explanations inside the SQL comments, so the walkthrough can focus on what each query is doing and what output to expect.

Create the sample table and data

			
CREATE TABLE Review(
  ReviewId    int IDENTITY PRIMARY KEY,
  Name        varchar(50) NOT NULL,
  Email       varchar(150),
  Phone       varchar(20),
  ReviewText  varchar(1000)
)
GO
INSERT INTO Review
 (Name,                     Email,                      Phone,          ReviewText) VALUES
 ('John Doe',               'john@contoso.com',         '123-4567890',  'This product is excellent! I really like the build quality and design. #excellent #quality'),
 ('Alice Smith',            'alice@fabrikam@com',       '234-567-81',   'Good value for money, but the software is terrible.'),
 ('Mary Jo Anne Erickson',  'mary.jo.anne@acme.co.uk',  '456-789-1234', 'Poor battery life,   bad camera performance,   and poor build quality. #poor'),
 ('Max Wong',               'max@fabrikam.com',         NULL,           'Excellent service from the support team, highly recommended!' || char(9) || '#goodservice #recommended'),
 ('Bob Johnson',            'bob.fabrikam.net',         '345-678-9012', 'The product is good, but delivery was delayed.' || char(13) || char(10) || 'Overall, decent experience.'),
 ('Terri S Duffy',          'terri.duffy@acme.com',     '678-901-2345', 'Battery life is weak, camera quality is poor. #aweful'),
 ('Eve Jones',              NULL,                       '456-789-0123', 'I love this product, it''s great! #fantastic #amazing'),
 ('Charlie Brown',          'charlie@contoso.co.in',    '587-890-1234', 'I hate this product, it''s terrible!')

		

This establishes the sample data used throughout the rest of the walkthrough.

REGEXP_LIKE

REGEXP_LIKE is used to test whether a value matches a regular expression pattern. It is especially useful in WHERE clauses.

Get rows with valid email addresses

			
SELECT
  ReviewId,
  Name,
  Email
FROM
  Review
WHERE
  REGEXP_LIKE(
    Email,
    '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
/*
    ^                    Start of string
    [a-zA-Z0-9._%+-]+    One or more letters, digits, dot, underscore, percent, plus, or hyphen
    @                    Literal @ before the domain
    [a-zA-Z0-9.-]+       One or more letters, digits, dot, or hyphen for the domain name
    \.                   Literal . before the top-level domain
    [a-zA-Z]{2,}         At least two letters for the top-level domain (e.g., com, org, co, uk)
    $                    End of string
*/
  )

		

ReviewId	Name	Email
1	John Doe	john@contoso.com
3	Mary Jo Anne Erickson	mary.jo.anne@acme.co.uk
4	Max Wong	max@fabrikam.com
6	Terri S Duffy	terri.duffy@acme.com
8	Charlie Brown	charlie@contoso.co.in

Get rows with valid email addresses that end with .com

			
SELECT
  ReviewId,
  Name,
  Email
FROM
  Review
WHERE
  REGEXP_LIKE(
    Email,
    '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.com$'
/*
    ^                    Start of string
    [a-zA-Z0-9._%+-]+    One or more letters, digits, dot, underscore, percent, plus, or hyphen
    @                    Literal @ before the domain
    [a-zA-Z0-9.-]+       One or more letters, digits, dot, or hyphen for the domain name
    \.com                Literal . before the top-level domain which must be "com"
    $                    End of string
*/
  )

		

ReviewId	Name	Email
1	John Doe	john@contoso.com
4	Max Wong	max@fabrikam.com
6	Terri S Duffy	terri.duffy@acme.com

Get rows with valid phone numbers

			
SELECT
  ReviewId,
  Name,
  Phone
FROM
  Review
WHERE
  REGEXP_LIKE(
    Phone,
    '^(\d{3})-(\d{3})-(\d{4})$'
/*
    ^           Start of string
    (\d{3})     Match exactly 3 digits (area code)
    -           Literal hyphen
    (\d{3})     Match exactly 3 digits (first part of the phone number)
    -           Literal hyphen
    (\d{4})     Match exactly 4 digits (second part of the phone number)
    $           End of string
*/
    )

		

ReviewId	Name	Phone
3	Mary Jo Anne Erickson	456-789-1234
5	Bob Johnson	345-678-9012
6	Terri S Duffy	678-901-2345
7	Eve Jones	456-789-0123
8	Charlie Brown	587-890-1234

REGEXP_COUNT

REGEXP_COUNT counts the number of times a regex pattern occurs in a string. It can also be used to return validation flags in the SELECT list.

Indicate valid and invalid email addresses and phone numbers

			
SELECT
  ReviewId,
  Name,
  Email,
  IsEmailValid  = CASE WHEN REGEXP_COUNT(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') = 1 THEN 1 ELSE 0 END,
  Phone,
  IsPhoneValid  = CASE WHEN REGEXP_COUNT(Phone, '^(\d{3})-(\d{3})-(\d{4})$') = 1 THEN 1 ELSE 0 END
FROM
  Review

		

ReviewId	Name	Email	IsEmailValid	Phone	IsPhoneValid
1	John Doe	john@contoso.com	1	123-4567890	0
2	Alice Smith	alice@fabrikam@com	0	234-567-81	0
3	Mary Jo Anne Erickson	mary.jo.anne@acme.co.uk	1	456-789-1234	1
4	Max Wong	max@fabrikam.com	1		0
5	Bob Johnson	bob.fabrikam.net	0	345-678-9012	1
6	Terri S Duffy	terri.duffy@acme.com	1	678-901-2345	1
7	Eve Jones		0	456-789-0123	1
8	Charlie Brown	charlie@contoso.co.in	1	587-890-1234	1

Count the number of vowels in each name

			
SELECT
  Name,
  VowelCount = REGEXP_COUNT(
    Name,
    '[AEIOU]',  -- match any single vowel
    1,      -- start position
    'i'     -- case insensitive flag (match uppercase or lowercase vowels)
  )  
FROM
  Review

		

Name	VowelCount
John Doe	3
Alice Smith	4
Mary Jo Anne Erickson	7
Max Wong	2
Bob Johnson	3
Terri S Duffy	3
Eve Jones	4
Charlie Brown	4

Count specific “sentiment” words from each review

			
SELECT
  Name,
  ReviewText,
  GoodSentimentWordCount = REGEXP_COUNT(
    ReviewText,
    '\b(excellent|great|good|love|like)\b',
/*
    \b                                  start of word boundary
    (excellent|great|good|love|like)    match any of the words in the parentheses
    \b                                  end of word boundary            
*/
    1,    -- start position
    'i'   -- case insensitive flag (match uppercase or lowercase words)
  ),
  BadSentimentWordCount = REGEXP_COUNT(
    ReviewText,
    '\b(bad|poor|terrible|hate)\b',
/*
    \b                          start of word boundary
    (bad|poor|terrible|hate)    match any of the words in the parentheses
    \b                          end of word boundary            
*/
    1,    -- start position
    'i'   -- case insensitive flag (match uppercase or lowercase words)
  )
FROM
  Review

		

Name	GoodSentimentWordCount	BadSentimentWordCount
John Doe	3	0
Alice Smith	1	1
Mary Jo Anne Erickson	0	4
Max Wong	1	0
Bob Johnson	1	0
Terri S Duffy	0	1
Eve Jones	2	0
Charlie Brown	0	2

CHECK constraints

Regex support is also useful for enforcing data quality rules with CHECK constraints. Before those constraints can be added, existing invalid data has to be removed or corrected.

Attempt to add constraints before cleaning the data

			
-- Cannot add check constraints to ensure valid email addresses and phone numbers in the Review table
ALTER TABLE Review 
 ADD CONSTRAINT CK_ValidEmail CHECK (Email IS NULL OR REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'))
ALTER TABLE Review 
 ADD CONSTRAINT CK_ValidPhone CHECK (Phone IS NULL OR REGEXP_LIKE(Phone, '^(\d{3})-(\d{3})-(\d{4})$'))

		

If executed, these constraints would fail against the existing data, because the current table still contains invalid email addresses and phone numbers.

Delete rows with invalid email addresses and phone numbers

Let’s use the REGEXP_LIKE function in the WHERE clause of a DELETE statement to remove all rows with invalid email addresses or phone numbers:

			
DELETE FROM Review
WHERE
  (Email IS NOT NULL AND NOT REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')) OR
  (Phone IS NOT NULL AND NOT REGEXP_LIKE(Phone, '^(\d{3})-(\d{3})-(\d{4})$'))
SELECT * FROM Review

		

ReviewId	Name	Email	Phone
3	Mary Jo Anne Erickson	mary.jo.anne@acme.co.uk	456-789-1234
4	Max Wong	max@fabrikam.com
6	Terri S Duffy	terri.duffy@acme.com	678-901-2345
7	Eve Jones		456-789-0123
8	Charlie Brown	charlie@contoso.co.in	587-890-1234

Add CHECK constraints

With only valid data remaining in the table, the two check constraints can now be created:

			
-- Add check constraints to ensure valid email addresses and phone numbers in the Review table
ALTER TABLE Review 
 ADD CONSTRAINT CK_ValidEmail CHECK (Email IS NULL OR REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'))
ALTER TABLE Review 
 ADD CONSTRAINT CK_ValidPhone CHECK (Phone IS NULL OR REGEXP_LIKE(Phone, '^(\d{3})-(\d{3})-(\d{4})$'))

		

Now test the constraints by trying to insert invalid values:

			
-- Try and fail to insert an invalid email address or phone number
INSERT INTO Review VALUES
 ('Invalid Email', 'invalid-email@com', '123-456-7890', 'Review')
INSERT INTO Review VALUES
 ('Invalid Phone', 'valid-email@gmail.com', '234-342-INVALID', 'Review')

		

Both of these statements fail, because the inserted values violate the regex-based CHECK constraints.

However, data that satisfies the check constraints is valid:

			
INSERT INTO Review
 (Name,             Email,                          Phone,          ReviewText) VALUES
 ('John Doe',       'john@fabrikam.com',            '123-456-7890', 'This product is excellent! I really like the build quality and design. #excellent #quality'),
 ('Alice Smith',    'alice.smith@fabrikam.co.uk',   '234-567-8195', 'Good value for money, but the software is terrible.'),
 ('Bob Johnson',    'bob@fabrikam.com',             '345-678-9012', 'The product is good, but delivery was delayed. Overall, decent experience.'),
 ('Stuart Green',   'stuart.green@acme.com',        '456-789-0123', 'Pretty good product, I am enjoying it!')

		

These rows satisfy the regex-based constraints, so the insert succeeds. The following SELECT * is included in the code, but the output is not repeated here because it is just the table contents after the insert.

REGEXP_SUBSTR

REGEXP_SUBSTR extracts a substring that matches a regex pattern.

Extract the domain name of each valid email address

			
-- Extract the domain name of each valid email address
SELECT
  ReviewId,
  Name,
  Email,
  DomainName = REGEXP_SUBSTR(
    Email,
    '@(.+)$',   -- @ = literal at-symbol; (.+)$ = capture everything after the at-symbol
    1,          -- start position
    1,          -- return the first occurrence
    'c',        -- enable capture group indexing
    1)          -- return the first capture group, which is the domain name
FROM
  Review

		

ReviewId	Name	Email	DomainName
3	Mary Jo Anne Erickson	mary.jo.anne@acme.co.uk	acme.co.uk
4	Max Wong	max@fabrikam.com	fabrikam.com
6	Terri S Duffy	terri.duffy@acme.com	acme.com
7	Eve Jones
8	Charlie Brown	charlie@contoso.co.in	contoso.co.in
9	John Doe	john@fabrikam.com	fabrikam.com
10	Alice Smith	alice.smith@fabrikam.co.uk	fabrikam.co.uk
11	Bob Johnson	bob@fabrikam.com	fabrikam.com
12	Stuart Green	stuart.green@acme.com	acme.com

Show how many email addresses there are for each domain

			
;WITH DomainNameCte AS (
  SELECT 
    DomainName = REGEXP_SUBSTR(
      Email,
      '@(.+)$',   -- @ = literal at-symbol; (.+)$ = capture everything after the at-symbol
      1,          -- start position
      1,          -- return the first occurrence
      'c',        -- enable capture group indexing
      1)          -- return the first capture group, which is the domain name
  FROM
    Review
)
SELECT
  DomainName,
  DomainCount = COUNT(*)
FROM
  DomainNameCte
GROUP BY
  DomainName
ORDER BY
  DomainName

		

DomainName	DomainCount
	1
acme.co.uk	1
acme.com	2
contoso.co.in	1
fabrikam.co.uk	1
fabrikam.com	3

REGEXP_INSTR

REGEXP_INSTR returns the position of a regex match.

Find the position of the @ character and the first . after it

			
SELECT
  ReviewId,
  Name,
  Email,
  At          = REGEXP_INSTR(Email, '@'),   -- match the at-symbol; simple scenario (could also achieve with CHARINDEX)
  DotAfterAt  = REGEXP_INSTR(Email,
    '@[^@]*?(\.)',  -- @ = match the at-symbol; [^@]*? = match everything after the at-symbol until the first dot; (\.) = capture the first dot after the at-symbol
    1,              -- start position
    1,              -- return the first occurrence
    0,              -- return the position of the match (not the psition after it)
    'c',            -- enable capture group indexing
    1               -- return the position of the first capture group, which is the dot
  )
FROM
  Review
/*
                      1         2
             12345678901234567890123456
    Example: alice.smith@fabrikam.co.uk
                        ^        ^
                        |        |
                        |        +-- match first dot after @ = 21
                        +-- match starts at @
*/

		

ReviewId	Email	At	DotAfterAt
3	mary.jo.anne@acme.co.uk	13	18
4	max@fabrikam.com	4	13
6	terri.duffy@acme.com	12	17
7
8	charlie@contoso.co.in	8	16
9	john@fabrikam.com	5	14
10	alice.smith@fabrikam.co.uk	12	21
11	bob@fabrikam.com	4	13
12	stuart.green@acme.com	13	18

REGEXP_REPLACE

REGEXP_REPLACE replaces text matched by a regex pattern.

Strip middle names

			
SELECT
  ReviewId,
  Name,
  ShortName = REGEXP_REPLACE(
    Name,                   -- scan the Name column
    '^(\S+)\s+.*\s+(\S+)$', -- ^(\S+) = capture the first word; .* = match everything in the middle; (\S+)$ = capture the last word
    '\1 \2',                -- replace the whole name with first + last
    1,                      -- start position
    1,                      -- replace only the first occurrence
    'i'                     -- case insensitive flag (optional, but safe to include)
  )
FROM
  Review

		

ReviewId	Name	ShortName
3	Mary Jo Anne Erickson	Mary Erickson
4	Max Wong	Max Wong
6	Terri S Duffy	Terri Duffy
7	Eve Jones	Eve Jones
8	Charlie Brown	Charlie Brown
9	John Doe	John Doe
10	Alice Smith	Alice Smith
11	Bob Johnson	Bob Johnson
12	Stuart Green	Stuart Green

REGEXP_MATCHES

REGEXP_MATCHES returns regex matches as rows, including match position and captured substrings.

Extract words starting with A followed by any two characters

			
SELECT *
FROM REGEXP_MATCHES(
  'ATE ABOVE ACT',
  '\b(A)(..)\b'   -- \b = word boundary; (A) = the letter A; (..) = any two characters after A
)

		

match_id	start_position	end_position	match_value	substring_matches
1	1	3	ATE	[{“value”:”A”,”start”:1,”length”:1},{“value”:”TE”,”start”:2,”length”:2}]
2	11	13	ACT	[{“value”:”A”,”start”:11,”length”:1},{“value”:”CT”,”start”:12,”length”:2}]

Extract hashtags from text

			
SELECT *
FROM REGEXP_MATCHES(
  'Learning #AzureSQL #AzureSQLDB',
  '#([A-Za-z0-9_]+)'  -- # = hash symbol; ([A-Za-z0-9_]+) = followed by 1 or more characters or underscores
)

		

match_id	start_position	end_position	match_value	substring_matches
1	10	18	#AzureSQL	[{“value”:”AzureSQL”,”start”:11,”length”:8}]
2	20	30	#AzureSQLDB	[{“value”:”AzureSQLDB”,”start”:21,”length”:10}]

Extract hashtags from the review text in the database

			
SELECT
  r.ReviewId,
  r.ReviewText,
  m.*
FROM
  Review AS r
  CROSS APPLY REGEXP_MATCHES(r.ReviewText, '#([A-Za-z0-9_]+)') AS m

		

ReviewId	match_value
3	#poor
4	#goodservice
4	#recommended
6	#aweful
7	#fantastic
7	#amazing
9	#excellent
9	#quality

REGEXP_SPLIT_TO_TABLE

REGEXP_SPLIT_TO_TABLE splits a string into rows using a regex delimiter.

Extract individual words from text using whitespace as the delimiter

			
SELECT *
FROM
  REGEXP_SPLIT_TO_TABLE(
    'the quick brown    fox jumped' || char(9) || 'over the lazy' || char(13) || char(10) || 'dog',
    '\s+'   -- \s+ = match one or more whitespace characters (spaces, tabs, newlines)
  )

		

ordinal	value
1	the
2	quick
3	brown
4	fox
5	jumped
6	over
7	the
8	lazy
9	dog

Extract individual words from review text

			
SELECT
  r.ReviewId,
  r.ReviewText,
  WordText        = s.value,
  WordPosition    = s.ordinal
FROM
  Review AS r
  CROSS APPLY REGEXP_SPLIT_TO_TABLE(r.ReviewText, '\s+') AS s

		

This returns one row per token from each review. Each row includes the original review text, the token value, and the token position within that review.

Clean punctuation from the split words

			
SELECT
  r.ReviewId,
  r.ReviewText,
  WordText        = REGEXP_REPLACE(s.value, '[^\w]', '', 1, 0, 'i'),
  WordPosition    = s.ordinal
FROM
  Review AS r
  CROSS APPLY REGEXP_SPLIT_TO_TABLE(r.ReviewText, '\s+') AS s

		

This version is similar, but removes punctuation from each token.

Final thoughts

Native regex support dramatically improves the T-SQL developer experience. Instead of relying on fragile combinations of LIKE, PATINDEX, CHARINDEX, and procedural cleanup code, you can now express many validation, extraction, replacement, and tokenization tasks directly and clearly in SQL.

For text-heavy workloads, data quality checks, ETL processing, review analysis, and general string manipulation, these functions are a very welcome addition to SQL Server 2025 and Azure SQL Database.

Posted in Azure SQL Database, SQL Server, SQL Server 2025. Tags: regex, regular-expressions, SQL Server, SQL Server 2025, technology. Leave a Comment »

Fuzzy String Matching in SQL Server 2025

October 8, 2025 — Leonard Lobel

Fuzzy string matching is the process of finding strings that are approximately equal, rather than exact matches. This is a critical capability for data cleansing, deduplication, primitive natural language search, and matching user input against known values.

Until now, SQL Server’s fuzzy options have been limited to phonetic comparisons. But now, SQL Server 2025 (and Azure SQL Database) introduces a set of modern string similarity functions you can run directly in T-SQL with far more precision.

Note: This post is based on SQL Server 2025 CTP 2.1. Syntax and behavior are subject to subtle changes by the time the product is released. Fuzzy string matching will ultimately be supported across all SKUs of SQL Server, including SQL Server 2025 for Windows, SQL Server 2025 for Linux, Azure SQL Database, and Managed Instance.

Legacy Options: SOUNDEX and DIFFERENCE

These two functions have been around for decades:

SOUNDEX: Produces a code representing the phonetic sound of a word (e.g., “Green” → G650, “Greener” → G656).
DIFFERENCE: Compares SOUNDEX codes on a 1–4 scale (4 ≈ exact).

While these work marginally well for quick phonetic checks, they are not suited for long strings or nuanced comparisons.

New in SQL Server 2025: Four Modern Fuzzy Matching Functions

SQL Server 2025 now has these well-known fuzzy matching algorithms built into the engine:

Function Name	Returns	Use Cases
`EDIT_DISTANCE`	Number of edits (insert/delete/substitute) between two strings (aka Levenshtein distance)	You want a raw “how many changes?” counter or to sort by degree of change
`EDIT_DISTANCE_SIMILARITY`	Normalized similarity percentage (0–100) (aka Levenshtein similarity)	You want a rankable score or thresholds like `>= 80`
`JARO_WINKLER_DISTANCE`	A distance score optimized for short strings (lower is better)	You prefer inverse scoring (distance)
`JARO_WINKLER_SIMILARITY`	A similarity score emphasizing common prefixes (higher is better)	Names and short, typo-prone values

Exploring with Word Pairs

Let’s create a table of word pairs and score them with both the new and legacy functions.

CREATE TABLE WordPair (
  WordPairId    int IDENTITY PRIMARY KEY,
  Word1         varchar(50),
  Word2         varchar(50)
)

Now populate the table with some sample data:

INSERT INTO WordPair VALUES
 ('Colour',     'Color'),
 ('Flavour',    'Flavor'),
 ('Centre',     'Center'),
 ('Theatre',    'Theater'),
 ('Theatre',    'Theatrics'),
 ('Theatre',    'Theatrical'),
 ('Organise',   'Organize'),
 ('Analyse',    'Analyze'),
 ('Catalogue',  'Catalog'),
 ('Programme',  'Program'),
 ('Metre',      'Meter'),
 ('Honour',     'Honor'),
 ('Neighbour',  'Neighbor'),
 ('Travelling', 'Traveling'),
 ('Grey',       'Gray'),
 ('Green',      'Greene'),
 ('Green',      'Greener'),
 ('Green',      'Greenery'),
 ('Green',      'Greenest'),
 ('Orange',     'Purple'),      -- very different
 ('Defence',    'Defense'),
 ('Practise',   'Practice'),
 ('Practice',   'Practice'),    -- identical
 ('Aluminium',  'Aluminum'),
 ('Cheque',     'Check')

This sample include a mix of near-matches (British vs. American spellings), slight variations (“Green” and “Greener”), one intentionally exact pair (“Practice” and “Practice”), and one intentionally bad pair (“Orange” vs. “Purple”).

Now let’s score each pair using the new and legacy functions:

SELECT
  *,
  -- New SQL Server 2025 fuzzy matching functions
  LevenshteinDistance     = EDIT_DISTANCE(Word1, Word2),
  LevenshteinSimilarity   = EDIT_DISTANCE_SIMILARITY(Word1, Word2),
  JaroWrinklerDistance    = JARO_WINKLER_DISTANCE(Word1, Word2),
  JaroWrinklerSimilarity  = JARO_WINKLER_SIMILARITY(Word1, Word2),
  -- Legacy SQL Server fuzzy matching functions
  Soundex1                = SOUNDEX(Word1),
  Soundex2                = SOUNDEX(Word2),
  Difference              = DIFFERENCE(Word1, Word2)
FROM
  WordPair
ORDER BY 
  LevenshteinSimilarity DESC

This query exercises the four new functions alongside the two legacy functions for every row. The results are sorted by EDIT_DISTANCE_SIMILARITY descending (0–100; 100 = exact), which yields a quick and easy comparison.

Run the query, and observe:

The exact match on “Practice/Practice” floats to the very top (similarity 100, distance 0).
Close pairs like “Grey/Gray” and “Colour/Color” cluster near the top with high similarity and small edit distances.
The outlier (“Orange/Purple”) drops to the bottom with low similarity and large distance.
You’ll see that legacy SOUNDEX/DIFFERENCE sometimes overrate or underrate certain pairs, while the newer metrics capture nuance (insertions, substitutions, transpositions, prefix weight).

Clean up the example table

DROP TABLE WordPair

Real-World Deduping (Customer Records)

Now let’s detect potential duplicates in a customer table by scoring first names, last names, and addresses, then combining them.

CREATE TABLE Customer (
  CustomerId int IDENTITY PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Address varchar(100)
)

While minimal, this structure will suffice to keep focus on fuzzy matching. Now populate the table with sample data, including a mix of exact duplicate and near duplicate values across the first name, last name, and address fields.

INSERT INTO Customer VALUES
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Jonathan',   'Smith',    '123 N Main St.'),
 ('Johnathan',  'Smith',    '456 Ocean View Blvd'),
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Daniel',     'Smith',    '123 N. Main St.'),
 ('Danny',      'Smith',    '123 N. Main St.'),
 ('John',       'Smith',    '123 Main Street'),
 ('Jonathon',   'Smyth',    '123 N Main St'),
 ('Jon',        'Smith',    '123 N Main St.'),
 ('Johnny',     'Smith',    '124 N Main St'),
 ('Ethan',      'Goldberg', '742 Evergreen Terrace'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Blvd'),
 ('Carl',       'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard')

SELECT * FROM Customer

Score First Name Similarity

We’ll compute pairwise first-name similarity across all row combinations using JARO_WINKLER_SIMILARITY (great for short strings and prefix sensitivity).

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  FirstNameQuality = CASE
    WHEN FirstNameSimilarity = 1    THEN 'Exact'
    WHEN FirstNameSimilarity >= .85 THEN 'Very Strong'
    WHEN FirstNameSimilarity >= .75 THEN 'Strong'
    WHEN FirstNameSimilarity >= .4  THEN 'Weak'
                                    ELSE 'Very Weak'
    END
FROM
  PairwiseSimilarityCte
ORDER BY
  FirstNameSimilarity DESC

How it works:

The self-join c2.CustomerId < c1.CustomerId generates unique unordered pairs (no duplicates, no self-pairs).
The CTE computes similarity for each pair; the outer query labels it with human-friendly buckets.

What to expect:

Exact repeats (e.g., identical first names) show FirstNameSimilarity = 1 and label Exact.
Variants like Johnathan/ Jonathan and Jon/ John cluster as Very Strong or Strong.
Unrelated names (e.g., Daniel / Johnathan) drop into Weak/Very Weak.

Score Last Name Similarity

Same pattern, now focused on last names. This will show phonetic look-alikes like Smith/Smyth scoring high.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  LastNameQuality = CASE
    WHEN LastNameSimilarity = 1     THEN 'Exact'
    WHEN LastNameSimilarity >= .85  THEN 'Very Strong'
    WHEN LastNameSimilarity >= .75  THEN 'Strong'
    WHEN LastNameSimilarity >= .4   THEN 'Weak'
                                    ELSE 'Very Weak'
    END
FROM
    PairwiseSimilarityCte
ORDER BY
    LastNameSimilarity DESC

What to expect:

All Smith/Smith pairs rank Exact.
Smith/Smyth should score Very Strong (tiny spelling change).
Completely different surnames (e.g., Goldberg / Smith) fall low.

Score Address Similarity

Addresses are noisy (abbreviations, punctuation, ordering). JARO_WINKLER_SIMILARITY handles short transpositions and prefix overlaps well.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  AddressQuality = CASE
    WHEN AddressSimilarity = 1      THEN 'Exact'
    WHEN AddressSimilarity >= .85   THEN 'Very Strong'
    WHEN AddressSimilarity >= .75   THEN 'Strong'
    WHEN AddressSimilarity >= .4    THEN 'Weak'
                                    ELSE 'Very Weak'
  END
FROM
  PairwiseSimilarityCte
ORDER BY
  AddressSimilarity DESC

What to expect:

Exact duplicates top the list.
Variants like “123 North Main Street” vs “123 N Main St.” score Very Strong/Strong.
Unrelated addresses (e.g., “742 Evergreen Terrace” vs “123 N. Main St.”) land low.

Combine All Three: A Composite Match Score

A high first-name score plus a low address score probably isn’t a true duplicate. Let’s average the three similarities into a FinalCombinedScore for better overall ranking.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName),
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName),
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
),
FinalCombinedScoreCte AS (
  SELECT
    *,
    FinalCombinedScore = (FirstNameSimilarity + LastNameSimilarity + AddressSimilarity) / 3.0
  FROM
    PairwiseSimilarityCte
)
SELECT
  *,
  FinalQuality = CASE
    WHEN FinalCombinedScore = 1     THEN 'Exact'
    WHEN FinalCombinedScore >= .85  THEN 'Very Strong'
    WHEN FinalCombinedScore >= .75  THEN 'Strong'
    WHEN FinalCombinedScore >= .4   THEN 'Weak'
                                    ELSE 'Very Weak'
  END
FROM
  FinalCombinedScoreCte
ORDER BY
  FinalCombinedScore DESC

How it works:

The first CTE computes three similarities for each pair.
The second CTE averages them into FinalCombinedScore.
The outer query labels and sorts by that combined metric.

What to expect:

True duplicates (identical first, last, address) appear right at the top (Exact).
Pairs like “Jonathon Smyth, 123 N Main St” and “Johnathan Smith, 123 North Main Street” rank high because names and addresses are all very similar.
Pairs that share a name but differ in address (e.g., Johnathan Smith at 456 Ocean View Blvd vs 123 North Main) rank lower—exactly what you want when deduping.

Final Thoughts

The new fuzzy string functions in SQL Server 2025 finally give T-SQL first-class similarity scoring. Whether you’re cleaning legacy data, merging customer lists, or building typo-tolerant features, you can now do it natively, efficiently, and with the granularity you always wanted.

Posted in SQL Server, SQL Server 2025. Tags: sql, SQL Server. Leave a Comment »

Getting Started with Change Event Streaming in SQL Server 2025 (Part 2: Consuming Events)

August 30, 2025 — Leonard Lobel

Welcome back! In this second part of my two-part series covering the new Change Event Streaming (CES) feature in SQL Server 2025, I’ll show you how to consume events generated by CES. In Part 1, we provisioned an Azure event hub, generated a SAS token to access the event hub, created the CesDemo sample database, and enabled Change Event Streaming (CES) on the database. We then added tables to an event stream group with deliberate choices for @include_old_values and @include_all_columns. So at this point, CES is now emitting DML changes (inserts, updates, and deletes) from those tables into the event hub.

Note: This post is based on SQL Server 2025 CTP 2.1. Syntax and behavior are subject to subtle changes by the time the product is released. Change Event Streaming (CES) will ultimately be supported across all SKUs of SQL Server, including SQL Server 2025 for Windows, SQL Server 2025 for Linux, Azure SQL Database, and Managed Instance.

Now we’re ready to build a client application to consume generated events. But before we start coding, let’s establish some context so the steps make sense.

First, CES merely writes into Event Hubs. It doesn’t know (or care) who’s listening. It’s up to your client application(s) to subsequently consume those events. Our sample C# application will use the Event Hubs client SDK (specifically, EventProcessorClient) to listen for events.

Every CES client needs somewhere to record progress, as it processes events. This is called a checkpoint, which works like a “bookmark”. Using checkpoints, client applications can stop and later resume where they left off, and not reprocess events that have already been processed. The SDK uses Azure Blob Storage for this purpose.

You’ll also encounter the term consumer group. Think of a consumer group as a “view” of the stream with its own checkpoint. By utilizing multiple consumer groups (one per client application), each application can maintain its own checkpoint for bookmarking its place in the event stream. The Basic tier allows for only one consumer group. Moving to (and paying for) a higher tier than Basic will allow you to manage multiple client applications that consume events simultaneously from the same event hub, each at their own pace, without stepping on each other.

Create a Blob Storage Container

You’ll need a blob container in Azure Storage so that the Event Hubs client SDK can manage checkpoints for your consumer groups.

Create a Storage Account

A blob container lives within a storage account. To create a new storage account:

In the Azure portal, create a new resource.
From the Marketplace, create a new Storage Account resource.
Provide a name for a new storage account in either a new or existing resource group (dashes not permitted).
For the Primary service, choose Azure Blob Storage or Azure Data Lake Storage Gen 2.
For Redundancy, choose Locally-redundant storage (LRS) (sufficient for development and testing).
Click Review + create, and then Create.

Create a Blob Container

Now you can create a new blob container within the new storage account:

Under Data Storage on the left, click Containers.
Click + Add container.
Provide a name for the new container.
Click Create.

Now get the connection string for the storage account:

Under Security + Networking on the left, click Access Keys.
Click Show under the Connection String for key1.
Click the Copy icon to copy the connection string to the clipboard.
Paste the connection string into Notepad; it will be needed for the client application configuration.

Create the Visual Studio Project

Alright, we’re ready to roll. We’ll build our consumer client as a simple console app, keeping the the focus on wiring up the stream, deserializing events, and showing what’s happening.

Note: CES consumers can also be built with Azure Functions (I’ll cover that in a later post). Azure Functions hide much of the boilerplate with an Event Hubs trigger, run serverlessly, and scale out automatically. In contrast, building a client “manually” as we’re doing here, gives you maximum control over connection behavior, batching, retry policies, and diagnostics.

Let’s get started!

Launch Visual Studio 2022. Then select Create a new project and choose Console App (C#). Name the project CESClient, click Next, and then click Create.

Install NuGet Packages

First, we’ll need three NuGet packages to support our application. Right-click the CESClient project and choose Manage NuGet Packages. Click the Browse tab, and then locate and install the following packages:

Azure.Messaging.EventHubs.Processor
- Includes the Event Hubs client and processor, as well as Azure Blob Storage for checkpoint support.
Microsoft.Extensions.Configuration.Json
- Supports external configuration in appsettings.json rather than using hard-coded configuration.
Newtonsoft.Json
- Allows us to deserialize the CloudEvent payload received from the event hub, which is supplied as JSON.

Add a Configuration File

Now create the appsettings.json file where we’ll keep our configuration. This includes connection details and secrets (SAS token, Blob connection string).

Right-click the project and choose Add > New Item
Name the file appsettings.json.
Replace its content with:

{
  "EventHub": {
    "HostName": "ces-namespace.servicebus.windows.net",
    "Name": "ces-hub",
    "SasToken": "paste-your-sas-token-here"
  },
  "BlobStorage": {
    "ConnectionString": "paste-your-blob-connection-string-here",
    "ContainerName": "ces-checkpoint"
  }
}

For the EventHub property, note the HostName property specifies our event hub namespace name ces-namespace as the host name prefix, the Name property specifies our event hub name ces-hub, and the SasToken property holds the SAS token generated for accessing the event hub. All three of these values were established during setup and configuration in Part 1.
For the BlobStorage property, paste in values for the ConnectionString and ContainerName for the Azure Storage blob container that you just created.
To ensure this file gets copied to the output directory when we build the project, click appsettings.json in the Solution Explorer panel. Then, in the Properties panel set Copy to Output Directory to Copy if newer.

Add the Code

Now supply the following code in Program.cs:

using Azure;
using Azure.Messaging.EventHubs;
using Azure.Messaging.EventHubs.Processor;
using Azure.Messaging.EventHubs.Consumer;
using Azure.Storage.Blobs;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text.Json;
using System.Threading.Tasks;

namespace CESClient
{
  public class Program
  {
    private static int _eventCount;

    // Add methods here

  }
}

This imports all the namespaces we’ll be referencing and defines a private field as a simple event counter that we’ll increment with each received event.

Next plug in the Main method:

public static async Task Main(string[] args)
{
  // Say hello
  Console.WriteLine("SQL Server 2025 Change Event Streaming Client");
  Console.WriteLine();
  Console.Write("Initializing... ");

  // Load configuration from appsettings.json
  var config = new ConfigurationBuilder()
      .SetBasePath(Directory.GetCurrentDirectory())
      .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
      .Build();

  // Create a blob container client that the event processor will use for checkpointing
  var blobStorageConnectionString = config["BlobStorage:ConnectionString"];
  var blobStorageContainerName = config["BlobStorage:ContainerName"];

  var storageClient = new BlobContainerClient(blobStorageConnectionString, blobStorageContainerName);

  // Create an event processor client to process events in the event hub
  var eventHubHostName = config["EventHub:HostName"];
  var eventHubName = config["EventHub:Name"];
  var sasToken = config["EventHub:SasToken"];

  var processor = new EventProcessorClient(
      storageClient,                                        // checkpoint store
      EventHubConsumerClient.DefaultConsumerGroupName,      // Basic tier: one consumer group (e.g., $Default)
      eventHubHostName,
      eventHubName,
      new AzureSasCredential(sasToken)
  );

  // Register handlers for processing events and errors
  processor.ProcessEventAsync += ProcessEventHandler;
  processor.ProcessErrorAsync += ProcessErrorHandler;

  // Start listening for events
  Console.Write("starting... ");
  _eventCount = 0;

  await processor.StartProcessingAsync();

  Console.WriteLine("waiting... press any key to stop.");
  Console.ReadKey(intercept: true);

  // Stop listening for events
  await processor.StopProcessingAsync();

  Console.WriteLine("Stopped");
}

This code loads the configuration from appsettings.json, creates the blob container client (for saving checkpoints), spins up the Event Hubs processor with the default consumer group, and attaches handlers for processing events and errors. Finally, it starts and stops cleanly when the user presses any key.

Process Events

Now add the ProcessEventHandler method. This method first parses the outer CloudEvent envelope, then the inner payload, prints helpful metadata, and routes to the insert/update/delete handlers. Finally (and critically), it updates the checkpoint so restarts will resume from the next event. (I explain the CloudEvent payload structure in Part 1.)

private static async Task ProcessEventHandler(ProcessEventArgs eventArgs)
{
  try
  {
    // Deserialize the event data
    using var doc = JsonDocument.Parse(eventArgs.Data.Body.ToArray());
    var root = doc.RootElement;
    var dataJson = root.GetProperty("data");

    using var innerDoc = JsonDocument.Parse(dataJson.GetString());
    var data = innerDoc.RootElement;

    Console.WriteLine($"Processing event... #{++_eventCount}");

    // Deserialize the "current" and "old" fields in the eventrow property of the event data to dictionaries
    var operation = root.GetProperty("operation").GetString();
    var cols = data.GetProperty("eventsource").GetProperty("cols").EnumerateArray();
    var current = JsonSerializer.Deserialize<Dictionary<string, string>>(data.GetProperty("eventrow").GetProperty("current").GetString());
    var old = JsonSerializer.Deserialize<Dictionary<string, string>>(data.GetProperty("eventrow").GetProperty("old").GetString());

    DisplayEventMetadata(eventArgs, root, data);

    switch (operation)
    {
      case "INS":
        ProcessInsert(cols, current);
        break;
      case "UPD":
        ProcessUpdate(cols, current, old);
        break;
      case "DEL":
        ProcessDelete(cols, old);
        break;
    }

    Console.WriteLine();
    Console.WriteLine(new string('-', 80));
    Console.WriteLine();

    // Persist progress so we don't reprocess this event on restart
    await eventArgs.UpdateCheckpointAsync();
  }
  catch (Exception ex)
  {
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(ex.Message);
    Console.ResetColor();
  }
}

Display Event Metadata

This method renders a quick “context dump” for each event: it first prints the sequence number and offset from ProcessEventArgs so you can pinpoint the event’s exact position within the event hub (useful for ordering and replay). It then surfaces key CloudEvent fields from the outer envelope; spec/version, the event type, the DML operation (INS, UPD, DEL), timestamp, unique ID, logical ID, and the data content type. Finally, it drills into the inner CES payload to show the database, schema, and table that produced the event. Together, these details make it easy to correlate what you’re seeing in the console with the emitting source and to troubleshoot issues like unexpected operations or schema mismatches.

private static void DisplayEventMetadata(ProcessEventArgs eventArgs, JsonElement root, JsonElement data)
{
  Console.WriteLine("Event Args");
  Console.WriteLine($"  Sequence:Offset => {eventArgs.Data.SequenceNumber}:{eventArgs.Data.Offset}");
  Console.WriteLine();
  Console.WriteLine("Event Data");
  Console.WriteLine($"  Spec version:       {root.GetProperty("specversion").GetString()}");
  Console.WriteLine($"  Operation:          {root.GetProperty("type").GetString()}");
  Console.WriteLine($"  Time:               {root.GetProperty("time").GetString()}");
  Console.WriteLine($"  Event ID:           {root.GetProperty("id").GetString()}");
  Console.WriteLine($"  Logical ID:         {root.GetProperty("logicalid").GetString()}");
  Console.WriteLine($"  Operation:          {root.GetProperty("operation").GetString()}");
  Console.WriteLine($"  Data content type:  {root.GetProperty("datacontenttype").GetString()}");
  Console.WriteLine();
  Console.WriteLine("Data");
  Console.WriteLine($"  Database:           {data.GetProperty("eventsource").GetProperty("db").GetString()}");
  Console.WriteLine($"  Schema:             {data.GetProperty("eventsource").GetProperty("schema").GetString()}");
  Console.WriteLine($"  Table:              {data.GetProperty("eventsource").GetProperty("tbl").GetString()}");
  Console.WriteLine();
}

Handle Inserts

For inserts, a full “after” image is easiest to read and is a quick way to validate the @include_all_columns setting we established in Part 1.

private static void ProcessInsert(JsonElement.ArrayEnumerator cols, Dictionary<string, string> current)
{
  Console.WriteLine("Operation: Insert");
  Console.ForegroundColor = ConsoleColor.Green;

  foreach (var col in cols)
  {
    var name = col.GetProperty("name").GetString();
    Console.WriteLine($"\t{name}: {current[name]}");
  }

  Console.ResetColor();
}

Handle Updates

For tables where we’ve enabled @include_old_values in Part 1, you’ll get a great side-by-side view; otherwise you’ll just see the “after” image.

private static void ProcessUpdate(JsonElement.ArrayEnumerator cols, Dictionary<string, string> current, Dictionary<string, string> old)
{
  Console.WriteLine("Operation: Update");

  foreach (var col in cols)
  {
    var name = col.GetProperty("name").GetString();

    if (old.Count > 0 && current[name] != old[name])
    {
      Console.ForegroundColor = ConsoleColor.Yellow;
      Console.WriteLine($"\t{name}: {current[name]} (old: {old[name]})");
      Console.ResetColor();
    }
    else
    {
      Console.WriteLine($"\t{name}: {current[name]}");
    }
  }
}

Handle Deletes

Deletes only have the “before” image, which are useful for auditing and reconciliation purposes.

private static void ProcessDelete(JsonElement.ArrayEnumerator cols, Dictionary<string, string> old)
{
  Console.WriteLine("Operation: Delete");
  Console.ForegroundColor = ConsoleColor.Red;

  foreach (var col in cols)
  {
    var name = col.GetProperty("name").GetString();
    Console.WriteLine($"\t{name}: {old[name]}");
  }

  Console.ResetColor();
}

Process Errors

Finally, we need to tolerate errors without crashing the application. Should an error occur, this method displays the exception details. Of course, a real-world scenario would require proper error handling; for example, saving the event details to a queue for automatic retry or manual intervention.

private static Task ProcessErrorHandler(ProcessErrorEventArgs e)
{
  Console.ForegroundColor = ConsoleColor.Red;
  Console.WriteLine(e.Exception.Message);
  Console.ResetColor();
  return Task.CompletedTask;
}

Run the Application

The moment of truth is here! Go ahead and run the application. The client console window should open, and you should see:

SQL Server 2025 Change Event Streaming Client
Initializing... starting... waiting... press any key to stop.

If errors appear, double-check configuration values and NuGet package installs.

Generate and Monitor Change Events

Let’s exercise inserts, updates, deletes, as well as trigger-driven changes, based on the database schema we setup in Part 1. This will allow us to observe the events being captured in real-time, and examine the CloudEvent payloads as we receive them.

Start SSMS and open a query window to the CesDemo database. Then tile the SSMS and client console windows side-by-side. This way, you can examine the events in the client console window as you generate them from the SSMS window.

Create an Order

In SSMS, run the stored procedure to create a new order:

EXEC CreateOrder @CustomerId = 1

In the client console window, you should observe an INS event for the Order table.

Create Order Details

Now add two details to the order:

EXEC CreateOrderDetail @OrderId = 1, @ProductId = 1, @Quantity = 2
EXEC CreateOrderDetail @OrderId = 1, @ProductId = 2, @Quantity = 1

Expect two corresponding INS events for OrderDetail. And because the OrderDetail trigger adjusts Product.ItemsInStock, also expect UPD events for Product reflecting stock decrements (2 for product 1; 1 for product 2).

Delete an Order

Now call the stored procedure that deletes an entire order, along with the order details.

EXEC DeleteOrder @OrderId = 1

Expect DEL events for the order and its details, and UPD events for Product as stock is restored.

Update a Customer

Now change a customer’s city to Chicago:

UPDATE Customer SET City = 'Chicago' WHERE CustomerId = 1

Expect a UPD event for Customer. Recall that in Part 1, we chose not to include old values for this table, so you’ll see only the “after” values.

Bulk Update Products

Let’s apply a 20% discount on the price of all cameras:

UPDATE Product SET UnitPrice = UnitPrice * 0.8 WHERE Category = 'Camera'

Expect UPD events for each matching row. In Part 1, we included old values and only changed columns for Product, so you’ll see the old and new UnitPrice (and the primary key, which is always included).

Update a Table Without a Primary Key

This last example demonstrates the need for always having a primary key defined on a table:

UPDATE TableWithNoPK SET ItemName = 'Stove' WHERE Id = 3

Expect a UPD event without key columns. So you’ll see that an item name was changed to Stove in some row, but you won’t know which row, rendering this event data as useless information.

Conclusion

That’s a wrap! In this two-part blog post series, you have successfully built out an end-to-end CES pipeline. First you configured SQL Server 2025 to stream changes into Azure Event Hubs with the appropriate SAS credential, event stream group, and table settings. Then you built a C# consumer that reads the CloudEvent-wrapped payloads and uses Azure Blob Storage for checkpoints. Our demo ran with the default consumer group (Basic tier) for a single application, but higher tiers support multiple groups for multiple independent clients. You now have a clean, real-time path from database changes to actionable events!

Posted in Azure SQL Database, SQL Server 2025. Tags: azure-event-hubs, change-event-streaming, sql, SQL Server. Leave a Comment »

Getting Started with Change Event Streaming in SQL Server 2025 (Part 1: Setup and Configuration)

August 18, 2025 — Leonard Lobel

Change Event Streaming (CES) is one of the most exciting new features coming in SQL Server 2025. It allows you to continuously stream row-level changes from your tables directly into Azure Event Hubs, where multiple consumer applications can subscribe to the event data in real time.

Note: This post is based on SQL Server 2025 CTP 2.1. Syntax and behavior are subject to subtle changes by the time the product is released. Change Event Streaming (CES) will ultimately be supported across all SKUs of SQL Server, including SQL Server 2025 for Windows, SQL Server 2025 for Linux, Azure SQL Database, and Managed Instance.

In this two-part series, I’ll show you how to set up and configure CES (Part 1), and then how to build a consumer application to process the streamed changes (Part 2).

Let’s dive in!

Step 1: Create an Event Hub

Before SQL Server can stream changes, you need a target destination. CES is designed to stream directly into Azure Event Hubs.

Create an Event Hub Namespace

An event hub lives within an event namespace. To create a new event hub namespace:

In the Azure portal, create a new resource.
From the Marketplace, create a new Event Hubs resource.
Provide a name for a new Event Hubs namespace in either a new or existing resource group.
Choose the Basic pricing tier with 1 throughput unit (sufficient for development and testing).
Click Review + create, and then Create.

Create an Event Hub

Now you can create a new event hub within the new event hub namespace:

On the namespace Overview page, click + Event Hub.
Provide a name for the new event hub, and leave all other options at their default settings.
Click Review + create, and then Create.

Create an Event Hub Policy

Now create a policy that allows managing the event hub:

Under Settings on the left, click Shared Access Policies.
Click + Add to create a new policy.
Provide a name for the policy.
Check Manage (which automatically includes Send and Listen).
Click Create.

Generate a SAS Token

Finally, you’ll need a Shared Access Signature (SAS) token for SQL Server and other clients to authenticate against the Event Hub. Unfortunately, the Azure portal does not provide a GUI for generating SAS tokens for Event Hub, so you must generate one programmatically using PowerShell, Azure CLI, or the Azure SDK. In this walkthrough, we’ll use PowerShell.

Install PowerShell Modules

Run PowerShell as an administrator and install the necessary modules.

Note: You only need to install these modules once on a machine. If you’ve already installed them previously, you can skip this step.

# Install the general Azure cmdlets (this can take up to 20 minutes)
Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force

# Install the Event Hub module (runs quickly)
Install-Module -Name Az.EventHub -Scope CurrentUser -Force

Create the SAS Token Script

Copy the following code into a new file named Generate-SasToken.ps1. This script was adapted from Microsoft’s documentation at https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-event-streaming/configure.

function Generate-SasToken {

    # Provide values for the following resources:
    $resourceGroupName  = "ces-demo-rg"
    $namespaceName      = "ces-namespace"
    $eventHubName       = "ces-hub"
    $policyName         = "ces-policy"

    # Login to Azure and select the Azure Subscription
    Connect-AzAccount -InformationAction SilentlyContinue | Out-Null

    # Validate the existence of the specified resource group, event hub namespace, and event hub
    Get-AzResourceGroup -Name $resourceGroupName -ErrorAction Stop | Out-Null
    Get-AzEventHubNamespace -ResourceGroupName $resourceGroupName -Name $namespaceName -ErrorAction Stop | Out-Null
    Get-AzEventHub -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -Name $eventHubName -ErrorAction Stop | Out-Null

    # Get the event hub authorization policy (it must have Manage rights)
    $policy = Get-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -ErrorAction SilentlyContinue

    if (-not ("Manage" -in $policy.Rights)) {
        throw "Authorization rule '$policyName' does not exist, or is missing the required 'Manage' right"
    }

    # Get the Primary Key of the Shared Access Policy
    $keys = Get-AzEventHubKey -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName

    if (-not $keys) {
        throw "Could not obtain Azure Event Hub Key"
    }

    if (-not $keys.PrimaryKey) {
        throw "Could not obtain Primary Key"
    }

    $primaryKey = ($keys.PrimaryKey) 

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

        $sinceEpoch = [datetime]::UtcNow - [datetime]"1970-01-01"
        $expiry = [int]$sinceEpoch.TotalSeconds + (60 * 60 * 24 * 31 * 6)  # 6 months
        $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-Host "`n-- Generated SAS Token --" -ForegroundColor Gray
    Write-Host $sasToken -ForegroundColor White
    Write-Host "-- End of generated SAS Token --`n" -ForegroundColor Gray

    # Copy the SAS token to the clipboard
    $sasToken | Set-Clipboard
    Write-Host "The generated SAS token has been copied to the clipboard." -ForegroundColor Green
}

Generate-SasToken

At the top of the script (lines 4 through 7), fill in the values for your resource group, namespace, event hub, and policy. Also, the script generates a token that expires after 6 months. To adjust the expiration, edit the $expiry assignment on line 42.

Run the Script

Before you can execute the script, you must allow PowerShell to run local scripts:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

When prompted for confirmation, type A (for “Yes to All”) and press Enter.

Now run the script:

.\Generate-SasToken.ps1

You’ll be prompted to log in to your Microsoft account and select your Azure subscription. The script will then generate the SAS token and display it between the lines:

-- Generated SAS Token --
<your SAS token>
-- End of generated SAS Token --

The script also copies the generated SAS token to the clipboard so that you can paste it into Notepad. You’ll need it later when configuring SQL Server to stream events to Event Hubs, and then again (Part 2) when configuring your consumer applications that subsequently read those events from Event Hubs.

Step 2: Create the Demo Database

We’ll use a small sample database to demonstrate CES. Run this script in SSMS to create the database:

-- Create the demo database
USE master
GO

CREATE DATABASE CesDemo
GO

USE CesDemo
GO

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

SET IDENTITY_INSERT Customer ON
INSERT INTO Customer
  (CustomerId,  CustomerName,               City) VALUES
  (1,           'Shutter Bros Wholesale',   'New York'),
  (2,           'Aperture Supply Co.',      'Los Angeles')
SET IDENTITY_INSERT Customer OFF

CREATE TABLE Product (
  ProductId     int IDENTITY PRIMARY KEY,
  Name          varchar(80),
  Color         varchar(15),
  Category      varchar(20),
  UnitPrice     decimal(8, 2),
  ItemsInStock  smallint
)
GO

SET IDENTITY_INSERT Product ON
INSERT INTO Product
  (ProductId, Name,                                  Color,     Category,       UnitPrice,  ItemsInStock) VALUES 
  (1,         'Canon EOS R5 Mirrorless Camera',      'Black',   'Camera',       3899.99,    10),
  (2,         'Nikon Z6 II Mirrorless Camera',       'Silver',  'Camera',       1996.95,    8),
  (3,         'Sony NP-FZ100 Rechargeable Battery',  'Black',   'Accessory',    78.00,      25)
SET IDENTITY_INSERT Product OFF

CREATE TABLE [Order] (
  OrderId       int IDENTITY PRIMARY KEY,
  CustomerId    int REFERENCES Customer(CustomerId),
  OrderDate     datetime2
)
GO

CREATE TABLE OrderDetail (
  OrderDetailId int IDENTITY PRIMARY KEY,
  OrderId       int REFERENCES [Order](OrderId),
  ProductId     int REFERENCES Product(ProductId),
  Quantity      smallint
)
GO

-- This table lacks Primary Key. Combining that with IncludeAllColumns = 0 results in events that
-- have no primary key, which is essentially useless
CREATE TABLE TableWithNoPK (
  Id        int IDENTITY,
  ItemName  varchar(50)
)
GO

INSERT INTO TableWithNoPK (ItemName) VALUES
  ('Camera'),
  ('Automobile'),
  ('Oven'),
  ('Couch')
GO

-- Create a DML trigger on OrderDetail that updates the ItemsInStock column in the Product table
-- based on the Quantity column in the OrderDetail table
CREATE TRIGGER trgUpdateItemsInStock ON OrderDetail AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  -- Handle insert
  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock - i.Quantity
    FROM
      Product AS p
      INNER JOIN inserted AS i ON p.ProductId = i.ProductId

  -- Handle update
  ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) AND UPDATE(Quantity)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock + d.Quantity - i.Quantity
    FROM
      Product AS p
      INNER JOIN inserted AS i ON p.ProductId = i.ProductId
      INNER JOIN deleted AS d ON p.ProductId = d.ProductId

  -- Handle delete
  ELSE IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock + d.Quantity
    FROM
      Product AS p
      INNER JOIN deleted AS d ON p.ProductId = d.ProductId
END
GO

-- Add some procs to handle orders
CREATE OR ALTER PROC CreateOrder
  @CustomerId int
AS
BEGIN
  INSERT INTO [Order](CustomerId, OrderDate)
  VALUES (@CustomerId, SYSDATETIME())

  SELECT OrderId = SCOPE_IDENTITY()
END
GO

CREATE OR ALTER PROC CreateOrderDetail
  @OrderId int,
  @ProductId int,
  @Quantity smallint
AS
BEGIN
  INSERT INTO OrderDetail (OrderId, ProductId, Quantity)
  VALUES (@OrderId, @ProductId, @Quantity)

  SELECT OrderDetailId = SCOPE_IDENTITY()
END
GO

CREATE OR ALTER PROC DeleteOrder
  @OrderId int
AS
BEGIN
  BEGIN TRANSACTION
    DELETE FROM OrderDetail WHERE OrderId = @OrderId
    DELETE FROM [Order] WHERE OrderId = @OrderId
  COMMIT TRANSACTION
END
GO

This database includes:

Customer, Order, OrderDetail, and Product tables
Stored procedures for inserting/deleting rows in the Order and OrderDetail tables.
A trigger on OrderDetail that updates inventory in Product. This is to demonstrate that CES also streams changes to tables that are updated by triggers, not just changes to tables that you issue direct DML statements on.
A TableWithNoPK to illustrate why tables without primary keys can be problematic when used with CES.

Step 3: Configure CES

With the Event Hub and database ready, let’s enable CES.

Create a Database Master Key

You need to store the SAS token in SQL Server as a database scoped credential, and that requires creating a password-protected database master key first so that SQL Server encrypt that SAS token credential.

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

Create a Database Scoped Credential

To store the SAS token securely in SQL Server, run the following statement (paste the SAS token you copied to Notepad into the SECRET parameter—keep the entire string intact).

CREATE DATABASE SCOPED CREDENTIAL SqlCesCredential
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = '<your SAS token>'

Enable CES for the Database

Execute this T-SQL to enable CES for the current database:

EXEC sys.sp_enable_event_stream

Now verify that CES is enabled:

SELECT * FROM sys.databases WHERE is_event_stream_enabled = 1

Create an Event Stream Group

An event stream group defines the Event Hub target for your events. Be sure to provide the correct values for your event hub namespace and event hub names in the @destination_location parameter:

EXEC sys.sp_create_event_stream_group
  @stream_group_name      = 'SqlCesGroup',
  @destination_location   = 'ces-namespace.servicebus.windows.net/ces-hub',
  @destination_credential = SqlCesCredential,
  @destination_type       = 'AzureEventHubsAmqp'

Add Tables to the Event Stream Group

Decide whether to include old values and whether to include all columns. Each table in our demo uses different settings for different reasons; old values and all values are included when we need that extra context, and they are excluded in favor of reduced bandwidth for smaller event payloads when we don’t.

-- Customer: full row in each event, no old values
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Customer',
  @include_old_values = 0,      -- do not include old values on updates/deletes
  @include_all_columns = 1      -- include all columns even if unchanged

-- Product: only changed columns, include old values (important for inventory diffs)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Product',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- Order: only changed columns, include old values (for auditing changes)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Order',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- OrderDetail: only changed columns, include old values (quantity updates matter)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.OrderDetail',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- TableWithNoPK: demonstrates CES limitations without a primary key
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.TableWithNoPK',
  @include_old_values = 0,      -- no old values
  @include_all_columns = 0      -- changed columns only (essentially useless without a PK)

Customer: All columns included for upsert scenarios. Old values aren’t important here.
Product: Old values are essential to calculate stock and pricing diffs.
Order: Old values matter for audit.
OrderDetail: Old and new quantities are needed for downstream stock adjustments.
TableWithNoPK: Included as a demo; in real-world scenarios, CES requires a primary key to make the events useful.

Verify CES on Each Table

Run the following statements to confirm that CES is enabled on all the tables you just added to the event stream group, and to view the associated CES metadata associated with each table:

EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Customer'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Product'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Order'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'OrderDetail'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'TableWithNoPK'

The CloudEvent Payload

At this point, CES is fully configured! From here on out, all changes to these tables will be streamed to your Event Hub, where they can be consumed by multiple clients in real-time. Specifically, each event is generated and streamed as a CloudEvent with the following JSON structure:

{
	"specversion": "1.0",
	"type": "com.microsoft.SQL.CES.DML.V1",
	"source": "\/",
	"id": "cc3fcdca-09c0-4f46-a8d3-5d0c3c1eb85a",
	"logicalid": "8376457a-17af-49f4-b9ea-0d5071f515f4:0000002C000007300011:00000000000000000002",
	"time": "2025-06-30T12:29:46.290Z",
	"datacontenttype": "application\/avro-json",
	"operation": "UPD",
	"segmentindex": 1,
	"finalsegment": true,
	"data": "{\n  \"eventsource\": {\n    \"db\": \"CesDemo\",\n    \"schema\": \"dbo\",\n    \"tbl\": \"Product\",\n    \"cols\": [\n      {\n        \"name\": \"ProductId\",\n        \"type\": \"int\",\n        \"index\": 0\n      },\n      {\n        \"name\": \"ItemsInStock\",\n        \"type\": \"smallint\",\n        \"index\": 5\n      }\n    ],\n    \"pkkey\": [\n      {\n        \"columnname\": \"ProductId\",\n        \"value\": \"2\"\n      }\n    ],\n    \"transaction\": {\n      \"commitlsn\": \"0000002C:00000730:0011\",\n      \"beginlsn\": \"0000002C:00000730:000C\",\n      \"sequencenumber\": 2,\n      \"committime\": \"2025-06-30T12:29:46.290Z\"\n    }\n  },\n  \"eventrow\": {\n    \"old\": \"{\\\"ProductId\\\": \\\"2\\\", \\\"ItemsInStock\\\": \\\"8\\\"}\",\n    \"current\": \"{\\\"ProductId\\\": \\\"2\\\", \\\"ItemsInStock\\\": \\\"7\\\"}\"\n  }\n}"
}

In this sample, the operation property is UPD, indicating an UPDATE operation on a table. Also notice that there is nested JSON contained in the data property, which you can unpack to get the necessary details of each DML operation:

{
	"eventsource": {
		"db": "CesDemo",
		"schema": "dbo",
		"tbl": "Product",
		"cols": [
			{
				"name": "ProductId",
				"type": "int",
				"index": 0
			},
			{
				"name": "ItemsInStock",
				"type": "smallint",
				"index": 5
			}
		],
		"pkkey": [
			{
				"columnname": "ProductId",
				"value": "2"
			}
		],
		"transaction": {
			"commitlsn": "0000002C:00000730:0011",
			"beginlsn": "0000002C:00000730:000C",
			"sequencenumber": 2,
			"committime": "2025-06-30T12:29:46.290Z"
		}
	},
	"eventrow": {
		"old": "{\"ProductId\": \"2\", \"ItemsInStock\": \"8\"}",
		"current": "{\"ProductId\": \"2\", \"ItemsInStock\": \"7\"}"
	}
}

Here, the eventsource property describes the database, schema, table, columns, primary key, and transaction for the event. And the eventrow property is yet another nested level of JSON within the CloudEvent which provides the actual column values affected by the event as a collection of key/value pairs.

In Part 2, we’ll build a consumer application that listens for events, unpackages the CloudEvent payload, and processes them in real time.

Posted in Azure SQL Database, SQL Server 2025. Tags: azure-event-hubs, change-event-streaming, sql, SQL Server. Leave a Comment »

Creating REST and GraphQL Endpoints with Data API Builder (DAB)

September 5, 2024 — Leonard Lobel

We’ve all been there—stuck in the repetitive grind of building APIs over databases, wishing there was a faster, more efficient way to get the job done. That’s exactly the frustration that led to the creation of Data API Builder (DAB). Instead of getting bogged down in the tedium of manually crafting each endpoint, DAB does all the work, allowing you to focus on the parts of your project that truly need your attention and expertise.

Simplifying API Creation

DAB is all about simplicity and efficiency. The entire process revolves around a configuration file where you specify the entities—whether they’re tables, views, or stored procedures—that you want to expose as either REST or GraphQL endpoints (or both). Here’s what that configuration might look like:

{
    "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.6.13/dab.draft.schema.json",
    "data-source": {
        "database-type": "mssql",
        "connection-string": "Server=localhost;Database=Library;Integrated Security=true;TrustServerCertificate=true"
    },
    "runtime": {
        "rest": {
            "enabled": true,
            "path": "/api"
        },
        "graphql": {
            "allow-introspection": true,
            "enabled": true,
            "path": "/graphql"
        },
        "host": {
            "mode": "development",
            "cors": {
                "origins": [],
                "allow-credentials": false
            },
            "authentication": {
                "provider": "StaticWebApps"
            }
        }
    },
    "entities": {
        "Author": {
            "source": "dbo.Author",
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "*"
                    ]
                }
            ],
            "relationships": {
                "Books": {
                    "cardinality": "many",
                    "target.entity": "Book",
                    "linking.object": "dbo.BookAuthor"
                }
            }
        },
        "Book": {
            "source": "dbo.Book",
            "permissions": [
                {
                    "role": "anonymous",
                    "actions": [
                        "*"
                    ]
                }
            ],
            "relationships": {
                "Authors": {
                    "cardinality": "many",
                    "target.entity": "Author",
                    "linking.object": "dbo.BookAuthor"
                }
            }
        }
    }
}

Let’s break this down a bit. The data-source section specifies that the database type is mssql for SQL Server, along with the necessary connection string. In the runtime section, you can see that both REST and GraphQL endpoints are enabled, each with their own respective URI paths. The entities section defines which entities should be exposed—in this case, the Book and Author tables. These tables are linked in a many-to-many relationship through the BookAuthor junction table. This configuration file essentially maps out how your API will interact with your database, making it easy to expose the desired data while maintaining control over access and structure.

One of the great things about DAB is that you don’t have to manage this configuration file manually. You get a CLI that lets you to maintain and update the configuration without ever needing to dive into the raw JSON. This makes it even easier to adapt and scale your API as your project grows, ensuring that your endpoints stay in sync with your database structure.

Once the configuration is set and the API is started, clients can issue requests to the REST and GraphQL endpoints. For example, issuing an HTTP GET request to the following REST endpoint retrieves books with more than 500 pages, sorted by the page count, and returns just the title of each book:

http://localhost:5000/api/Book?$filter=Pages gt 500&$orderby=Pages&$select=Title

REST vs. GraphQL: Choosing the Right Approach

When building APIs, the choice between REST and GraphQL often comes down to the structure of your data and the needs of your application. REST is the more traditional approach, where each endpoint is designed to interact with a single entity. For instance, if you need to retrieve an author and their books, you might have to make multiple REST calls: one to get the author details and another to fetch the associated books. While this model is straightforward and well-suited for many applications, it can lead to over-fetching or under-fetching of data, especially in more complex scenarios.

GraphQL, on the other hand, allows for more flexibility and efficiency by enabling clients to specify exactly what data they need in a single request. This is particularly powerful when dealing with related entities. In a GraphQL query, you can request an author along with all their related books in a single call, effectively returning a graph of interconnected data. This not only reduces the number of requests made to the server but also provides a more efficient way to handle complex data structures.

With Data API Builder (DAB), this flexibility is seamlessly integrated. DAB automatically constructs the appropriate SQL statements to join related tables based on the relationships defined in your configuration. This means that whether you’re exposing your data via REST or GraphQL, DAB handles the complexity behind the scenes, allowing you to focus on the higher-level logic of your application.

Why DAB is a Game-Changer for CRUD Operations

When it comes to CRUD operations—Create, Read, Update, Delete—DAB really shines. It takes the hassle out of providing secure, direct access to your database tables (and views) by generating the necessary endpoints automatically. There’s no need to write any code yourself; DAB handles it all, giving you immediate access to your data through a clean, consistent API. This is a massive time-saver, especially for developers who need to quickly spin up APIs for internal tools, prototypes, or even production applications.

Exposing Stored Procedures

If exposing your tables directly doesn’t align with your security needs or architectural preferences, DAB allows you to add a layer of control by using stored procedures. This way, you can shield your tables from direct access and instead expose only the business logic that you want to make available via the API. This approach gives you the best of both worlds: easy API generation with the ability to enforce business rules and data security.

Supporting Multiple Databases

DAB supports multiple database platforms, making it a valuable tool in a variety of environments. For relational databases like SQL Server, Azure SQL Database, and MySQL, DAB delivers a seamless experience. Once you specify the entities in your configuration file, DAB creates REST endpoints that allow for straightforward interaction with your data. Additionally, it generates GraphQL endpoints that can join related rows, enabling you to fetch an entire entity graph in one go, which is especially powerful in complex applications.

On the other hand, if you’re working with Azure Cosmos DB, you might find DAB’s advantages less intriguing. That’s because Cosmos DB already comes with a native REST API, which provides CRUD operations out of the box. Furthermore, because Cosmos DB is a NoSQL database with a denormalized data model, it doesn’t naturally benefit from the joining capabilities that make GraphQL so compelling. In these cases, while DAB can still be used, the value proposition is different, focusing more on standardization and ease of use rather than on adding new capabilities.

Securing Your APIs with DAB

Security is a top concern in any API, and DAB offers robust support for various security models to help you protect your data. One of the most powerful features is its support for Role-Based Access Control (RBAC) with Microsoft Entra ID, which allows you to enforce role-level security. This means you can ensure that users only have access to the data they’re authorized to view, adding an essential layer of protection to your API. Additionally, DAB enables filtering based on user context through database policies. These policies dynamically adjust the data returned based on who is making the request, providing a more tailored and secure API experience.

For example:

"authentication": {
    "provider": "AzureAD",
    "jwt": {
        "issuer": "https://login.microsoftonline.com/<tenant-id>/v2.0",
        "audience": "<client-id>"
    }
}

This configuration enables authentication using Entra ID, specifying the issuer and audience for JWT validation. It ensures that only users authenticated through your Entra ID setup can access the API, providing a secure and manageable way to control access based on organizational roles and policies.

Then you can fine-tune access to specific entities. For example:

"Book": {
    "source": "dbo.Book",
    "permissions": [
        {
            "role": "Book.Reader",
            "actions": ["read"]
        },


    {
            "role": "Book.Librarian",
            "actions": ["*"]
        }
    ]
}

This configuration enables readonly access to the Books entity for users assigned to the Book.Reader role. Meanwhile, users in the Book.Librarian role have full read-write access to the Books entity. Unauthenticated users, or those not in these roles, will have no access at all. This granular control over who can perform what actions on which data is crucial for building secure, role-based APIs that align with your business rules and security policies.

Best Practices for Using DAB

While DAB is designed to be easy to use, following best practices can help you avoid common pitfalls and get the most out of the tool. One important best practice is to keep your configuration files organized, especially if you’re working across multiple environments like development, staging, and production. By maintaining separate configuration files for each environment, you can avoid issues that might arise from deploying the wrong settings. This organization helps ensure that your APIs behave consistently across different stages of your development and deployment process.

It’s also crucial to optimize your queries to ensure that you’re only fetching the data you need. Over-fetching data can lead to performance bottlenecks, particularly in high-traffic environments, so fine-tuning your queries is essential. Additionally, leveraging caching, logging, and monitoring can further enhance performance and help you quickly identify and resolve any issues that arise. Paying attention to these details ensures that your APIs not only perform well but also remain secure and easy to maintain. By following these best practices, you can maximize the effectiveness of DAB and ensure that your API development process is as smooth and efficient as possible.

Ready to Learn More?

If you’re eager to get started with DAB, there are some great resources available to help you hit the ground running. The official documentation is a fantastic place to start, offering quickstarts and comprehensive guides. This documentation will walk you through everything from the initial setup to more advanced configurations, ensuring you have a solid understanding of how to make the most of DAB. It’s an invaluable resource whether you’re just getting started or looking to deepen your knowledge.

In addition to the documentation, the GitHub repository is another excellent resource. Here, you can explore the source code, check out sample projects, and even contribute to the ongoing development of DAB. Seeing the code in action can provide valuable insights into how DAB works under the hood and how you can customize it to fit your specific needs. Together, these resources offer a comprehensive toolkit for mastering DAB and integrating it into your development workflow.

Posted in Cosmos DB, SQL Server. Leave a Comment »

Granular Dynamic Data Masking (DDM) Permissions in SQL Server 2022

January 12, 2024 — Leonard Lobel

Dynamic Data Masking (DDM) is a security feature introduced back in SQL Server 2016 that obscures sensitive data in the result set of a query, ensuring that unauthorized users can’t see the data they shouldn’t access. See my older blog post at https://lennilobel.wordpress.com/2016/05/07/sql-server-2016-dynamic-data-masking-ddm/ for an introduction to this feature. This blog post explains the new DDM capabilities added in SQL Server 2022; specifically, granular permissions.

For example, here is a table with four masked columns, populated with a few rows of data:

-- Create table with a few masked columns
CREATE TABLE Membership(
    MemberId int IDENTITY PRIMARY KEY,
    FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(2, "...", 2)') NULL,
    LastName varchar(100) NOT NULL,
    Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
    DiscountCode smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL)

-- Populate table
INSERT INTO Membership VALUES 
 ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10),
 ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5),
 ('Dan', 'Mu', '555.123.4569', 'ZMu@contoso.net', 50),
 ('Jane', 'Smith', '454.222.5920', 'Jane.Smith@hotmail.com', 40),
 ('Danny', 'Jones', '674.295.7950', 'Danny.Jones@hotmail.com', 30)

In order for a user to see the data in the four masked columns, they must be granted the UNMASK permission. However, prior to SQL Server 2022, this was a database-wide permission; users that have the UNMASK permission can see every masked column in every table of every schema in the database.

The granular DDM permissions feature introduced in SQL Server 2022 is a significant enhancement that addresses this major limitation. Previously, SQL Server allowed granting or revoking the UNMASK permission only at the database level, which limited its flexibility and adoption. However, SQL Server 2022 expands this capability, offering much-needed granularity. Now, administrators can grant or revoke UNMASK permissions at various levels, providing tailored access control that matches specific security requirements.

This granular control can be applied:

Database Level: As before, affecting all masked columns across the entire database.
Schema Level: Affecting all tables within a specific schema.
Table Level: Applying to all columns within a specific table.
Column Level: The most granular level, targeting individual columns within a table.

This flexibility greatly enhances the practical use of Dynamic Data Masking by allowing precise control over who can see unmasked data, ensuring that only authorized users can access sensitive information at the level of detail appropriate to their role or needs. Let’s see how to grant UNMASK permissions at the individual column level.

We’ll set column-level UNMASK permissions for a specific user, named ContactUser, who has been tasked with reaching out to members. To facilitate this, they’ll need access to certain information that’s normally masked, specifically the FirstName, Phone, and Email columns within the Membership table. However, they don’t require access to the DiscountCode, which should remain masked:

-- Create a new user called ContactUser with no login
CREATE USER ContactUser WITHOUT LOGIN

-- Grant SELECT permissions on the Membership table to ContactUser
GRANT SELECT ON Membership TO ContactUser

-- Grant UNMASK permission on specific columns to ContactUser
GRANT UNMASK ON Membership(FirstName) TO ContactUser
GRANT UNMASK ON Membership(Phone) TO ContactUser
GRANT UNMASK ON Membership(Email) TO ContactUser

By running the above code, we’ve created ContactUser and granted them the SELECT permission on the Membership table. We’ve then gone a step further by granting the UNMASK permission, but specifically and only for the FirstName, Phone, and Email columns. This allows ContactUser to view these normally masked columns in their unmasked state, while the DiscountCode remains masked, adhering to the principle of least privilege.

Let’s see what happens when ContactUser accesses the Membership table, particularly focusing on the columns for which they’ve been granted UNMASK permissions:

-- Impersonate ContactUser to query the Membership table
EXECUTE AS USER = 'ContactUser'
SELECT * FROM Membership
REVERT

By executing the code above, you’ll notice that ContactUser can view the FirstName, Phone, and Email columns without any masking, thanks to the granular UNMASK permissions that have been explicitly granted for these columns. However, the DiscountCode remains masked, with its values randomized between 1 and 100, demonstrating the effect of the random() masking function. This behavior aligns perfectly with our intent for ContactUser, allowing them access to the necessary contact information while keeping other sensitive data, like discount codes, masked. Run the code multiple times to observe the dynamic masking in action for the DiscountCode column.

Granular DDM permissions can also be granted at the schema and table level. For example:

-- View unmasked data in all columns of all tables in the dbo schema
GRANT UNMASK ON SCHEMA::dbo TO SomeUser

-- View unmasked data in all columns of the Membership table in the dbo schema
GRANT UNMASK ON dbo.Membership TO SomeUser

This new ability in SQL Server 2022 significantly enhances the usefulness of Dynamic Data Masking in SQL Server 2022. Happy coding!

Posted in SQL Server. Leave a Comment »

Always Encrypted = Never Decrypted (Except on the Client)

February 28, 2018 — Leonard Lobel

Security is, was, and always will be, of paramount concern to software developers. Ideally, we build our applications with multiple layers of security, and typically, encryption plays a key role in our efforts to protect sensitive data (passwords, credit card numbers, etc.) against unauthorized access. With the advent of cloud computing, security concerns become even more pressing, because we need to draw a clear separation between those who own our data (us, the client), from those who manage it (our cloud service provider). This is where Always Encrypted – a new security feature in SQL Server 2016 – comes into play.

Older Encryption Features

Before diving in to Always Encrypted, let’s first establish some context by reviewing existing encryption features that have been available in earlier versions of SQL Server. For one, column level encryption has been available since SQL Server 2005. This type of encryption uses either certificates or symmetric keys such that, without the required certificate or key, encrypted data remains hidden from view.

As of SQL Server 2008 (Enterprise Edition), you can also use Transparent Data Encryption (TDE) to encrypt the entire database – again, using a special certificate and database encryption key – such that without the required certificate and key, the entire database remains encrypted and completely inaccessible. A really nice feature of TDE is that any backup files made of the database are also rendered unusable without the TDE certificate and database encryption key.

From a security standpoint, these older features do serve us well. However, they suffer from two significant drawbacks. First, the very certificates and keys used for encryption are themselves stored in the database (or database server), which means that the database is always capable of decrypting the data (as long as the required certificates and keys are present). While this may have been perfectly fine some years back, it becomes a major problem today if you are interested in moving their data to the cloud. By giving up physical ownership of the database, you are handing over the encryption keys and certificates to your cloud provider, empowering them to access your private data.

Another issue is the fact that these older features only encrypt data “at rest” – meaning, data is only encrypted when it is being written to disk. So even if, for example, SSL is used to encrypt a credit card number between the client browser and the web server, it then gets sent in clear text over the wire from the web server to the database server, where it ultimately gets encrypted once more in database storage. And the same is true in the opposite direction, when reading back the credit card. So, between the web server and the database server, a hacker could sniff out your sensitive data. Again, this was perhaps a lesser concern years ago, but nowadays customers also want to move their applications and virtual networks to the cloud as well, not just their databases. Yet migrating to the cloud also means handing over the sensitive network connection between the web and database servers, that used to be in your control when that infrastructure was hosted in your own data centers.

And so now, in SQL Server 2016 (all editions), we have Always Encrypted, where “always” really means all the time. So data is encrypted not just at rest, but also in flight. Furthermore, the encryption keys themselves – which are essential for both encrypting and decrypting – are not stored in the database. Those keys stay with you, the client. And that’s why, as you’ll see, Always Encrypted is a hybrid feature, where all the encryption and decryption occurs exclusively on the client side.

With this approach, you effectively separate the clients who own the data, from the cloud providers who manage it. Because the data is always encrypted, SQL Server is powerless to decrypt it. All it can do is serve it up in its encrypted state, so that the data is also encrypted in flight. Only when it arrives at the client can it be decrypted, on the client, by the client, who possesses the necessary keys. Likewise, when inserting or updating new data, that data gets encrypted immediately on the client, before it ever leaves the client, and remains encrypted in-flight all the way to the database server, where SQL Server can only store it in that encrypted state – it cannot decrypt it. This is classic GIGO (garbage in, garbage out), as far as SQL Server is concerned.

Enabling Always Encrypted is a bit of a process, and one which is made much easier using the Always Encrypted Wizard in SQL Server Management Studio. As you’ll see, the wizard really streamlines the whole process, and makes it virtually painless to take an existing, unencrypted table, and convert it into a table with always encrypted columns in it.

Encryption Types

Always Encrypted supports two types of encryption: randomized and deterministic.

Randomized encryption is the most secure, because it randomly and unpredictably generates completely different results for the same value. However, this randomness means that there’s no equality support, because the same data always has a different encrypted value. So you’ll never be able to query on a column using randomized encryption, nor would you be able to join tables on this column, or group, or index, this column, or perform any other operation that requires a deterministic equality test.

For that, you’ll use deterministic encryption, which always generates the same encrypted result for the same data, repeatedly and predictably. This gives you the equality support you need to query on it, index on it, and so on, but is less secure because a hacker could start analyzing patterns to figure out how to decrypt your data. The risk increases significantly when working with small sets of values, particularly Boolean (bit) values like True/False, or Male/Female, because the hacker will only see two distinct values in a bit column that uses deterministic encryption. However, you should be aware that range searching will not be possible even with deterministic encryption, because it’s impossible to determine sequence of values, even if the encrypted value itself is deterministic.

Encryption Keys

Always Encrypted uses a key to encrypt one or more columns in a table; and thus, we call this key the column encryption key, or CEK. The CEK is used on the client to encrypt and decrypt values in whichever column – or columns – you apply the CEK to. So if you have multiple columns you want to encrypt, you could either create a single CEK and apply it to all the columns, or you could create one CEK for each column, or you could mix it up, and apply one or more CEKs to one or more columns in any combination you’d like. Remember that the CEK is used on the client – it is never exposed in the database, which is what keeps it undecryptable (if that’s a word) at the database level. However, the database does store encrypted versions of the CEKs.

So the columns get encrypted by the CEKs, and the CEKs themselves get encrypted by the CMK, or column master key. And only the client possesses the CMK, so the database can’t do anything with the encrypted CEKs other than give them to the client, who can then use the CMK to decrypt the CEKs, and then use the decrypted CEKs to encrypt and decrypt data.

Always Encrypted requires that the client-side CMK be stored in a trusted location, and supports several options for this, including Azure Key Vault – which is a secure, cloud-based repository, local certificate stores, and hardware security modules. We’ll be using the local certificate store to contain our CMK in the following walkthrough.

Always Encrypted Workflow

Let’s start with a simple Customer table with a few columns that we’d like to protect with Always Encrypted:

Here you see the table with three columns, and we’d like to encrypt the Name and social security number columns. In SQL Server Management Studio, the Always Encrypted wizard creates a column encryption key (CEK) for us, which will be used to encrypt and decrypt the Name and SSN columns on the client (again, multiple CEKs are supported, but not with the wizard). It also creates a column master key (CMK).

Remember that the CMK must be stored in a trusted location, and the wizard supports either the local certificate store or Azure Key Vault.

Once the CMK is stored away (client-side) the wizard uses it to encrypt the CEK, and then stores the encrypted CEK in the database. To be clear, the database receives only the encrypted CEK, which it can’t do anything with given the fact that it doesn’t also receive the CMK. Instead, it receives the client-side location of the CMK. Thus, the database has all the metadata that the client needs for encryption. The client can therefore connect to the database and discover all the CEKs, as well as the path to where the CMK is stored in either the certificate store or Azure Key Vault:

Next, the wizard begins the encryption process, which is to say that it creates a new temporary table, transfers data into it from the Customer table, and encrypts the data on the fly. Again, the encryption occurs client-side, within SSMS, and when it’s finished, the wizard drops the Customer table, and renames the temporary table to Customer:

At this point, we’re done with the wizard and with SSMS. And this is what we’re left with: a database with encrypted data that is absolutely powerless to decrypt itself. Because you need the CEK to decrypt, but the database only has an encrypted CEK. And you need the CMK to decrypt the CEK, but the database only has a path to the CMK (accessible just to the client), not the CMK itself:

So now it’s time to work with this data from your client application. Say you need to retrieve a customer name by querying on the customer’s social security number. Here’s your query, which references the Name column in the SELECT, and the SSN column in the WHERE clause, and both of these columns are encrypted in the database:

Your application passes this query on to ADO.NET, which would normally just pass it on as-is to SQL Server. But that won’t work now because we need to encrypt the social security number first, on the client, before issuing the query. This is going to involve a bit of a conversation between SQL Server and the client, and this conversation gets initiated by including a special setting in the database connection string – Column Encryption Setting=Enabled. With this setting in place, SQL Server will send the encrypted CEK back to the client, and will also send the path to the CMK back to the client. Then the client retrieves the CMK from the trusted location (the local certificate store, or Azure Key Vault) and uses it to decrypt the CEK.

Now the client has a CEK that it can use for encryption and decryption. So it can translate the query and send it out over the wire looking like this:

That is how the query appears “in-flight”, and anyone hacking the wire will only get this encrypted view of the social security number (called the cipher-text). This can work only if we are using deterministic encryption for the social security number, so the same social security number will always encrypt to the same cipher-text value, making it possible to query on it.

What about the Name column? Well, the encrypted name is being returned here in the query result, but we could be using either randomized or deterministic encryption for this column. In this scenario, we can say that we only query on the social security number, but never on the name, in which case we’d prefer to use randomized encryption for the name, which is less predictable and thus more secure than deterministic encryption.

The cipher-text in the query result, whether it’s been randomly or deterministically encrypted, is what gets sent back to ADO.NET on the client, so that on the return trip back, the name in the result is still encrypted. Only when the result arrives at the client does ADO.NET use the CEK to decrypt it, like so:

Encrypting an Existing Table

Let’s see all this work on a real table. I’ll create the Customer table with a few columns, and then populate the table with a couple of rows.

You can see that nothing is encrypted yet, and all the data is clearly visible:

I’m also able to query on the Name column, which is case-insensitive:

I can also run a case-insensitive query on the SSN column.

And of course, I can also run a range query on the SSN column to get all those customers with SSN numbers that start with 5 or greater, and alphabetically, this includes the string n/a.

So now let’s use the wizard to encrypt the name and SSN column with Always Encrypted. I’ll right-click on the database, and choose Tasks > Encrypt Columns… to fire up the Always Encrypted wizard.

Click Next on the introduction page, and now we can select columns for encryption.

First, we’ll encrypt the Name column, and we’ll choose Randomized encryption. We have no requirements to ever query on the name, so we’ll want to choose the more secure encryption type. We’ll also let the wizard create a new column encryption key for the Name column

We’ll also encrypt the SSN column, but this time we’ll choose Deterministic, because we want to be able to query on a customer’s social security number. So it’s necessary to ensure that a given social security number always encrypts to the same cipher-text.

Now what about those warning symbols that appear under State? When I hover, the wizard explains that the collation for these columns is being changed from the normal, case-insensitive (CI) collation, to a binary collation (BIN2), which is case-sensitive. This impacts sorting and querying against the encrypted column, as you’ll see shortly.

Now I’ll click Next, and we can configure the column master key. As with the column encryption key, we’ll let the wizard create a new CMK, and save it to the local certificate store. When you use the local certificate store, it means that the CMK must be stored in a certificate on the local machine.

We have only two rows in our table, so this operation is going to be very quick. But large tables with many rows may take some time to process, and you might not be ready to run that process at the same time that you are running the wizard. Remember that this process involves copying the data from the unencrypted table to a new, temporary table – encrypting the data on-the-fly – and then swapping out the old table for the new one. You don’t want to interrupt this process while it’s running, so the next wizard page gives you the option of running the process now, or saving a PowerShell script that you can run later, at a time that’s more convenient.

We’ll allow the process to run right now, so I’ll click Next once more to reach the Summary page

Here, a quick review shows that the wizard will create a new column master key (CMK) that will be used to encrypt a new column encryption key (CEK), and that the new CMK will be saved to the local machine certificate store. We can also see that the CEK will be used to encrypt the Name and SSN columns, with the Name column using randomized encryption, and the SSN column using deterministic encryption.

Finally, I’ll click Finish to let it run.

In the Certificates console, you can see that a new Always Encrypted certificate was created automatically. This is, in essence, our CMK. If I drill into the certificate details, you can see that the hashing algorithm used by the CMK is SHA256.

Back in SSMS, let’s see how our table has been altered by the actions of the wizard. I’ll right-click the Customer table, and script it out to a new query window:

The CustomerId and City columns look exactly as we defined them, but there’s some new syntax here on the Name and SSN columns. Their datatypes haven’t changed – they are still varchar strings – but the collation has been changed to binary, and ENCRYPTED WITH specifies the CEKs used to encrypt these columns, the encryption type (randomized or deterministic) the encryption algorithm itself.

Now let’s query the table, and, as you might have expected, we only see cipher-text for the Name and SSN columns, because they are encrypted.

Furthermore, we can’t run any queries against either of the encrypted columns.

Now let’s query the system catalog views to discover how our database is using Always Encrypted.

First, we see a master key reference, indicating that the CMK can be found in a certificate store via a local machine path. We also see a column encryption key reference, and the value of that CEK, which is encrypted by the CMK. Again, the unencrypted CEK – which is needed to encrypt and decrypt – is not contained anywhere in the database.

If we query on sys.columns joined with sys.column_encryption_keys, we can see the new metadata columns indicating that the Name and SSN columns are both protected by CEK_Auto1, and that Name is using randomized encryption, while SSN is using deterministic encryption:

Querying and Storing Encrypted Data

So how can we decrypt this data and read it?
Remember, it’s the client that does this, and the client will decrypt it for us if we just connect to the database using the special setting Column Encryption Setting=Enabled.
In this case, the client is SSMS, so just right-click in the query window and choose Connection > Change Connection. Then, under Additional Connection Parameters, plug in column encryption setting=enabled:

This action, of course, resets our connection, so I’ll first switch from master back to our encrypted database, and then query the Customer table once more. And now – like magic – we see the data fully decrypted:

Here’s what’s going on. When we reconnected with the special column encryption setting, that initiated a conversation between the client (SSMS in this case) and SQL Server. SQL Server has passed the CEK (encrypted by the CMK) to the client, as well as the location of the CMK, which is our local certificate store in this case. SSMS has then gone ahead and retrieved the CMK from the certificate store, and used it to decrypt the CEK. At that point, SQL Server returned encrypted data as before, but this time the client (again, SSMS) used the CEK to decrypt the results.

However, in SSMS we are still unable to query on the encrypted columns and we also cannot insert new data for the encrypted columns.

This is because these actions need to be parameterized by an ADO.NET client. To demonstrate, we’ll execute some ADO.NET client code. In Visual Studio, I’ll run some code where I’ve appended column encryption setting=enabled to our connection string, and then I’ll issue the same simple SELECT statement against the Customer. And we can clearly see the name and SSN columns, which have been decrypted.

What about querying directly on the encrypted columns? Well, this should be possible, but with restrictions.

First, because we’re using randomized encryption for the Name column, you can simply never query on it. This is a limitation that we accept in return for the more secure type of encryption, which produces unpredictable values.

The SSN column uses deterministic encryption, making it possible to query against it, but only as a case-sensitive equality query. So when we query for the number of customers where the SSN is n/a, we get a count of 1 for the 1 customer SSN that matches. However, the lowercase n/a SSN will not get matched if we query for N/A in uppercase.

And we’re also restricted to an equality compare; so greater-than, less-than, and BETWEEN queries are not permitted. This makes sense of course, because even though the encryption is deterministic, which produces consistent and predictable results, those encrypted results do not retain the sequence of their unencrypted values.

Now let’s try the INSERT again. This failed in SSMS, but it should work this time, because we’ve set up a parameterized SqlCommand object. So I’ll prepare the command object with the INSERT statement, and then populate the parameters collection with the values for the new name, SSN, and City – where we expect the client to encrypt the SSN and City columns before sending out the command.

Sure enough, we don’t fail this time, and the new customer row gets added successfully:

Returning back to SSMS, querying the table now shows the newly inserted row, along with the earlier three rows, and the data appears decrypted because we’re still running on the SSMS connection with the column encryption setting:

Limitations and Considerations

Before concluding, it’s worth taking a moment to think about some issues you may run into using Always Encrypted. This is a great new technology, but it does have some limitations

First, there is no support for any of the large object and other specialized data types, which cannot be encrypted. There are also other notable incompatibilities with Always Encrypted to point out. Most of these aren’t too onerous, but notably you can’t use Always Encrypted with the new Temporal or Stretch features in SQL Server 2016.
Entity Framework was certainly not designed to work with Always Encrypted, but it is possible. If you’re building applications with EF6 against Always Encrypted tables, then check out this article here which talks you through the various workarounds needed for these two technologies to get along:

http://blogs.msdn.com/b/sqlsecurity/archive/2015/08/27/using-always-encrypted-with-entity-framework-6.aspx

You should also always remember that encryption and decryption always happen on the client, and this translates into additional management to ensure that clients can access the CMK. If you’re using the local machine certificate store, this means the additional administrative burden of deploying certificates to all the clients that need access to encrypted data.

And, there are several other restrictions, for which Aaron Bertrand has done an excellent job articulating in his blog post here, at this link:

http://blogs.sqlsentry.com/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations/

Conclusion

This post began by reviewing encryption capabilities available in earlier versions of SQL Server, which store keys and certificates on the server, and which encrypts data only at rest

Then I introduced Always Encrypted, a new SQL Server 2016 feature in which the client – not the server – handles encryption and decryption. This makes it a very attractive feature for those customer’s that remain reticent to move their database to the cloud, because they don’t want to move their encryption keys to the cloud as well.

We discussed the two encryption types, where randomized provides more secure encryption based on unpredictable values. Such unpredictability means that you can never query on the encrypted data, and that’s why we also have deterministic encryption. This produces the same encrypted values every time, making it more predictable and les secure, but this also makes it possible to query on the encrypted data, with some restrictions. You learned how the CMK is used to encrypt each CEK, which in turn is used to encrypt the actual data in your tables.

Posted in SQL Server, SQL Server 2016. 1 Comment »

Using T4 Templates to Generate C# Enums from SQL Server Database Tables

April 26, 2017 — Leonard Lobel

When building applications with C# and SQL Server, it is often necessary to define codes in the database that correspond with enums in the application. However, it can be burdensome to maintain the enums manually, as codes are added, modified, and deleted in the database.

In this blog post, I’ll share a T4 template that I wrote which does this automatically. I looked online first, and did find a few different solutions for this, but none that worked for me as-is. So, I built this generic T4 template to do the job, and you can use it too.

Let’s say you’ve got a Color table and ErrorType table in the database, populated as follows:

Now you’d like enums in your C# application that correspond to these rows. The T4 template will generate them as follows:

Before showing the code that generates this, let’s point out a few things.

First, notice that the Color table has only an ID and a name column, while the ErrorType table has an ID, name, and description columns. Thus, the ErrorType enums include XML comments based on the descriptions in the database, which appear in IntelliSense when referencing the enums in Visual Studio.

Also notice that one enum was generated for the Color table, while two enums were generated for the ErrorType table. This is because we instructed the T4 template to create different enums for different value ranges (0 – 999 for SystemErrorType, and 1000 – 1999 for CustomerErrorType) in the same table.

Finally, the Color enum includes an additional member Undefined with a value of 0 that does not appear in the database, while the other enums don’t include the Undefined member. This is because, in the case of Color, we’d like an enum to represent Undefined, but we don’t want it in the database because we don’t want to allow 0 as a foreign key into the Color table.

This gives you an idea of the flexibility that this T4 template gives you for generating enums from the database. The enums above were generated with the following T4 template:

<#@include file="EnumsDb.ttinclude" #>
<#
  var configFilePath = "app.config";

  var enums = new []
  {
    new EnumEntry
      ("Supported colors", "DemoDatabase", "dbo", "Color", "ColorId", "ColorName", "ColorName")
      { GenerateUndefinedMember = true },

    new EnumEntry
      ("System error types", "DemoDatabase", "dbo", "ErrorType", "ErrorTypeId", "ErrorTypeName", "Description")
      { EnumName = "SystemErrorType", ValueRange = new Tuple<long, long>(0, 999) },

    new EnumEntry
      ("Customer error types", "DemoDatabase", "dbo", "ErrorType", "ErrorTypeId", "ErrorTypeName", "Description")
      { EnumName = "CustomerErrorType", ValueRange = new Tuple<long, long>(1000, 1999) },
  };

  var code = this.GenerateEnums(configFilePath, enums);

  return code;
#>

You can see that there is very little code that you need to write in the template, and that’s because all the real work is contained inside the EnumsDb.ttinclude file (referenced at the top with @include), which contains the actual code to generate the enums, and can be shared throughout your application. A complete listing of the EnumsDb.ttinclude file appears at the end of this blog post.

All you need to do is call GenerateEnums with two parameters. The first is a path your application’s configuration file, which holds the database connection string(s). The second is an array of EnumEntry objects, which drives the creation of enums in C#. Specifically, in this case, there are three EnumEntry objects, which results in the three enums that were generated.

Each enum entry contains the following properties:

Description (e.g., “Supported colors”). This description appears as XML comments for the enum.
Database connection string key (e.g., “DemoDatabase”). It is expected that a connection string named by this key is defined in the application’s configuration file. Since this property exists for each enum entry, it is entirely possible to generate different enums from different databases.
Schema name (e.g., “dbo”). This is the schema that the table is defined in.
Table name (e.g., “Color”). This is the table that contains rows of data for each enum member.
ID column name (e.g., “ColorId”). This is the column that contains the numeric value for each enum member. It must be an integer type, but it can be an integer of any size. The generator automatically maps tinyint to byte, int to int, smallint to short, and bigint to long.
Member column name (e.g., “ColorName”). This is the column that contains the actual name of the enum member.
Description column name. This should be the same as the Member column name (e.g., “ColorName”) if there is no description, or it can reference another column containing the description if one exists. If there is a description, then it is generated as XML comments above each enum member.
Optional:

EnumName. If not specified, the enum is named after the table. Otherwise, you can choose another name if you don’t want the enum named after the table name.
GenerateUndefinedMember. If specified, then the template will generate an Undefined member with a value of 0.
ValueRange. If specified, then enum members are generated only for rows in the database that fall within the specified range of values. The ErrorType table uses this technique to generate two enums from the table; one named SystemErrorType with values 0 – 999, and another named CustomerErrorType with values 1000 – 1999.

This design offers a great deal of flexibility, while constantly ensuring that the enums defined in your C# application are always in sync with the values defined in the database. And of course, you can modify the template as desired to suit any additional needs that are unique to your application.

The full template code is listed below. I hope you enjoy using it as much as I enjoyed writing it. Happy coding! 😊

<#@ template hostspecific="true" language="C#" debug="false" #>
<#@ assembly name="System.Configuration" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text" #>
<#+
  private string GenerateEnums(string configFilePath, EnumEntry[] entries)
  {
    if (entries == null)
    {
      return string.Empty;
    }

    var ns = System.Runtime.Remoting.Messaging.CallContext.LogicalGetData("NamespaceHint");
    var sb = new StringBuilder();
    sb.AppendLine(this.GenerateHeaderComments());
    sb.AppendLine($"namespace {ns}");
    sb.AppendLine("{");
    foreach(var entry in entries)
    {
      try
      {
        sb.Append(this.GenerateEnumMembers(configFilePath, entry));
      }
      catch (Exception ex)
      {
        sb.AppendLine($"#warning Error generating enums for {entry.EnumDescription}");
        sb.AppendLine($"  // Message: {ex.Message}");
      }
    }
    sb.AppendLine();
    sb.AppendLine("}");
    return sb.ToString();
  }

  private string GenerateHeaderComments()
  {
    var comments = $@"
// ------------------------------------------------------------------------------------------------
// <auto-generated>
//  This code was generated by a C# code generator
//  Generated at {DateTime.Now}
// 
//  Warning: Do not make changes directly to this file; they will get overwritten on the next
//  code generation.
// </auto-generated>
// ------------------------------------------------------------------------------------------------
    ";

    return comments;
  }

  private string GenerateEnumMembers(string configFilePath, EnumEntry entry)
  {
    var code = new StringBuilder();
    var connStr = this.GetConnectionString(configFilePath, entry.ConnectionStringKey);
    var enumDataType = default(string);
    using (var conn = new SqlConnection(connStr))
    {
      conn.Open();
      using (var cmd = conn.CreateCommand())
      {
        cmd.CommandText = @"
          SELECT DATA_TYPE
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND COLUMN_NAME = @IdColumnName
        ";

        cmd.Parameters.AddWithValue("@SchemaName", entry.SchemaName);
        cmd.Parameters.AddWithValue("@TableName", entry.TableName);
        cmd.Parameters.AddWithValue("@IdColumnName", entry.IdColumnName);

        var sqlDataType = cmd.ExecuteScalar();
        if(sqlDataType == null)
        {
          throw new Exception($"Could not discover ID column [{entry.IdColumnName}] data type for enum table [{entry.SchemaName}].[{entry.TableName}]");
        }
        enumDataType = this.GetEnumDataType(sqlDataType.ToString());

        var whereClause = string.Empty;
        if (entry.ValueRange != null)
        {
          whereClause = $"WHERE [{entry.IdColumnName}] BETWEEN {entry.ValueRange.Item1} AND {entry.ValueRange.Item2}";
        }

        cmd.CommandText = $@"
          SELECT
            Id = [{entry.IdColumnName}],
            Name = [{entry.NameColumnName}],
            Description = [{entry.DescriptionColumnName}]
          FROM
            [{entry.SchemaName}].[{entry.TableName}]
          {whereClause}
          ORDER BY
            [{entry.IdColumnName}]
        ";
        cmd.Parameters.Clear();

        var innerCode = new StringBuilder();
        var hasUndefinedMember = false;
        using (var rdr = cmd.ExecuteReader())
        {
          while (rdr.Read())
          {
            if (rdr["Name"].ToString() == "Undefined" || rdr["Id"].ToString() == "0")
            {
              hasUndefinedMember = true;
            }
            if (entry.NameColumnName != entry.DescriptionColumnName)
            {
              innerCode.AppendLine("\t\t/// <summary>");
              innerCode.AppendLine($"\t\t/// {rdr["Description"]}");
              innerCode.AppendLine("\t\t/// </summary>");
            }
            innerCode.AppendLine($"\t\t{rdr["Name"].ToString().Replace("-", "_")} = {rdr["Id"]},");
          }
          rdr.Close();
        }
        if ((entry.GenerateUndefinedMember) && (!hasUndefinedMember))
        {
          var undefined = new StringBuilder();

          undefined.AppendLine("\t\t/// <summary>");
          undefined.AppendLine("\t\t/// Undefined (not mapped in database)");
          undefined.AppendLine("\t\t/// </summary>");
          undefined.AppendLine("\t\tUndefined = 0,");

          innerCode.Insert(0, undefined);
        }
        code.Append(innerCode.ToString());
      }
      conn.Close();
    }

    var final = $@"
  /// <summary>
  /// {entry.EnumDescription}
  /// </summary>
  public enum {entry.EnumName} : {enumDataType}
  {{
    // Database information:
    //  {entry.DbInfo}
    //  ConnectionString: {connStr}

{code}
  }}
    ";

    return final;
  }

  private string GetConnectionString(string configFilePath, string key)
  {
    var map = new ExeConfigurationFileMap();
    map.ExeConfigFilename = this.Host.ResolvePath(configFilePath);
    var config = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);
    var connectionString = config.ConnectionStrings.ConnectionStrings[key].ConnectionString;

    return connectionString;
  }

  private string GetEnumDataType(string sqlDataType)
  {
    var enumDataType = default(string);
    switch (sqlDataType.ToString())
    {
      case "tinyint"  : return "byte";
      case "smallint" : return "short";
      case "int"      : return "int";
      case "bigint"   : return "long";
      default :
        throw new Exception($"SQL data type {sqlDataType} is not valid as an enum ID column");
    }
  }

  private class EnumEntry
  {
    public string EnumDescription { get; }
    public string ConnectionStringKey { get; }
    public string SchemaName { get; }
    public string TableName { get; }
    public string IdColumnName { get; }
    public string NameColumnName { get; }
    public string DescriptionColumnName { get; }

    public string EnumName { get; set; }
    public Tuple<long, long> ValueRange { get; set; }
    public bool GenerateUndefinedMember { get; set; }

    public string DbInfo
    {
      get
      {
        var info = $"ConnectionStringKey: {this.ConnectionStringKey}; Table: [{this.SchemaName}].[{this.TableName}]; IdColumn: [{this.IdColumnName}]; NameColumn: [{this.NameColumnName}]; DescriptionColumn: [{this.DescriptionColumnName}]";
        if (this.ValueRange != null)
        {
          info = $"{info}; Range: {this.ValueRange.Item1} to {this.ValueRange.Item2}";
        }
        return info;
      }
    }

    public EnumEntry(
      string enumDescription,
      string connectionStringKey,
      string schemaName,
      string tableName,
      string idColumnName = null,
      string nameColumnName = null,
      string descriptionColumnName = null
    )
    {
      this.EnumDescription = enumDescription;
      this.ConnectionStringKey = connectionStringKey;
      this.SchemaName = schemaName;
      this.TableName = tableName;
      this.IdColumnName = idColumnName ?? tableName + "Id";
      this.NameColumnName = nameColumnName ?? "Name";
      this.DescriptionColumnName = descriptionColumnName ?? "Description";
      this.EnumName = tableName;
    }

  }
#>

Posted in C#, SQL Server. Leave a Comment »

« Older posts

Leonard Lobel

Leonard Lobel

Compute Product of Prices for Each Product

Alternative using OVER (PARTITION BY ...)

Compounded Return from Periodic Rates

Alternative using OVER (PARTITION BY ...)

Summary

Encoding with BASE64_ENCODE and Decoding with BASE64_DECODE

URL-Safe Base64 Encoding

Practical Examples

Summary

Create the sample table and data

REGEXP_LIKE

Get rows with valid email addresses

Get rows with valid email addresses that end with .com

Get rows with valid phone numbers

REGEXP_COUNT

Indicate valid and invalid email addresses and phone numbers

Count the number of vowels in each name

Count specific “sentiment” words from each review

CHECK constraints

Attempt to add constraints before cleaning the data

Delete rows with invalid email addresses and phone numbers

Add CHECK constraints

REGEXP_SUBSTR

Extract the domain name of each valid email address

Show how many email addresses there are for each domain

REGEXP_INSTR

Find the position of the @ character and the first . after it

REGEXP_REPLACE

Strip middle names

REGEXP_MATCHES

Extract words starting with A followed by any two characters

Extract hashtags from text

Extract hashtags from the review text in the database

REGEXP_SPLIT_TO_TABLE

Extract individual words from text using whitespace as the delimiter

Extract individual words from review text

Clean punctuation from the split words

Final thoughts

Legacy Options: SOUNDEX and DIFFERENCE

New in SQL Server 2025: Four Modern Fuzzy Matching Functions

Exploring with Word Pairs

Clean up the example table

Real-World Deduping (Customer Records)

Score First Name Similarity

Score Last Name Similarity

Score Address Similarity

Combine All Three: A Composite Match Score

Final Thoughts

Create a Blob Storage Container

Create a Storage Account

Create a Blob Container

Create the Visual Studio Project

Install NuGet Packages

Add a Configuration File

Add the Code

Process Events

Display Event Metadata

Handle Inserts

Handle Updates

Handle Deletes

Process Errors

Run the Application

Generate and Monitor Change Events

Create an Order

Create Order Details

Delete an Order

Update a Customer

Bulk Update Products

Update a Table Without a Primary Key

Conclusion

Step 1: Create an Event Hub

Create an Event Hub Namespace

Create an Event Hub

Create an Event Hub Policy

Generate a SAS Token

Install PowerShell Modules

Create the SAS Token Script

Run the Script

Alternative using `OVER (PARTITION BY ...)`

Alternative using `OVER (PARTITION BY ...)`

Encoding with `BASE64_ENCODE` and Decoding with `BASE64_DECODE`