Please Don’t Use Deprecated Data Types

I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL 2005 (see http://msdn.microsoft.com/en-US/library/ms143729(v=SQL.90).aspx under Textpointers). Why are you persisting these things six years later?

I bring this up because I’ve come across further egregious usage of these data types in vendor code yet again. The vendor in question? Microsoft.

Yes, that’s right, the folks that deprecated the data type six years ago is still using it to a large extent within the ReportServer and ReportServerTempDB databases that support SQL Server Reporting Services. Seriously Microsoft? Can you please get with the plan and fix this nonsense?

The following query, run against the ReportServer database will show 14 different tables (31 columns) using a variety of NTEXT and IMAGE data types.

select st.name as TableName, t.name as DataType, sc.name as ColumnName 
    from sys.types t 
        inner join sys.columns sc
            on t.system_type_id = sc.system_type_id
        inner join sys.tables st
            on sc.object_id = st.object_id
where 
    t.name in ('image', 'text', 'ntext')
order by 
    st.name, t.name

select st.name as TableName, t.name as DataType, sc.name as ColumnName

from sys.types t

inner join sys.columns sc

on t.system_type_id = sc.system_type_id

inner join sys.tables st

on sc.object_id = st.object_id

where

t.name in ('image', 'text', 'ntext')

order by

st.name, t.name

Continue reading on SirSQL.net.

Please Don’t Use Deprecated Data Types

Related

Best Practices Q & A

Robert L Davis

Jason Brimhall

Argenis Fernandez

MinionWare, LLC

Kenneth Fisher

David Klee

Ed Watson

Nic Cain

Chrissy LeMair