SQL Server | Lenni's Technology Blog

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 »

Calling C++ From SQL CLR C# Code

January 29, 2014 — Leonard Lobel

Ever since Microsoft integrated the .NET Common Language Runtime (CLR) into the relational database engine back in SQL Server 2005, the recommended technique for extending T-SQL with custom code has been to use SQL CLR with a .NET language (such as C# or VB). This is because CLR code is managed code, meaning that at runtime, the .NET framework ensures that ill-behaved code can never crash the process it’s running in. Prior to SQL CLR, the only way to extend T-SQL was with extended stored procedures written in native C++. Because native code is unmanaged, buggy C++ code can all too easily crash the process it’s running in. In the case of an extended stored procedure, this means crashing SQL Server itself, which I think we can all agree is a bad thing. It is for this very reason that extended stored procedures are deprecated in SQL Server and why SQL CLR is the way to go instead.

That said, you may have an existing C++ dynamic link library (DLL file) which exposes some public function that you need to call from your T-SQL code. Sure, the recommended approach to take is to refactor that C++ code in C# and then call it using SQL CLR. But what if that’s not a viable option? Perhaps you don’t have access to the C++ source code, or perhaps you do and it’s prohibitively expensive to port it to C#. Or maybe you just like living on the edge. In any case, you find that you absolutely need to call into C++, but you don’t want to use extended stored procedures since they are deprecated (and relatively difficult to implement). In this scenario, you can create a C# wrapper function that calls into the C++ DLL, and then implement the C# wrapper function as a SQL CLR user-defined function (UDF). This blog post shows you exactly how to do just that.

First though, to be clear, this technique carries the same risk as extended stored procedures – just one C++ memory access violation resulting from a rogue pointer can instantly crash SQL Server. For this reason, you will see that there are a few additional steps required when implementing such a solution. Fundamentally, these additional steps make it clear that you are introducing risk, and that you absolve SQL Server of any blame if your custom code crashes the SQL Server process as a result.

The next thing to be aware of is 32/64-bit compatibility. That is, you cannot load a 32-bit C++ DLL into a 64-bit SQL Server process, and vice-versa (attempting to do so will surely result in SQL Server throwing a BadImageFormatException error). This presents no problem if you have access to the source code, since you can compile it as either a 32-bit or 64-bit DLL to match the version of SQL Server that you’re running. If you don’t have access to the source code, and the DLL that you have does not match your version of SQL Server, then that presents a greater challenge, although there are several advanced “run out-of-process” solutions that are beyond the scope of this blog post.

The following sections provide step-by-step procedures that walk you through the process of calling C++ from C# in a SQL CLR user-defined function.

Create the C++ library.
Test the C++ library (optional).
1. Call from a C++ native executable harness.
2. Call from a C# executable harness.
Create the C# SQL CLR UDF.
Deploy to SQL Server.

For simplicity’s sake, the C++ library code that we’ll be calling from SQL Server is a simple math function named AddIntegers. This function accepts two integer parameters and returns their sum. I’ll demonstrate using Visual Studio 2013, although everything works just the same with earlier Visual Studio versions (I can confirm this for certain with VS 2012 and VS 2010, but it most likely works with even older VS versions as well).

Creating the C++ library

The instructions assume that you’re running 64-bit SQL Server, and thus they also explain how to compile the native C++ code as a 64-bit DLL.

To create the C++ library, follow these steps:

Start Visual Studio 2013 (note that these instructions also work with VS 2010 and 2012).
Create the new C++ library project:
1. From the File menu, choose New Project.
2. Under Installed Templates, choose Visual C++
3. Select the Win32 Project template.
4. Name the project MathLibNative.
5. Choose any desired location to create the project; for example, C:\Demo\.
6. Name the solution MathLibFromSQL.
7. Click OK
When the Win32 Application Wizard appears:
1. Click Next.
2. For Application Type, choose DLL.
3. For Additional Options, check Empty Project.
4. Click Finish.
Create the header file. This will contain the publically visible signature to the AddIntegers function.
1. Right-click the MathLibNative project in Solution Explorer and choose Add | New Item.
2. In the Add New Item dialog, choose Header File (.h).
3. Name the file MathFunctions.h.
4. Click Add.
5. Type the following in the code editor for MathFunctions.h:
```
__declspec(dllexport) int AddIntegers(int a, int b);
```
Implement the AddIntegers function.
1. Right-click the MathLibNative project in Solution Explorer and choose Add | New Item.
2. In the Add New Item dialog, choose C++ File (.cpp).
3. Name the file MathFunctions.cpp.
4. Click Add.
5. Type the following in the code editor for MathFunctions.cpp.
```
#include "MathFunctions.h"

int AddIntegers(int a, int b)
{
  int sum = a + b;
  return sum;
}
```
Configure the library project for 64-bit, which is required since we intend to load this DLL into 64-bit SQL Server.
1. From the BUILD menu, choose Configuration Manager.
2. Click the Active Solution Platform dropdown and choose <New…> to display the New Solution Platform dialog.
3. In the Type Or Select The New Platform combobox, type MathLibNative.
4. Click OK to close the New Solution Platform dialog.
5. Click the Platform dropdown for the MathLibNative project (it is currently set for Win32, meaning 32-bit).
6. Click <New…> to display the New Project Platform dialog.
7. Choose x64 from the New Platform dropdown.
8. Click OK to close the New Project Platform dialog.
9. Click Close to close the Configuration Manager dialog.
Press CTRL+SHIFT+B to build the solution and ensure there are no compiler errors. This creates the native DLL file named MathLibNative.dll. The __declspec(dllexport) attribute in the header file (in step 4e above) also causes the compiler to generate a library file named MathLibNative.lib that you can link to from another C++ application (you will do this in the next section when you create an executable C++ test harness).

Testing the C++ library (optional)

Ultimately, we’re going to call into the native DLL file directly from a SQL CLR UDF written in C#. However, it’s often helpful to create a test harness first. This aids in debugging and helps prove that the DLL works as expected. In this section, we’ll create two test harness applications, one native C++ executable and one C# executable. The C++ executable will allow you to single-step debug into the C++ DLL, and the C# executable will help you determine the correct interface for calling into the C++ DLL. Again, this isn’t strictly necessary; you can instead jump ahead to the next section and create the SQL CLR UDF. However, the moment something doesn’t work as expected (and that moment will come), you’ll need to fall back on one or both of these test harnesses to help discover what the problem is, so it’s good to have them.

Creating a native C++ executable test harness

To create the native C++ executable test harness, follow these steps:

Create a new C++ executable project:
1. Right-click the MathLibFromSql solution in Solution Explorer and choose Add | New Project.
2. Under Installed Templates, choose Visual C++
3. Select the Win32 Project template.
4. Name the project MathLibNativeHarness.
5. Leave the default for the project location.
6. Click OK.
When the Win32 Application Wizard appears:
1. Click Next.
2. For Application Type, choose Console Application.
3. For Additional Options, check Empty Project.
4. Click Finish.
Create the harness code. This is placed in a function named main, which is the entry point for the console executable:
1. Right-click the MathLibNativeHarness project in Solution Explorer and choose Add | New Item.
2. In the Add New Item dialog, choose C++ File (.cpp).
3. Name the file Main.cpp.
4. Click Add.
5. Type the following in the code editor for MathFunctions.cpp.
```
#include "..\MathLibNative\MathFunctions.h"
#include <iostream>

int main()
{
  int sum = AddIntegers(23, 9);
  std::cout << sum << std::endl;
}
```
Configure the harness project for 64-bit.
1. From the BUILD menu, choose Configuration Manager.
2. Click the Active Solution Platform dropdown choose MathLibNative.
3. Click the Platform dropdown for the MathLibNativeHarness project.
4. Click <New…> to display the New Project Platform dialog.
5. Choose x64 from the New Platform dropdown.
6. Click OK to close the New Project Platform dialog.
7. Check the Build checkbox for the MathLibNativeHarness project.
8. Click Close to close the Configuration Manager dialog.
Link to the library.
1. Right-click the MathLibNativeHarness project in Solution Explorer and choose Properties.
2. Expand the Linker options and click Input.
3. For Additional Dependencies, click the dropdown and choose <Edit…>
4. In the Additional Dependencies dialog, type the full path to the MathLibNative.lib file generated by the compiler when it built the DLL file. If you created the solution in C:\Demo, the full path is C:\Demo\MathLibFromSQL\x64\Debug\MathLibNative.lib.
5. Click OK to close the Additional Dependencies dialog.
6. Click OK to close the project’s property pages dialog.
Press CTRL+SHIFT+B to build the solution and ensure there are no compiler errors.
Run the C++ test harness.
1. Right-click the MathLibNativeHarness project in Solution Explorer and choose Set As Startup Project.
2. Press CTRL+F5 to run the harness without the debugger.
3. The console output should appear with the expected result (32, which is 23 plus 9), proving that the DLL is being called properly.

If you wish, you can also run the harness with the debugger by pressing F5. This will display and close the console window too quickly for you to see the output, but you can set breakpoints and debug the code. You can even single-step into the DLL file itself.

Creating a C# executable test harness

Calling the native C++ DLL from C# works differently, and since that’s what we need to do in our SQL CLR UDF with a C# class library, it’s also helpful to figure out how to “get it right” first with a C# console application test harness. One critical part in getting this to work is to identify the correct entry point to the AddIntegers function. This would be easy if the entry point was simply named after the function (i.e., AddIntegers), but unfortunately that’s not the case. The entry point is based on the function name, but is preceded by a question mark symbol and suffixed with a bit of randomly generated text. Fortunately, there is a small, simple, and free tool available on the web named Dependency Walker that can discover the entry point for you.

Before creating the C# executable test harness, use Dependency Walker to discover the entry point to the AddIntegers function. To do so, follow these steps:

Download Dependency Walker from http://www.dependencywalker.com.
Extract the downloaded zip file to a folder of your choice.
Navigate to the folder and launch depends.exe.
In the Security Warning dialog, click Run.
From the File menu, choose Open.
Navigate to the folder where the native C++ DLL file was built (i.e., C:\Demo\MathLibFromSQL\x64\Debug).
Double-click the file MathLibNative.dll.
Dependency Walker may display a message that errors were detected, but this can be safely ignored; just click OK.
Locate the entry point to the AddIntegers function in the second grid on the right.
Leave this window open so you can easily copy the entry point name and paste it into the C# code in the next procedure.

To create the C# executable test harness, follow these steps:

Create a new C# executable project:
1. Right-click the MathLibFromSql solution in Solution Explorer and choose Add | New Project.
2. Under Installed Templates, choose Visual C#
3. Select the Console Application template.
4. Name the project MathLibNativeCSharpHarness.
5. Leave the default for the project location.
6. Click OK.

Replace the starter code generated by Visual Studio in Program.cs with the following:

using System;
using System.Runtime.InteropServices;

namespace MathLibNativeCSharpHarness
{
  class Program
  {
    [DllImport(@"C:\Demo\MathLibFromSQL\x64\Debug\MathLibNative.dll",
      CallingConvention = CallingConvention.Cdecl,
      EntryPoint = "?AddIntegers@@YAHHH@Z")]
    private static extern int AddIntegers(int a, int b);

    static void Main(string[] args)
    {
      int sum = AddIntegers(33, 19);
      Console.WriteLine(sum);
    }
  }
}

Note the EntryPoint parameter of the DllImport attribute. This is the value that I pasted in from Dependency Walker as I wrote this post. To use the appropriate EntryPoint parameter value for your environment:
1. Return to the Dependency Walker window that you opened to MathLibNative.dll in the previous section.
2. Right-click the Function value in the second grid on the right and choose Copy Function Name.
3. Return to the C# code and paste it in as the EntryPoint parameter value in the DllImport attribute.
Also note the pathname to MathLibNative.dll; adjust it as necessary if you have created the solution in a folder other than C:\Demo.
Configure the harness project for 64-bit.
1. From the BUILD menu, choose Configuration Manager.
2. Click the Platform dropdown for the MathLibNativeCSharpHarness project.
3. Click <New…> to display the New Project Platform dialog.
4. Choose x64 from the New Platform dropdown (it may already be selected by default).
5. Click OK to close the New Project Platform dialog.
6. Check the Build checkbox for the MathLibNativeCSharpHarness project.
7. Click Close to close the Configuration Manager dialog.
Press CTRL+SHIFT+B to build the solution and ensure there are no compiler errors.
Run the C# test harness.
1. Right-click the MathLibNativeCSharpHarness project in Solution Explorer and choose Set As Startup Project.
2. Press CTRL+F5 to run the harness without the debugger.
3. The console output should appear with the expected result (52, which is 33 plus 19), proving that the DLL is being called properly and that we have figured out the correct DllImport attribute to use in the C# code that we’ll create for the SQL CLR UDF.

Creating the C# SQL CLR UDF

You’re now ready to write the SQL CLR UDF, which is a C# method decorated with the SqlFunction attribute. This is a simple wrapper method; all it does is call into the C++ DLL just like the C# executable harness we created in the previous procedure does. The wrapper method itself accepts and returns special data types that correspond to SQL Server. In our scenario, the method accepts two SqlInt32 parameters and returns a SqlInt32 value, where SqlInt32 corresponds to the int data type in C++ and C#.

To create the C# SQL CLR UDF, follow these steps:

Create a new C# class library project.
1. Right-click the MathLibFromSql solution in Solution Explorer and choose Add | New Project.
2. Under Installed Templates, choose Visual C#
3. Select the Class Library template.
4. Name the project MathLibNativeSQLCLR.
5. Leave the default for the project location.
6. Click OK.
Delete the Class1.cs file (this file was generated automatically for the project by Visual Studio and is not needed).
1. Right-click the Class1.cs file in Solution Explorer and choose Delete.
2. Click OK to confirm that you want to delete the file Class1.cs.

Add the MathFunctions class.

Right-click the MathLibNativeSQLCLR project in Solution and choose Add | New Item.
In the Add New Item dialog, choose Class.
Name the file MathFunctions.cs.
Click Add.

Replace the starter code generated by Visual Studio in MathFunctions.cs with the following:

using System.Data.SqlTypes;
using System.Runtime.InteropServices;

namespace MathLibNativeSQLCLR
{
  public class MathFunctions
  {
    [DllImport(@"C:\Demo\MathLibFromSQL\x64\Debug\MathLibNative.dll",
      CallingConvention = CallingConvention.Cdecl,
      EntryPoint = "?AddIntegers@@YAHHH@Z")]
    private static extern int AddIntegers(int a, int b);

    public static SqlInt32 AddIntegersUdf(SqlInt32 a, SqlInt32 b)
    {
      var sum = AddIntegers((int)a, (int)b);
      return sum;
    }
  }
}

Note the EntryPoint parameter of the DllImport attribute. This is the same value we used for the C# harness in the previous procedure. If you skipped the previous procedure as optional, you need to at least follow the instructions found there for using Dependency Walker to discover the correct entry point name.
Also note the pathname to MathLibNative.dll; adjust it as necessary if you have created the solution in a folder other than C:\Demo.
Configure the SQL CLR project for 64-bit.
1. From the BUILD menu, choose Configuration Manager.
2. Click the Platform dropdown for the MathLibNativeSQLCLR project.
3. Click <New…> to display the New Project Platform dialog.
4. Choose x64 from the New Platform dropdown (it may already be selected by default).
5. Click OK to close the New Project Platform dialog.
6. Check the Build checkbox for the MathLibNativeSQLCLR project.
7. Click Close to close the Configuration Manager dialog.
Press CTRL+SHIFT+B to build the solution and ensure there are no compiler errors.

Deploying to SQL Server

You’re now ready to deploy the SQL CLR UDF you created in the previous procedure to a SQL Server database. To do this, use your tool of choice to connect to your SQL Server instance and open a new query window that you can execute T-SQL commands in. You can of course use SQL Server Management Studio (SSMS), or alternatively, you can use SQL Server Data Tools (SSDT) inside of Visual Studio (SSDT is installed by default with VS 2012 and VS 2013, but needs to be installed separately for VS 2010).

Most of the remaining steps are standard procedure with any SQL CLR implementation. However, some of them are required specifically because we’re calling native C++ code from SQL CLR.

Enable SQL CLR. This, of course, is required to support any custom SQL CLR implementation in SQL Server.
```
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
GO
```

Create the database.

CREATE DATABASE MathLibDb
GO
USE MathLibDb
GO

Set the database’s TRUSTWORTHY property. This is normally not required for SQL CLR, but is needed here so that “unsafe” assemblies (that is, those that call into native C++ code) can be created in the database.
```
ALTER DATABASE MathLibDb SET TRUSTWORTHY ON
GO
```

Create the assembly. Because this assembly calls into native C++ code, you must also specify PERMISSION_SET = UNSAFE.

CREATE ASSEMBLY [MathLibNativeSQLCLR]
  AUTHORIZATION dbo
  FROM 'C:\Demo\MathLibFromSQL\MathLibNativeSQLCLR\bin\x64\Debug\MathLibNativeSQLCLR.dll'
  WITH PERMISSION_SET = UNSAFE
GO

Create the T-SQL UDF.

CREATE FUNCTION AddIntegersUdf(@A int, @B int)
  RETURNS int
  AS EXTERNAL NAME [MathLibNativeSQLCLR].[MathLibNativeSQLCLR.MathFunctions].[AddIntegersUdf]
GO

After all this effort, it’s quite rewarding to see the result. To watch the magic happen, invoke the T-SQL UDF just like you would any other, passing in any two numbers to be added. For example:

SELECT dbo.AddIntegersUdf(47, 16)

When you see the query return the sum of the two numbers passed in to the UDF, you know that everything is working correctly:

The T-SQL code calls the SQL CLR UDF, which calls the native C++ DLL that performs the work and returns the result all the way back up to SQL Server. So yes, it takes some effort, but it does work. Remember though, make sure your C++ code is well-behaved or you risk crashing SQL Server!

Posted in SQL Server, SQLCLR. Tags: Calling native C++ from SQL CLR, Calling native C++ from SQL Server, Calling unmanaged C++ from SQL CLR, Calling unmanaged C++ from SQL Server, SQL Server, SQLCLR. Leave a Comment »

SQL Server 2008 FILESTREAM Part 3 of 3: Using the OpenSqlFilestream API

January 23, 2011 — Leonard Lobel

This is the final installment in a 3-post series covering the new FILESTREAM feature in SQL Server 2008. In part 1, I explained how FILESTREAM works at a high level, and part 2 showed you the step-by-step procedure for enabling and using this awesome new feature. In this post, I’ll show you how to use the OpenSqlFilestream function exposed by the SQL Server Native Client API to achieve the maximum FILESTREAM performance possible in your .NET applications.

IMPORTANT UPDATE: I’ve just blogged on SqlFileStream, a managed code wrapper around OpenSqlFilestream that offers a simpler implementation of the mechanism described in this post. Read it here: https://lennilobel.wordpress.com/2011/08/22/using-sqlfilestream-in-c-to-access-sql-server-filestream-data/

What Is OpenSqlFilestream?

OpenSqlFilestream is a function provided by the SQL Server Native Client API, sqlncli10.dll, and gets installed on your development machine when you install the SQL Server 2008 client tools. This function can be called at the point in time that you want to store and retrieve BLOBs from varbinary(max) FILESTREAM columns, where SQL Server will “step aside,” and allow you to call OpenSqlFilestream to obtain a file handle. With the file handle returned by OpenSqlFilestream, you can stream directly against the file system—a native environment optimized for streaming. Contrast such direct file system access with storing and retrieving BLOBs against varbinary(max) FILESTREAM columns the “old-fashioned” way (either by embedding/extracting byte arrays, or in-lining binary streams as hexadecimal values in T-SQL as I demonstrated in part 2), which carries the additional overhead of the FILESTREAM abstraction layer. Using OpenSqlFilestream instead will give you lightning-fast BLOB performance. Let’s dive in!

Creating the Database

Before getting started, be sure that FILESTREAM is enabled for remote file system access at both the Windows Service and SQL Server instance levels (as explained in part 2). Then create a FILESTREAM-enabled database as follows (be sure to create the directory, C:\DB in this example, before creating the database):

CREATE DATABASE PhotoLibrary
 ON PRIMARY
  (NAME = PhotoLibrary_data,
   FILENAME = 'C:\DB\PhotoLibrary_data.mdf'),
 FILEGROUP FileStreamGroup CONTAINS FILESTREAM
  (NAME = PhotoLibrary_blobs,
   FILENAME = 'C:\DB\Photos')
 LOG ON
  (NAME = PhotoLibrary_log,
   FILENAME = 'C:\DB\PhotoLibrary_log.ldf')

Next, use the database and create a table for BLOB storage as follows:

USE PhotoLibrary
GO

CREATE TABLE PhotoAlbum(
 PhotoId int PRIMARY KEY,
 RowId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
 Description varchar(max),
 Photo varbinary(max) FILESTREAM DEFAULT(0x))

In this table, the Photo column is declared as varbinary(max) FILESTREAM, and will hold pictures that will be stored in the file system behind the scenes. This is virtually the same CREATE TABLE statement as shown in my last post (refer to part 2 for a complete explanation of the varbinary(max) FILESTREAM and ROWGUIDCOL columns). The only thing different here is the default value for the BLOB column. The value 0x represents a zero-length binary stream, which is different than NULL. Think of it as the difference between a zero-length string and a null string in .NET; the two are not the same. Similarly, you won’t be able to use OpenSqlFilestream against NULL instances of varbinary(max) FILESTREAM columns, and you’ll soon see why.

Storing BLOBs Using OpenSqlFilestream

I’ll walk you through the complete steps for building a C# data access class in Visual Studio that demonstrates how to use OpenSqlFilestream. In order to keep things simple for demonstration purposes, we won’t be following best practices; namely, we’ll be executing direct T-SQL statements instead of using stored procedures (as you should be using in production code).

Start Visual Studio and create a new Class Library project named PhotoLibraryDAL with a single class file named PhotoData.cs, and add the following public static method to the class named InsertPhoto:

public static void InsertPhoto(int photoId, string desc, string filename)
{
  const string InsertCmd =
   "INSERT INTO PhotoAlbum(PhotoId, Description)" +
   " VALUES(@PhotoId, @Description)";

  using(SqlConnection conn = new SqlConnection(ConnStr))
  {
    conn.Open();

    using(SqlTransaction txn = conn.BeginTransaction())
    {
      using(SqlCommand cmd = new SqlCommand(InsertCmd, conn, txn))
      {
        cmd.Parameters.Add(&quot;@PhotoId&quot;, SqlDbType.Int).Value = photoId;
        cmd.Parameters.Add(&quot;@Description&quot;, SqlDbType.VarChar).Value = desc;
        cmd.ExecuteNonQuery();
      }

      SavePhotoFile(photoId, filename, txn);
      txn.Commit();
    }

    conn.Close();
  }
}

Client applications call the InsertPhoto method to insert a new photo into the PhotoAlbum table by passing in a photo ID, description, and filename pointing to a local file containing the actual photo image. It then opens a connection, begins a transaction, and executes an INSERT statement. Notice that the INSERT statement supplies values only for the PhotoId and Description columns. What about the RowId and Photo columns? Because we’ve omitted them, the defaults we established in the table definition are applied. The RowId column gets assigned the next available GUID by the GETSEQUENTIALID function. And for the PhotoId BLOB column, the default 0x (zero-length binary stream) applies.

The result of supplying a zero-length binary stream value for the Photo column is that SQL Server creates an empty file in the file system that is linked to the Photo column of the row just inserted. However, because we’ve begun a transaction, SQL Server has automatically initiated an NTFS file system transaction over the empty file added to the file system. So now we have a new row inserted in the table—but not committed, and we have a new empty file created in the file system—but not committed. This only works because we specified (albeit by default) a zero-length binary stream (0x) rather than NULL when inserting the varbinary(max) FILESTREAM value. If an exception occurs, or if the database transaction rolls back, or if any other condition occurs in which the database transaction doesn’t commit successfully, the NTFS file system transaction will get rolled back automatically as well. In that case, both the inserted row in the table and the empty file in the file system go away.

Now is that “point in time” that we’d like SQL Server to “step aside” so we can call OpenSqlFilestream to store the BLOB for the photo image. To do that, we call the SavePhotoFile method, which is coded as follows:

private static void SavePhotoFile(int photoId, string filename, SqlTransaction txn)
{
  const int BlockSize = 1024 * 512;
  FileStream source = new FileStream(filename, FileMode.Open, FileAccess.Read);
  SafeFileHandle handle = GetOutputFileHandle(photoId, txn);
  using(FileStream dest = new FileStream(handle, FileAccess.Write))
  {
    byte[] buffer = new byte[BlockSize];
    int bytesRead;
    while((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0)
    {
      dest.Write(buffer, 0, bytesRead);
    }
    dest.Close();
  }
  source.Close();
}

There’s actually nothing magical about this method. You can see that it simply streams, in 512K chunks at a time, from a source stream to a destination stream. This is just the way you’d implement a simple, conventional, file-copy routine. Getting a handle on the source stream is also a no-brainer; since the source file is local, you simply invoke the System.IO.FileStream constructor that accepts a filename and enumerations specifying that the file should be opened for read access. So the question then becomes, how do we get a handle on the destination stream, which is a direct channel to the empty and not-yet-committed file on the server’s NTFS file system that SQL Server associates with the Photo column? The answer lies in the GetOutputFileHandle method which we call to obtain a SafeFileHandle object to the destination file:

private static SafeFileHandle GetOutputFileHandle(int photoId, SqlTransaction txn)
{
  const string GetOutputFileInfoCmd =
    "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT(), Photo.PathName()" +
    " FROM PhotoAlbum" +
    " WHERE PhotoId = @PhotoId";

  SqlCommand cmd = new SqlCommand(GetOutputFileInfoCmd, txn.Connection, txn);
  cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId;

  string filePath;
  byte[] txnToken;

  using(SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
  {
    rdr.Read();
    txnToken = rdr.GetSqlBinary(0).Value;
    filePath = rdr.GetSqlString(1).Value;
    rdr.Close();
  }

  SafeFileHandle handle =
    NativeSqlClient.GetSqlFilestreamHandle
    (filePath, NativeSqlClient.DesiredAccess.ReadWrite, txnToken);

  return handle;
}

This code is at the heart of the matter. In order to call OpenSqlFilestream, we need to obtain two key pieces of information. First, we need a logical path name to the destination file. We obtain that by selecting back the not-yet-committed row we just inserted, and invoking the PathName method on the Photo column. Nobody else can access this row because it hasn’t been committed yet (that would be a dirty read); it will never actually come into existence if the transaction rolls back. But we can read it because we’re inside the transaction, and so we can easily get the path name. No, the path name returned by PathName is not a UNC path to the physical file system on the server, so just lay that security concern to rest. It’s just a bogus path that has meaning only in the context of this transaction which SQL Server can use to map to the real file in the file system. Secondly, we need a token that identifies the NTFS file system transaction that SQL Server initiated behind the scenes, which we obtain with the GET_FILESTREAM_TRANSACTION_CONTEXT function.

Armed with these two key pieces of information, we can call OpenSqlFilestream. Because OpenSqlFilestream is a native code function, I’ve place it in a separate GetSqlFilestreamHandle method in a separate NativeSqlClient class to keep the DllImport and other native code details isolated from our .NET data access class (yes, I’m a neat freak when it comes to code, and you should be too):

using System;
using System.Runtime.InteropServices;

using Microsoft.Win32.SafeHandles;

namespace PhotoLibraryFilestreamDemo
{
  public class NativeSqlClient
  {
    public enum DesiredAccess : uint
    {
      Read,
      Write,
      ReadWrite,
    }

    [DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]
    private static extern SafeFileHandle OpenSqlFilestream(
      string path,
      uint access,
      uint options,
      byte[] txnToken,
      uint txnTokenLength,
      Sql64 allocationSize);

    [StructLayout(LayoutKind.Sequential)]
    private struct Sql64
    {
      public Int64 QuadPart;
      public Sql64(Int64 quadPart)
      {
        this.QuadPart = quadPart;
      }
    }

    public static SafeFileHandle GetSqlFilestreamHandle
     (string filePath, DesiredAccess access, byte[] txnToken)
    {
      SafeFileHandle handle = OpenSqlFilestream(
        filePath,
        (uint)access,
        0,
        txnToken,
        (uint)txnToken.Length,
        new Sql64(0));

      return handle;
    }
  }
}

As you can see, the GetSqlFilestreamHandle method accepts the transaction context token and the path obtained by the GET_FILESTREAM_TRANSACTION_CONTEXT function and PathName method respectively. It also accepts an enumeration that specifies the desired access mode, which can be Read, Write, or ReadWrite. The OpenSqlFilestream function requires other parameters that are not generally applicable for standard FILESTREAM usage, such as the unsigned 32-bit options and 64-bit allocation size arguments. These simply get passed in as 0. The SafeFileHandle returned by OpenSqlFilestream is defined by the .NET framework in the core library assembly mscorlib.dll, so no special reference needs to be set to access this class. Control then gets passed back up the call stack, to the SavePhotoFile method, which calls an overloaded version of the System.IO.Filestream constructor that accepts a SafeFileHandle object. After the source stream is copied entirely to the destination stream (overwriting the empty file on the server), control returns to the InsertPhoto method and the database transaction is finally committed. At that point, both the inserted row and the file in the file system are permanently saved, and the connection is closed. And that’s the way to stream BLOBs into varbinary(max) FILESTREAM columns in SQL Server 2008!

Retrieving BLOBs Using OpenSqlFilestream

Reading FILESTREAM data back out from the database to your application follows a very similar pattern. You open a connection and start a transaction. Note that this is normally not be considered best practice, as you should always try to have your read operations execute outside the context of a transaction. But to implement OpenSqlFilestream for read operations, this is exactly what you do. Then you use OpenSqlFilestream in the very same was we did for writing the BLOB to the database. The exact implementation depends on where you want to stream the content to.

For example, to stream a photo into an Image object for display in a Windows Forms PictureBox control, you can implement a SelectPhoto method like this:

public static Image SelectPhoto(int photoId, out string desc)
{
  const string SelectCmd =
    "SELECT Description, Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()" +
    " FROM PhotoAlbum" +
    " WHERE PhotoId = @PhotoId";

  Image photo;

  using(SqlConnection conn = new SqlConnection(ConnStr))
  {
    conn.Open();

    using(SqlTransaction txn = conn.BeginTransaction())
    {
      string filePath;
      byte[] txnToken;

      using(SqlCommand cmd = new SqlCommand(SelectCmd, conn, txn))
      {
        cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId;

        using(SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
        {
          rdr.Read();
          desc = rdr.GetSqlString(0).Value;
          filePath = rdr.GetSqlString(1).Value;
          txnToken = rdr.GetSqlBinary(2).Value;
          rdr.Close();
        }
      }

      photo = LoadPhotoImage(filePath, txnToken);

      txn.Commit();
    }

    conn.Close();
  }

  return photo;
}

private static Image LoadPhotoImage(string filePath, byte[] txnToken)
{
  Image photo;

  SafeFileHandle handle =
    NativeSqlClient.GetSqlFilestreamHandle
     (filePath, NativeSqlClient.DesiredAccess.Read, txnToken);

  using(FileStream fs = new FileStream(handle, FileAccess.Read))
  {
    photo = Image.FromStream(fs);
    fs.Close();
  }

  return photo;
}

Because the Image class has a static FromStream method that consumes the stream passed as a parameter to the method, this code essentially firehouses the stream at the fastest possible speed directly from the file system on SQL Server into the Image object.

Or, to stream a photo over HTTP from an ASP.NET service:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.Win32.SafeHandles;

namespace PhotoLibraryHttpService
{
 public partial class PhotoService : System.Web.UI.Page
 {
  private const string ConnStr =
    "Data Source=.;Integrated Security=True;Initial Catalog=PhotoLibrary;";

  protected void Page_Load(object sender, EventArgs e)
  {
   int photoId = Convert.ToInt32(Request.QueryString["photoId"]);
   if (photoId == 0)
   {
    return;
   }

   const string SelectCmd =
    "SELECT Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()" +
    " FROM PhotoAlbum" +
    " WHERE PhotoId = @PhotoId";

   using (SqlConnection conn = new SqlConnection(ConnStr))
   {
    conn.Open();

    using (SqlTransaction txn = conn.BeginTransaction())
    {
     string filePath;
     byte[] txnToken;

     using (SqlCommand cmd = new SqlCommand(SelectCmd, conn, txn))
     {
      cmd.Parameters.Add("@PhotoId", SqlDbType.Int).Value = photoId;

      using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
      {
       rdr.Read();
       filePath = rdr.GetSqlString(0).Value;
       txnToken = rdr.GetSqlBinary(1).Value;
       rdr.Close();
      }
     }

     this.StreamPhotoImage(filePath, txnToken);

     txn.Commit();
    }

    conn.Close();
   }
  }

  private void StreamPhotoImage(string filePath, byte[] txnToken)
  {
   const int BlockSize = 1024 * 512;
   const string JpegContentType = "image/jpeg";

   SafeFileHandle handle =
     NativeSqlClient.GetSqlFilestreamHandle
      (filePath, NativeSqlClient.DesiredAccess.Read, txnToken);

   using (FileStream source = new FileStream(handle, FileAccess.Read))
   {
    byte[] buffer = new byte[BlockSize];
    int bytesRead;
    Response.BufferOutput = false;
    Response.ContentType = JpegContentType;
    while ((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0)
    {
     Response.OutputStream.Write(buffer, 0, bytesRead);
     Response.Flush();
    }
    source.Close();
   }
  }
  }
}

Have Fun with FILESTREAM!

This concludes my 3-part series on using FILESTREAM in SQL Server 2008, which explains everything you need to know to get the most out of this truly awesome new feature. I hope you enjoyed it, and look forward to hearing your FILESTREAM success stories!

Posted in C#, SQL Server. Tags: FILESTREAM, SQL Server. 19 Comments »