TEXT vs VARCHAR

[sparkbyte]

Before I change a production table I want to be sure that the data remains intact. So I wrote a simple script to check that values of the fields.

Here is an Example of the data.

Declare @TestTable Table
	(
		ID Int IDENTITY,
		COMMENT nTEXT,
		Primary Key (ID)
	)
	

Insert Into @TestTable Values ('This is a test of the emergancy brocast service, if this were a real emergancy you would be running for the hills!!!')

Select 
	ID,
	DATALENGTH(COMMENT) As Text_Comment_Length,
	Len(Cast(COMMENT As nVarChar(Max))) As VarChar_Comment_Length
From @TestTable

OutPut
[tt]
ID Text_Comment_Length VarChar_Comment_Length
----------- ------------------- ----------------------
1 232 116
[/tt]


ID Text_Comment
----------- -----------------------------------------------------------------------------------------------------------------------
1 This is a test of the emergancy brocast service, if this were a real emergancy you would be running for the hills!!!

ID nVarChar_Comment
----------- -----------------------------------------------------------------------------------------------------------------------
1 This is a test of the emergancy brocast service, if this were a real emergancy you would be running for the hills!!!

As you can see the text of both is them same, but the lenght test differs.

WHY??


Thanks

John Fuhrman

 

[gmmastros]

DataLength returns the storage space required for the data where len returns the number of characters. Unicode data requires 2 bytes per character so the number for datalength is double.

Ex:

-- Unicode data
Select Len(N'Hello World'), DataLength(N'Hello World')

-- ASCII data
Select Len('Hello World'), DataLength('Hello World')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Gotcha, Thanks!!

Thanks

John Fuhrman