• 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