April 30, 2008 at 11:30 am
Can anyone give some difference between TEXT data type and VARCHAR data type?
For example if I want to create field for storing comment from user which one I should use?
Another question - Why I cannot use GROUP BY with TEXT ?
April 30, 2008 at 1:00 pm
You can look it up in more detail in the Books Online (the SQL Server documentation), but Text is a BLOB or Binary Large Object data type. It stores data, generally, not in the table, but rather in a space out on the file system. As such, it behaves totally differently than the other string data types CHAR, VARCHAR, NVARCHAR, NCHAR. That includes not being able to run aggregates against it. The other data types, with a notable exception, store their data directly within the table and are accessible through all the standard TSQL commands. The exception is when you create a VARCHAR or NVARCHAR field and set their length to MAX. Then it acts a lot like a Text field with any of the data that goes past a page in length.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2015 at 6:50 am
Hope this is useful for people hunting through old but well named topics.
This works
declare @var varchar(max) = ' '
select len(@var)
This doesn't
declare @var varchar(max) = ' '
select len(cast( @var as text))
Msg 8116, Level 16, State 1, Line 7
Argument data type text is invalid for argument 1 of len function.
This doesn't
declare @var varchar(max) = ' '
select left(cast( @var as text) ,2)
So it seems like the trend is that you cannot do any string manipulation with text fields.
Also, this
declare @var text = ' '
Msg 2739, Level 16, State 1, Line 2
The text, ntext, and image data types are invalid for local variables.
Even more reason to get rid of TEXT, NTEXT etc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply