Reason for dropping TEXT, IMAGE data types

Question

Reason for dropping TEXT, IMAGE data types

karthik M

One Orange Chip

Points: 29139
More actions
July 16, 2013 at 3:18 am

#280527

All,
what is the reason for dropping TEXT & IMAGE data type from the latest version?
Memory issue ?
Performance issue?
Maintenance issue ?
karthik

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

karthik M (7/16/2013)
All,
what is the reason for dropping TEXT & IMAGE data type from the latest version?
Memory issue ?
Performance issue?
Maintenance issue ?

These data types have been deprecated since the introduction of SQL Server 2005 and the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

From where do you have information that SQL 2014 would not support text/ntext/image? They certainly work in CTP1, and there is no information in Books Online, saying that they will be removed.

And as much as I would love to see these ugly bastards go away, I don't think it will happen for a long time. While the types have been deprecated for a long time, Microsoft has not cleaned up their own act. For instance SQL Trace uses these types. And since SQL Trace itself is deprecated, they will not change SQL Trace, so as long SQL Trace is there, the old LOB types will be there.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 3

With large-value data types You can define variables that can store large amounts of data, up to 2^31-1 bytes of character, binary, and Unicode data. That was not possible using the text, ntext and image data types from earlier versions of SQL Server.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

T.Ashish (8/9/2013)
With large-value data types You can define variables that can store large amounts of data, up to 2^31-1 bytes of character, binary, and Unicode data. That was not possible using the text, ntext and image data types from earlier versions of SQL Server.

You what?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 5

Text, Ntext, and Image data types are invalid for local variables.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

T.Ashish (8/11/2013)
Text, Ntext, and Image data types are invalid for local variables.

Gosh, where did you read that?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

ChrisM@Work (8/12/2013)

Gosh, where did you read that?

Maybe in an error message?

DECLARE @a text

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 8

declare @image1image

declare @text1text

declare @ntext1ntext

declare @varchar2varchar

declare @nvarchar2nvarchar

declare @varbinary2varbinary

declare @varchar1varchar(max)

declare @nvarchar1nvarchar(max)

declare @varbinary1varbinary(max)

First three variable declarations are definitely going to produce an error.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

T.Ashish (8/12/2013)
declare @image1image
declare @text1text
declare @ntext1ntext
declare @varchar2varchar
declare @nvarchar2nvarchar
declare @varbinary2varbinary
declare @varchar1varchar(max)
declare @nvarchar1nvarchar(max)
declare @varbinary1varbinary(max)
First three variable declarations are definitely going to produce an error.

Yes of course they will. But this won't:

declare @image1varbinary(max)

declare @text1varchar(max)

declare @ntext1nvarchar(max)

declare @varchar2varchar

declare @nvarchar2nvarchar

declare @varbinary2varbinary

declare @varchar1varchar(max)

declare @nvarchar1nvarchar(max)

declare @varbinary1varbinary(max)

"Text, Ntext, and Image data types are invalid for local variables." is misleading:

DROP TABLE #TEST

CREATE TABLE #TEST (text1 text)

INSERT INTO #TEST (text1)

SELECT CAST(REPLICATE('A',8000) AS VARCHAR(MAX))

+ CAST(REPLICATE('B',8000) AS VARCHAR(MAX))

+ 'THE END'

SELECT SUBSTRING(CAST(text1 AS VARCHAR(MAX)),16001,7) FROM #TEST

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

ChrisM@Work (8/12/2013)

"Text, Ntext, and Image data types are invalid for local variables." is misleading:

In such case you should file a Connect bug on message 2739. Although I don't really understand what is misleading - you cannot declare local variables of this type, only parameter. (And such parameters are readonly.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]