en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
ספט8

Written by: ronen ariely
08/09/2013 08:42 RssIcon

Introduction

Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Before SQL Server 2005 we used ntext , text, and image data types.

As of SQL server 2005 Microsoft announced: "ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead."

At this time SQL Server 2012 still support those types, But the SSMS GUI does not fully support it!

Let's try this:

use tempdb
GO
 
create table QQ01
(
MyText NTEXT
)
GO
 
insert QQ01 values
(REPLICATE('A', 100)),
(REPLICATE('A', 3999)),
(REPLICATE('A', 4000)),
(REPLICATE('A', 8000)),
(REPLICATE('A', 10000))
GO
 
select * from QQ01
GO


Open the table data for editing using the SSMS GUI

Editing the first row show no problem but trying to edit larger data like the last row will rise an Error:

Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated.
The statement has been terminated

* This happens only when the NTEXT column in that row contains large data in it.

"SET TEXTSIZE" Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

select @@TEXTSIZE
The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set TEXTSIZE to 2147483647 when connecting.

the maximum setting for SET TEXTSIZE is 2 gigabytes (GB), specified in bytes. A setting of 0 resets the size to the default (4 KB).

SET TEXTSIZE 10
declare @t as varchar(max) = 'fsdafsfsdffsfsfsfsfdsfafsdfsdfsdfsdfassf1'
select @t

-- varbinary(8000) is limited by 8000 bytes - that's for sure!
-- varbinary(max) is limited by 2 gigabytes
-- varbinary(max) FILESTREAM is limited by your file system (FAT32 - 2 Gb, NTFS - 16 exabytes)


Workaround

We can use a simple query to edit the data.

References

ntext, text, and image (Transact-SQL) – sql 2012
http://technet.microsoft.com/en-us/library/ms187993.aspx


ntext, text, and image (Transact-SQL) – sql 2005
http://technet.microsoft.com/en-us/library/ms187993(v=sql.90).aspx