Introduction

Deprecated features are not the most exciting part of SQL Server 2025, but they quietly decide how painful your next upgrade is going to be. You can absolutely run SQL Server 2025 with deprecated features in use and everything will seem fine. The catch is timing: those features are on a path to removal. If you only discover your dependencies when you’re already committed to an upgrade, you’re giving yourself very little room to maneuver.

This post is aimed at enterprise DBAs who manage real estates of SQL Server instances, not toy labs. We’ll walk through what “deprecated” actually means, why it matters in practice, how to discover deprecated usage using several complementary techniques, and then finish with a self-contained T-SQL demo script you can run in a lab.

The goal isn’t drama. The goal is to give you a concrete way to see what’s coming before it bites.

What “deprecated” actually means in SQL Server

In SQL Server, a deprecated feature is still present and still works, but Microsoft has officially tagged it for removal in a future version. It’s essentially on notice.

That is not the same as a feature that is discontinued or removed. Once it’s removed, it simply isn’t there in the newer engine. If your code still depends on it and you upgrade past the last version that supported it, you’ll hit errors, blocked upgrades, or broken workloads.

There’s a third category that gets mixed in: breaking or behavior changes. In those cases the feature is still present, but the way it behaves has changed between versions. That deserves attention too, but it’s a different problem. Here we’re focusing on the “still works today, but will go away” bucket.

Practically, when you see “deprecated” in the documentation, it means: don’t use it for new work, and assume you’ll eventually need to migrate anything that already depends on it.

How SQL Server handles deprecation

SQL Server doesn’t just rip features out randomly. There’s a rough lifecycle.

First, a feature is listed as deprecated in the official documentation. Each major version has a “Deprecated features” page that calls out anything on the way out. That deprecated status often persists for several versions, which is how these features end up embedded in long-lived applications.

At some point, some of those deprecated features move to the “discontinued” or “removed functionality” list for a specific version. That’s when things get serious. If you’re targeting that version and still depend on something that’s been removed, you’ll feel it.

For you as a DBA, the key is simple: your source of truth is the official docs for the versions you run and the versions you’re targeting. Your job is to compare what your environment actually uses against those lists. Everything else—blogs (including this one), forum posts, internal hearsay—is just commentary.

Why deprecated features matter in enterprise environments

If everything is working today, it’s natural to ask why you should spend time on this at all.

The first reason is upgrade risk. When a deprecated feature finally disappears, anything depending on it becomes an upgrade problem. Sometimes that means the upgrade doesn’t complete. Sometimes it completes, but critical jobs or APIs start failing. Either way, you’re now triaging under a change window instead of calmly planning ahead.

The second reason is supportability. Once something is deprecated, it’s not where engineering effort goes. Bugs in that area are more likely to be accepted as “by design” or left alone. You end up relying on behavior that nobody really wants you using long-term.

The third reason is cost. Fixing deprecated usage as part of normal work—when you’re already touching a system for other reasons—is usually cheap. Fixing it in a rush because an upgrade is blocked or because an auditor suddenly cares is not.

You don’t have to rip out every deprecated feature overnight. But you do need to know where they are and roughly how much work they represent.

There is no magic DMV

It would be nice if SQL Server had a single DMV that said “here is every deprecated feature you’re using, with exact code and line numbers.” It doesn’t.

Instead, you get several different “views” of the problem:

• Instance-level counters that track usage of some deprecated features
• Extended Events that fire whenever deprecated features are used
• Static views of schema and code via system catalogs
• Assessment and migration tools that apply rule sets across databases

Each one is incomplete on its own. Together, they’re good enough to make decisions.

Using the SQL Server Deprecated features performance counters

A good starting point is the deprecated features performance object.

SQL Server exposes a perfmon object called SQLServer:Deprecated Features. You can look at the same information through sys.dm_os_performance_counters. Each row is a specific deprecated feature that Microsoft chose to wire up. The counter value tells you how many times that feature has been used since the instance last started.

Here’s a simple query to get a feel for what’s in use:

SELECT  object_name,
        instance_name AS DeprecatedFeature,
        cntr_value    AS UseCount
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated Features%'
ORDER BY cntr_value DESC;

On a production instance, you may see some surprisingly high values here. That’s the first hint that certain deprecated features are quietly doing a lot of work.

If you schedule this query and log the output into a table every few minutes or every hour, you can build a history of usage. That smooths over restarts and helps you distinguish “we hit this once a week” from “this is in the middle of a hot path.”

The shortcomings are obvious: you don’t see which database or query is responsible, and not every deprecated feature is tracked. Think of this as a high-level indicator rather than a full inventory.

Extended Events: seeing the actual queries

Once you know something is using deprecated behavior, the next question is straightforward: what, exactly?

Extended Events gives you that level of detail.

SQL Server exposes deprecation events like deprecation_announcement and deprecation_final_support. You can capture those events and attach useful context to each one: SQL text, database id, application name, session id, and so on.

Here’s a simple server-level session that listens for both events and uses a ring buffer target:

USE master;
GO

CREATE EVENT SESSION Demo_Deprecated_Features
ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (sqlserver.sql_text,
            sqlserver.database_id,
            sqlserver.client_app_name,
            sqlserver.session_id)
),
ADD EVENT sqlserver.deprecation_final_support
(
    ACTION (sqlserver.sql_text,
            sqlserver.database_id,
            sqlserver.client_app_name,
            sqlserver.session_id)
)
ADD TARGET package0.ring_buffer
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = ON
);
GO

ALTER EVENT SESSION Demo_Deprecated_Features
ON SERVER
STATE = START;
GO

You let that run during normal workload. Later, you read the ring buffer:

WITH EventData AS
(
    SELECT CAST(t.target_data AS xml) AS TargetData
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS t
        ON s.address = t.event_session_address
    WHERE s.name = 'Demo_Deprecated_Features'
      AND t.target_name = 'ring_buffer'
)
SELECT TOP (50)
       n.value('@name', 'sysname') AS event_name,
       n.value('@timestamp', 'datetime2(3)') AS utc_time,
       DB_NAME(n.value('(action[@name="database_id"]/value)[1]', 'int')) AS database_name,
       n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name,
       n.value('(action[@name="session_id"]/value)[1]', 'int') AS session_id,
       n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM EventData
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS q(n)
ORDER BY utc_time DESC;

This view tells you exactly which statements triggered deprecation events, where they ran, and which application was behind them. That’s something you can route to specific teams and turn into concrete work items.

Static schema and code analysis

Counters and Extended Events tell you what happened while you were watching. They can’t see code paths that don’t run during that window. For older systems, that can be a big blind spot.

To cover that, you inspect the metadata directly.

For deprecated data types, sys.columns and sys.types are all you need. This query will list all columns using TEXT, NTEXT, or IMAGE:

SELECT
    t.name      AS table_name,
    c.name      AS column_name,
    ty.name     AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.columns AS c
JOIN sys.types   AS ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables  AS t  ON c.object_id = t.object_id
WHERE ty.name IN ('text','ntext','image')
ORDER BY t.name, c.column_id;

If you’re interested in TIMESTAMP (the deprecated alias for ROWVERSION), you can adjust the filter:

SELECT
    t.name      AS table_name,
    c.name      AS column_name,
    ty.name     AS data_type
FROM sys.columns AS c
JOIN sys.types   As ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables  AS t  ON c.object_id = t.object_id
WHERE ty.name = 'timestamp'
ORDER BY t.name, c.column_id;

For code patterns, sys.sql_modules lets you search procedure and function definitions. A simple text search is enough to find calls to deprecated text operations like READTEXT, TEXTPTR, WRITETEXT, and UPDATETEXT:

SELECT
    OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
    OBJECT_NAME(m.object_id)        AS object_name,
    m.definition
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%READTEXT%'
   OR m.definition LIKE '%TEXTPTR%'
   OR m.definition LIKE '%WRITETEXT%'
   OR m.definition LIKE '%UPDATETEXT%'
ORDER BY schema_name, object_name;

You’ll need to review some of the hits by hand, but this is a very fast way to surface legacy patterns that haven’t run for a while but would still break an upgrade.

Where assessment and migration tools fit

On top of what you can do with DMVs and system catalogs, it’s worth using Microsoft’s assessment tooling.

These tools connect to your instances, run rule sets against your databases (and optionally against captured workloads), and produce reports covering deprecated features, breaking changes, and compatibility issues for a chosen target.

They won’t see everything. They’re limited by the rules they ship with, and some of those rules are more focused on Azure migration than on on-prem engine differences. But they are good at breadth and at giving you something structured you can share with non-DBA stakeholders.

The way to think about them is as another input. They don’t replace your own Extended Events sessions or schema scans; they supplement them.

Making this sustainable

Running all of these checks once before a major upgrade is better than doing nothing, but it’s still reactive.

If you want to stop rediscovering the same deprecated patterns every few years, it helps to bake some of this into your normal routines. That might be a lightweight XE session for deprecation events that runs all the time, a scheduled job that logs deprecated feature counters into a history table, or some static checks incorporated into deployment pipelines to prevent new TEXT columns from appearing unnoticed.

You don’t need a massive framework. Even a couple of small, reliable scripts run on a schedule can catch issues early and make later upgrades much less dramatic.

A self-contained demo script you can run

To make this practical, here’s a full T-SQL script you can run in a non-production environment. It:

• Cleans up any previous demo database and Extended Events session
• Creates a demo database that uses deprecated types and text operations
• Exercises those features to generate deprecation usage
• Shows how to see that usage via DMVs, Extended Events, and static analysis
• Optionally cleans up at the end (cleanup section is commented out)

You can paste this into SSMS and run it as a single script, or walk through it step by step.

-- DEMO: Investigating Deprecated Features in SQL Server 2025

USE master;
GO
-- Drop XE session if it already exists
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'Demo_Deprecated_Features')
BEGIN
    DROP EVENT SESSION Demo_Deprecated_Features ON SERVER;
END;
GO

-- Drop demo database if it already exists
IF DB_ID('DemoDeprecatedFeatures') IS NOT NULL
BEGIN
    ALTER DATABASE DemoDeprecatedFeatures
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DemoDeprecatedFeatures;
END;
GO
--   1. CREATE DEMO DATABASE & OBJECTS USING DEPRECATED FEATURES
CREATE DATABASE DemoDeprecatedFeatures;
GO

USE DemoDeprecatedFeatures;
GO

-- Table with deprecated TEXT and IMAGE types
CREATE TABLE dbo.LegacyDocuments
(
    DocumentId  int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_LegacyDocuments PRIMARY KEY,
    Title       nvarchar(200)     NOT NULL,
    BodyText    text              NULL,   -- deprecated
    Attachment  image             NULL    -- deprecated
);
GO

-- Table with deprecated TIMESTAMP data type
CREATE TABLE dbo.LegacyAudit
(
    AuditId     int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_LegacyAudit PRIMARY KEY,
    AuditStamp  timestamp         NOT NULL, -- deprecated alias for rowversion
    Info        nvarchar(200)     NOT NULL
);
GO

-- Seed some data
INSERT dbo.LegacyDocuments (Title, BodyText)
VALUES ('Doc 1', 'Hello from deprecated TEXT'),
       ('Doc 2', 'Another deprecated TEXT value');
GO

INSERT dbo.LegacyAudit (Info)
VALUES ('Created demo row 1'),
       ('Created demo row 2');
GO

-- Stored procedure using TEXTPTR + READTEXT (both deprecated),
-- but using actual length to avoid 7124 errors.
CREATE OR ALTER PROCEDURE dbo.usp_LegacyReadText
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ptr varbinary(16);
    DECLARE @len int;

    SELECT TOP (1)
           @ptr = TEXTPTR(BodyText),
           @len = DATALENGTH(BodyText)
    FROM dbo.LegacyDocuments
    WHERE BodyText IS NOT NULL
    ORDER BY DocumentId;

    IF @ptr IS NOT NULL AND @len IS NOT NULL
    BEGIN
        READTEXT dbo.LegacyDocuments.BodyText @ptr 0 @len;
    END
END;
GO

-- DEMO #1 – DMV / PERFORMANCE COUNTERS
-- 2.1. Baseline – what deprecated features show up before we run anything?
SELECT  object_name,
        counter_name,
        instance_name AS DeprecatedFeature,
        cntr_value    AS UseCount
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated Features%'
  AND cntr_value > 0
ORDER BY cntr_value DESC;
GO

-- 2.2. Run some queries that use deprecated features
USE DemoDeprecatedFeatures;
GO

-- Use TEXT column in a SELECT – triggers text/ntext/image deprecation counters
SELECT BodyText
FROM dbo.LegacyDocuments
WHERE DocumentId = 1;
GO

-- Insert into table with TIMESTAMP column – hits the deprecated TIMESTAMP type
INSERT dbo.LegacyAudit (Info)
VALUES ('Row that hits deprecated TIMESTAMP type');
GO

-- Call procedure using TEXTPTR + READTEXT – hits several deprecation paths
EXEC dbo.usp_LegacyReadText;
GO

-- 2.3. Check the deprecated features counters again
SELECT  object_name,
        counter_name,
        instance_name AS DeprecatedFeature,
        cntr_value    AS UseCount
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Deprecated Features%'
  AND cntr_value > 0
ORDER BY counter_name, instance_name;
GO

--   3. DEMO #2 – EXTENDED EVENTS FOR DEPRECATION
-- 3.1. Create an Extended Events session with a ring_buffer target
CREATE EVENT SESSION Demo_Deprecated_Features
ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (sqlserver.sql_text,
            sqlserver.database_id,
            sqlserver.client_app_name,
            sqlserver.session_id)
),
ADD EVENT sqlserver.deprecation_final_support
(
    ACTION (sqlserver.sql_text,
            sqlserver.database_id,
            sqlserver.client_app_name,
            sqlserver.session_id)
)
ADD TARGET package0.ring_buffer
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = ON
);
GO

-- 3.2. Start the session
ALTER EVENT SESSION Demo_Deprecated_Features
ON SERVER
STATE = START;
GO

-- 3.3. Run some deprecated-feature queries again to generate events
USE DemoDeprecatedFeatures;
GO

SELECT BodyText
FROM dbo.LegacyDocuments
WHERE DocumentId = 2;
GO

INSERT dbo.LegacyAudit (Info)
VALUES ('Another row hitting TIMESTAMP');
GO

EXEC dbo.usp_LegacyReadText;
GO

-- 3.4. Read events from the ring_buffer target
USE master;
GO

WITH EventData AS
(
    SELECT CAST(xet.target_data AS xml) AS TargetData
    FROM sys.dm_xe_sessions AS s
    JOIN sys.dm_xe_session_targets AS xet
        ON s.address = xet.event_session_address
    WHERE s.name = 'Demo_Deprecated_Features'
      AND xet.target_name = 'ring_buffer'
)
SELECT TOP (50)
       n.value('@name', 'sysname')                                        AS event_name,
       n.value('@timestamp', 'datetime2(3)')                              AS utc_time,
       n.value('(action[@name="database_id"]/value)[1]', 'int')           AS database_id,
       DB_NAME(n.value('(action[@name="database_id"]/value)[1]', 'int'))  AS database_name,
       n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name,
       n.value('(action[@name="session_id"]/value)[1]', 'int')            AS session_id,
       n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')    AS sql_text
FROM EventData
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS q(n)
ORDER BY utc_time DESC;
GO
-- 4. DEMO #3 – STATIC ANALYSIS INSIDE THE DATABASE
USE DemoDeprecatedFeatures;
GO
-- 4.1. Find deprecated data types in columns (TEXT, NTEXT, IMAGE)
SELECT
    t.name      AS table_name,
    c.name      AS column_name,
    ty.name     AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.columns AS c
JOIN sys.types   AS ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables  AS t  ON c.object_id = t.object_id
WHERE ty.name IN ('text','ntext','image')
ORDER BY t.name, c.column_id;
GO

-- 4.2. Find columns using TIMESTAMP data type
SELECT
    t.name      AS table_name,
    c.name      AS column_name,
    ty.name     AS data_type
FROM sys.columns AS c
JOIN sys.types   AS ty ON c.user_type_id = ty.user_type_id
JOIN sys.tables  AS t  ON c.object_id = t.object_id
WHERE ty.name = 'timestamp'
ORDER BY t.name, c.column_id;
GO

-- 4.3. Find deprecated syntax in module definitions
SELECT
    OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
    OBJECT_NAME(m.object_id)        AS object_name,
    m.definition
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%READTEXT%'
   OR m.definition LIKE '%TEXTPTR%'
   OR m.definition LIKE '%WRITETEXT%'
   OR m.definition LIKE '%UPDATETEXT%'
ORDER BY schema_name, object_name;
GO

From detection to action

Everything up to this point has been about seeing what’s going on. At some stage you have to decide what to do with it.

The basic pattern is: pull findings into one place, group them by feature, database, and application, prioritize based on risk and impact, define standard replacements, and make sure the right teams own the changes. You don’t have to fix every deprecated feature in one release cycle, but you do want a visible, realistic plan rather than a vague “we’ll deal with it later.”

Closing thoughts

Deprecated features in SQL Server 2025 are early warning signs. They tell you which parts of your environment are running on behavior that has an end date. There is no single DMV that will solve this for you. But between performance counters, Extended Events, static analysis of schema and code, and the available assessment tools, you have enough visibility to make good decisions.

If you wrap a bit of automation around those pieces and treat deprecation as an ongoing concern rather than a last-minute chore, you’ll make your environment more predictable and your future upgrades much less painful.