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\u2019re already committed to an upgrade, you\u2019re giving yourself very little room to maneuver.<\/p>\n
This post is aimed at enterprise DBAs who manage real estates of SQL Server instances, not toy labs. We\u2019ll walk through what \u201cdeprecated\u201d 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.<\/p>\n
The goal isn\u2019t drama. The goal is to give you a concrete way to see what\u2019s coming before it bites.<\/p>\n
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\u2019s essentially on notice.<\/p>\n
That is not the same as a feature that is discontinued or removed. Once it\u2019s removed, it simply isn\u2019t there in the newer engine. If your code still depends on it and you upgrade past the last version that supported it, you\u2019ll hit errors, blocked upgrades, or broken workloads.<\/p>\n
There\u2019s 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\u2019s a different problem. Here we\u2019re focusing on the \u201cstill works today, but will go away\u201d bucket.<\/p>\n
Practically, when you see \u201cdeprecated\u201d in the documentation, it means: don\u2019t use it for new work, and assume you\u2019ll eventually need to migrate anything that already depends on it.<\/p>\n
SQL Server doesn\u2019t just rip features out randomly. There\u2019s a rough lifecycle.<\/p>\n
First, a feature is listed as deprecated in the official documentation. Each major version has a \u201cDeprecated features\u201d 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.<\/p>\n
At some point, some of those deprecated features move to the \u201cdiscontinued\u201d or \u201cremoved functionality\u201d list for a specific version. That\u2019s when things get serious. If you\u2019re targeting that version and still depend on something that\u2019s been removed, you\u2019ll feel it.<\/p>\n
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\u2019re targeting. Your job is to compare what your environment actually uses against those lists. Everything else\u2014blogs (including this one), forum posts, internal hearsay\u2014is just commentary.<\/p>\n
If everything is working today, it\u2019s natural to ask why you should spend time on this at all.<\/p>\n
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\u2019t complete. Sometimes it completes, but critical jobs or APIs start failing. Either way, you\u2019re now triaging under a change window instead of calmly planning ahead.<\/p>\n
The second reason is supportability. Once something is deprecated, it\u2019s not where engineering effort goes. Bugs in that area are more likely to be accepted as \u201cby design\u201d or left alone. You end up relying on behavior that nobody really wants you using long-term.<\/p>\n
The third reason is cost. Fixing deprecated usage as part of normal work\u2014when you\u2019re already touching a system for other reasons\u2014is usually cheap. Fixing it in a rush because an upgrade is blocked or because an auditor suddenly cares is not.<\/p>\n
You don\u2019t have to rip out every deprecated feature overnight. But you do need to know where they are and roughly how much work they represent.<\/p>\n
It would be nice if SQL Server had a single DMV that said \u201chere is every deprecated feature you\u2019re using, with exact code and line numbers.\u201d It doesn\u2019t.<\/p>\n
Instead, you get several different \u201cviews\u201d of the problem:<\/p>\n
Each one is incomplete on its own. Together, they\u2019re good enough to make decisions.<\/p>\n
A good starting point is the deprecated features performance object.<\/p>\n
SQL Server exposes a perfmon object called Here\u2019s a simple query to get a feel for what\u2019s in use:<\/p>\n On a production instance, you may see some surprisingly high values here. That\u2019s the first hint that certain deprecated features are quietly doing a lot of work.<\/p>\n 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 \u201cwe hit this once a week\u201d from \u201cthis is in the middle of a hot path.\u201d<\/p>\n The shortcomings are obvious: you don\u2019t 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.<\/p>\n Once you know something is using deprecated behavior, the next question is straightforward: what, exactly?<\/p>\n Extended Events gives you that level of detail.<\/p>\n SQL Server exposes deprecation events like Here\u2019s a simple server-level session that listens for both events and uses a ring buffer target:<\/p>\n You let that run during normal workload. Later, you read the ring buffer:<\/p>\n This view tells you exactly which statements triggered deprecation events, where they ran, and which application was behind them. That\u2019s something you can route to specific teams and turn into concrete work items.<\/p>\n Counters and Extended Events tell you what happened while you were watching. They can\u2019t see code paths that don\u2019t run during that window. For older systems, that can be a big blind spot.<\/p>\n To cover that, you inspect the metadata directly.<\/p>\n For deprecated data types, If you\u2019re interested in For code patterns, You\u2019ll need to review some of the hits by hand, but this is a very fast way to surface legacy patterns that haven\u2019t run for a while but would still break an upgrade.<\/p>\n On top of what you can do with DMVs and system catalogs, it\u2019s worth using Microsoft\u2019s assessment tooling.<\/p>\n 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.<\/p>\n They won\u2019t see everything. They\u2019re 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.<\/p>\n The way to think about them is as another input. They don\u2019t replace your own Extended Events sessions or schema scans; they supplement them.<\/p>\n Running all of these checks once before a major upgrade is better than doing nothing, but it\u2019s still reactive.<\/p>\n 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 You don\u2019t 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.<\/p>\n To make this practical, here\u2019s a full T-SQL script you can run in a non-production environment. It:<\/p>\n You can paste this into SSMS and run it as a single script, or walk through it step by step.<\/p>\n Everything up to this point has been about seeing what\u2019s going on. At some stage you have to decide what to do with it.<\/p>\n 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\u2019t have to fix every deprecated feature in one release cycle, but you do want a visible, realistic plan rather than a vague \u201cwe\u2019ll deal with it later.\u201d<\/p>\n 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.<\/p>\n If you wrap a bit of automation around those pieces and treat deprecation as an ongoing concern rather than a last-minute chore, you\u2019ll make your environment more predictable and your future upgrades much less painful.<\/p>\n","protected":false},"excerpt":{"rendered":" This post explains what deprecated features mean in SQL Server 2025 and how they impact long-term stability and upgrade planning. It outlines practical methods for detecting deprecated features using performance counters, Extended Events, static metadata queries, and assessment tooling. A complete T-SQL demo script is included so DBAs can reproduce the investigation in a lab. The content is written for enterprise SQL Server DBAs who need reliable, repeatable techniques to understand and mitigate deprecation risk. The focus is on real examples and operational clarity rather than promotion or hype.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[82,182,5,427],"tags":[624,659,655,658,656,585,620,657,344,660],"class_list":["post-1210","post","type-post","status-publish","format-standard","hentry","category-database-configuration","category-migration","category-performance","category-sql-server-2025","tag-database-administration-2","tag-database-upgrades","tag-deprecated-features","tag-dmvs","tag-extended-events-2","tag-sql-server-2","tag-sql-server-2025-2","tag-sql-server-performance-2","tag-t-sql","tag-technical-debt"],"yoast_head":"\nSQLServer:Deprecated Features<\/code>. You can look at the same information through sys.dm_os_performance_counters<\/code>. 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.<\/p>\nSELECT object_name,\n instance_name AS DeprecatedFeature,\n cntr_value AS UseCount\nFROM sys.dm_os_performance_counters\nWHERE object_name LIKE '%Deprecated Features%'\nORDER BY cntr_value DESC;<\/code><\/pre>\nExtended Events: seeing the actual queries<\/h2>\n
deprecation_announcement<\/code> and deprecation_final_support<\/code>. You can capture those events and attach useful context to each one: SQL text, database id, application name, session id, and so on.<\/p>\nUSE master;\nGO\n\nCREATE EVENT SESSION Demo_Deprecated_Features\nON SERVER\nADD EVENT sqlserver.deprecation_announcement\n(\n ACTION (sqlserver.sql_text,\n sqlserver.database_id,\n sqlserver.client_app_name,\n sqlserver.session_id)\n),\nADD EVENT sqlserver.deprecation_final_support\n(\n ACTION (sqlserver.sql_text,\n sqlserver.database_id,\n sqlserver.client_app_name,\n sqlserver.session_id)\n)\nADD TARGET package0.ring_buffer\nWITH\n(\n MAX_MEMORY = 4096 KB,\n EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\n MAX_DISPATCH_LATENCY = 5 SECONDS,\n TRACK_CAUSALITY = ON\n);\nGO\n\nALTER EVENT SESSION Demo_Deprecated_Features\nON SERVER\nSTATE = START;\nGO<\/code><\/pre>\nWITH EventData AS\n(\n SELECT CAST(t.target_data AS xml) AS TargetData\n FROM sys.dm_xe_sessions AS s\n JOIN sys.dm_xe_session_targets AS t\n ON s.address = t.event_session_address\n WHERE s.name = 'Demo_Deprecated_Features'\n AND t.target_name = 'ring_buffer'\n)\nSELECT TOP (50)\n n.value('@name', 'sysname') AS event_name,\n n.value('@timestamp', 'datetime2(3)') AS utc_time,\n DB_NAME(n.value('(action[@name=\"database_id\"]\/value)[1]', 'int')) AS database_name,\n n.value('(action[@name=\"client_app_name\"]\/value)[1]', 'nvarchar(256)') AS client_app_name,\n n.value('(action[@name=\"session_id\"]\/value)[1]', 'int') AS session_id,\n n.value('(action[@name=\"sql_text\"]\/value)[1]', 'nvarchar(max)') AS sql_text\nFROM EventData\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event') AS q(n)\nORDER BY utc_time DESC;<\/code><\/pre>\nStatic schema and code analysis<\/h2>\n
sys.columns<\/code> and sys.types<\/code> are all you need. This query will list all columns using TEXT<\/code>, NTEXT<\/code>, or IMAGE<\/code>:<\/p>\nSELECT\n t.name AS table_name,\n c.name AS column_name,\n ty.name AS data_type,\n c.max_length,\n c.is_nullable\nFROM sys.columns AS c\nJOIN sys.types AS ty ON c.user_type_id = ty.user_type_id\nJOIN sys.tables AS t ON c.object_id = t.object_id\nWHERE ty.name IN ('text','ntext','image')\nORDER BY t.name, c.column_id;<\/code><\/pre>\nTIMESTAMP<\/code> (the deprecated alias for ROWVERSION<\/code>), you can adjust the filter:<\/p>\nSELECT\n t.name AS table_name,\n c.name AS column_name,\n ty.name AS data_type\nFROM sys.columns AS c\nJOIN sys.types As ty ON c.user_type_id = ty.user_type_id\nJOIN sys.tables AS t ON c.object_id = t.object_id\nWHERE ty.name = 'timestamp'\nORDER BY t.name, c.column_id;<\/code><\/pre>\nsys.sql_modules<\/code> lets you search procedure and function definitions. A simple text search is enough to find calls to deprecated text operations like READTEXT<\/code>, TEXTPTR<\/code>, WRITETEXT<\/code>, and UPDATETEXT<\/code>:<\/p>\nSELECT\n OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,\n OBJECT_NAME(m.object_id) AS object_name,\n m.definition\nFROM sys.sql_modules AS m\nWHERE m.definition LIKE '%READTEXT%'\n OR m.definition LIKE '%TEXTPTR%'\n OR m.definition LIKE '%WRITETEXT%'\n OR m.definition LIKE '%UPDATETEXT%'\nORDER BY schema_name, object_name;<\/code><\/pre>\nWhere assessment and migration tools fit<\/h2>\n
Making this sustainable<\/h2>\n
TEXT<\/code> columns from appearing unnoticed.<\/p>\nA self-contained demo script you can run<\/h2>\n
\n
-- DEMO: Investigating Deprecated Features in SQL Server 2025\n\nUSE master;\nGO\n-- Drop XE session if it already exists\nIF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'Demo_Deprecated_Features')\nBEGIN\n DROP EVENT SESSION Demo_Deprecated_Features ON SERVER;\nEND;\nGO\n\n-- Drop demo database if it already exists\nIF DB_ID('DemoDeprecatedFeatures') IS NOT NULL\nBEGIN\n ALTER DATABASE DemoDeprecatedFeatures\n SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\n DROP DATABASE DemoDeprecatedFeatures;\nEND;\nGO\n-- 1. CREATE DEMO DATABASE & OBJECTS USING DEPRECATED FEATURES\nCREATE DATABASE DemoDeprecatedFeatures;\nGO\n\nUSE DemoDeprecatedFeatures;\nGO\n\n-- Table with deprecated TEXT and IMAGE types\nCREATE TABLE dbo.LegacyDocuments\n(\n DocumentId int IDENTITY(1,1) NOT NULL\n CONSTRAINT PK_LegacyDocuments PRIMARY KEY,\n Title nvarchar(200) NOT NULL,\n BodyText text NULL, -- deprecated\n Attachment image NULL -- deprecated\n);\nGO\n\n-- Table with deprecated TIMESTAMP data type\nCREATE TABLE dbo.LegacyAudit\n(\n AuditId int IDENTITY(1,1) NOT NULL\n CONSTRAINT PK_LegacyAudit PRIMARY KEY,\n AuditStamp timestamp NOT NULL, -- deprecated alias for rowversion\n Info nvarchar(200) NOT NULL\n);\nGO\n\n-- Seed some data\nINSERT dbo.LegacyDocuments (Title, BodyText)\nVALUES ('Doc 1', 'Hello from deprecated TEXT'),\n ('Doc 2', 'Another deprecated TEXT value');\nGO\n\nINSERT dbo.LegacyAudit (Info)\nVALUES ('Created demo row 1'),\n ('Created demo row 2');\nGO\n\n-- Stored procedure using TEXTPTR + READTEXT (both deprecated),\n-- but using actual length to avoid 7124 errors.\nCREATE OR ALTER PROCEDURE dbo.usp_LegacyReadText\nAS\nBEGIN\n SET NOCOUNT ON;\n\n DECLARE @ptr varbinary(16);\n DECLARE @len int;\n\n SELECT TOP (1)\n @ptr = TEXTPTR(BodyText),\n @len = DATALENGTH(BodyText)\n FROM dbo.LegacyDocuments\n WHERE BodyText IS NOT NULL\n ORDER BY DocumentId;\n\n IF @ptr IS NOT NULL AND @len IS NOT NULL\n BEGIN\n READTEXT dbo.LegacyDocuments.BodyText @ptr 0 @len;\n END\nEND;\nGO\n\n-- DEMO #1 \u2013 DMV \/ PERFORMANCE COUNTERS\n-- 2.1. Baseline \u2013 what deprecated features show up before we run anything?\nSELECT object_name,\n counter_name,\n instance_name AS DeprecatedFeature,\n cntr_value AS UseCount\nFROM sys.dm_os_performance_counters\nWHERE object_name LIKE '%Deprecated Features%'\n AND cntr_value > 0\nORDER BY cntr_value DESC;\nGO\n\n-- 2.2. Run some queries that use deprecated features\nUSE DemoDeprecatedFeatures;\nGO\n\n-- Use TEXT column in a SELECT \u2013 triggers text\/ntext\/image deprecation counters\nSELECT BodyText\nFROM dbo.LegacyDocuments\nWHERE DocumentId = 1;\nGO\n\n-- Insert into table with TIMESTAMP column \u2013 hits the deprecated TIMESTAMP type\nINSERT dbo.LegacyAudit (Info)\nVALUES ('Row that hits deprecated TIMESTAMP type');\nGO\n\n-- Call procedure using TEXTPTR + READTEXT \u2013 hits several deprecation paths\nEXEC dbo.usp_LegacyReadText;\nGO\n\n-- 2.3. Check the deprecated features counters again\nSELECT object_name,\n counter_name,\n instance_name AS DeprecatedFeature,\n cntr_value AS UseCount\nFROM sys.dm_os_performance_counters\nWHERE object_name LIKE '%Deprecated Features%'\n AND cntr_value > 0\nORDER BY counter_name, instance_name;\nGO\n\n-- 3. DEMO #2 \u2013 EXTENDED EVENTS FOR DEPRECATION\n-- 3.1. Create an Extended Events session with a ring_buffer target\nCREATE EVENT SESSION Demo_Deprecated_Features\nON SERVER\nADD EVENT sqlserver.deprecation_announcement\n(\n ACTION (sqlserver.sql_text,\n sqlserver.database_id,\n sqlserver.client_app_name,\n sqlserver.session_id)\n),\nADD EVENT sqlserver.deprecation_final_support\n(\n ACTION (sqlserver.sql_text,\n sqlserver.database_id,\n sqlserver.client_app_name,\n sqlserver.session_id)\n)\nADD TARGET package0.ring_buffer\nWITH\n(\n MAX_MEMORY = 4096 KB,\n EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\n MAX_DISPATCH_LATENCY = 5 SECONDS,\n TRACK_CAUSALITY = ON\n);\nGO\n\n-- 3.2. Start the session\nALTER EVENT SESSION Demo_Deprecated_Features\nON SERVER\nSTATE = START;\nGO\n\n-- 3.3. Run some deprecated-feature queries again to generate events\nUSE DemoDeprecatedFeatures;\nGO\n\nSELECT BodyText\nFROM dbo.LegacyDocuments\nWHERE DocumentId = 2;\nGO\n\nINSERT dbo.LegacyAudit (Info)\nVALUES ('Another row hitting TIMESTAMP');\nGO\n\nEXEC dbo.usp_LegacyReadText;\nGO\n\n-- 3.4. Read events from the ring_buffer target\nUSE master;\nGO\n\nWITH EventData AS\n(\n SELECT CAST(xet.target_data AS xml) AS TargetData\n FROM sys.dm_xe_sessions AS s\n JOIN sys.dm_xe_session_targets AS xet\n ON s.address = xet.event_session_address\n WHERE s.name = 'Demo_Deprecated_Features'\n AND xet.target_name = 'ring_buffer'\n)\nSELECT TOP (50)\n n.value('@name', 'sysname') AS event_name,\n n.value('@timestamp', 'datetime2(3)') AS utc_time,\n n.value('(action[@name=\"database_id\"]\/value)[1]', 'int') AS database_id,\n DB_NAME(n.value('(action[@name=\"database_id\"]\/value)[1]', 'int')) AS database_name,\n n.value('(action[@name=\"client_app_name\"]\/value)[1]', 'nvarchar(256)') AS client_app_name,\n n.value('(action[@name=\"session_id\"]\/value)[1]', 'int') AS session_id,\n n.value('(action[@name=\"sql_text\"]\/value)[1]', 'nvarchar(max)') AS sql_text\nFROM EventData\nCROSS APPLY TargetData.nodes('\/\/RingBufferTarget\/event') AS q(n)\nORDER BY utc_time DESC;\nGO\n-- 4. DEMO #3 \u2013 STATIC ANALYSIS INSIDE THE DATABASE\nUSE DemoDeprecatedFeatures;\nGO\n-- 4.1. Find deprecated data types in columns (TEXT, NTEXT, IMAGE)\nSELECT\n t.name AS table_name,\n c.name AS column_name,\n ty.name AS data_type,\n c.max_length,\n c.is_nullable\nFROM sys.columns AS c\nJOIN sys.types AS ty ON c.user_type_id = ty.user_type_id\nJOIN sys.tables AS t ON c.object_id = t.object_id\nWHERE ty.name IN ('text','ntext','image')\nORDER BY t.name, c.column_id;\nGO\n\n-- 4.2. Find columns using TIMESTAMP data type\nSELECT\n t.name AS table_name,\n c.name AS column_name,\n ty.name AS data_type\nFROM sys.columns AS c\nJOIN sys.types AS ty ON c.user_type_id = ty.user_type_id\nJOIN sys.tables AS t ON c.object_id = t.object_id\nWHERE ty.name = 'timestamp'\nORDER BY t.name, c.column_id;\nGO\n\n-- 4.3. Find deprecated syntax in module definitions\nSELECT\n OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,\n OBJECT_NAME(m.object_id) AS object_name,\n m.definition\nFROM sys.sql_modules AS m\nWHERE m.definition LIKE '%READTEXT%'\n OR m.definition LIKE '%TEXTPTR%'\n OR m.definition LIKE '%WRITETEXT%'\n OR m.definition LIKE '%UPDATETEXT%'\nORDER BY schema_name, object_name;\nGO<\/code><\/pre>\nFrom detection to action<\/h2>\n
Closing thoughts<\/h2>\n