SET TEXTSIZE statement in SQL Server

The SET TEXTSIZE command in SQL Server specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, ntext, or image data returned by a SELECT statement. This setting is session-specific, meaning it only affects the current connection and any subsequent SELECT statements within that session until it is changed again or the session ends. 

Syntex

SET TEXTSIZE { number }

The length of varchar(max), nvarchar(max), varbinary(max), text, ntext, or image data, in bytes. number is an integer with a maximum value of 2147483647 (2 GB). A value of -1 indicates unlimited size. A value of 0 resets the size to the default value of 4 KB.

For the demo we are creating a table and inserting few records into it.

use test_db  CREATE TABLE dbo.DemoText (     ID int IDENTITY,     Comments varchar(max) );  INSERT INTO dbo.DemoText(Comments) VALUES (REPLICATE('A', 5000));  -- 5 KB text INSERT INTO dbo.DemoText(Comments) VALUES (REPLICATE('A', 10000)); -- 10 KB text INSERT INTO dbo.DemoText(Comments) VALUES (REPLICATE('A', 15000)); -- 15 KB text INSERT INTO dbo.DemoText(Comments) VALUES (REPLICATE('A', 20000)); -- 20 KB text

See the data

set textsize 10; select * from dbo.DemoText  --Only first 10 bytes are returned. set textsize 100; select * from dbo.DemoText --Only first 100 bytes are returned. set textsize 0; select * from dbo.DemoText --Only first 4096 bytes are returned. set textsize -1; select * from dbo.DemoText --Only first 8000 bytes are returned.

 

 Alternative of TEXTSIZE is Left or Substring.