SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reason for dropping TEXT, IMAGE data types


Reason for dropping TEXT, IMAGE data types

Author
Message
karthik M
karthik M
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2957 Visits: 2584
All,

what is the reason for dropping TEXT & IMAGE data type from the latest version?

Memory issue ?
Performance issue?
Maintenance issue ?

karthik
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39732 Visits: 38563
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2127 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)

Group: General Forum Members
Points: 913 Visits: 599
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16406 Visits: 19554
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
Exploring Recursive CTEs by Example Dwain Camps
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)

Group: General Forum Members
Points: 913 Visits: 599
Text, Ntext, and Image data types are invalid for local variables.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16406 Visits: 19554
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
Exploring Recursive CTEs by Example Dwain Camps
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2127 Visits: 872
ChrisM@Work (8/12/2013)
[quote]Gosh, where did you read that?


Maybe in an error message?

DECLARE @a text

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)SSC Eights! (913 reputation)

Group: General Forum Members
Points: 913 Visits: 599
declare @image1 image
declare @text1 text
declare @ntext1 ntext
declare @varchar2 varchar
declare @nvarchar2 nvarchar
declare @varbinary2 varbinary
declare @varchar1 varchar(max)
declare @nvarchar1 nvarchar(max)
declare @varbinary1 varbinary(max)

First three variable declarations are definitely going to produce an error.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16406 Visits: 19554
T.Ashish (8/12/2013)
declare @image1 image
declare @text1 text
declare @ntext1 ntext
declare @varchar2 varchar
declare @nvarchar2 nvarchar
declare @varbinary2 varbinary
declare @varchar1 varchar(max)
declare @nvarchar1 nvarchar(max)
declare @varbinary1 varbinary(max)

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



Yes of course they will. But this won't:
declare @image1   varbinary(max)
declare @text1 varchar(max)
declare @ntext1 nvarchar(max)
declare @varchar2 varchar
declare @nvarchar2 nvarchar
declare @varbinary2 varbinary
declare @varchar1 varchar(max)
declare @nvarchar1 nvarchar(max)
declare @varbinary1 varbinary(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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search