But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
Can anybody tell me why?
Thanks in advance!
Don't put the VARCHAR keyword in square brackets. It's killing my feng shui.
Anyhow, according to this article, VARCHAR(MAX) is different from TEXT in that string manipulation functions work on it whereas they don't work on TEXT. It used to be that if you wanted to do string manipulation on text that it had to be less than 8000 characters long. Now you can do it thanks to the MAX datatypes. Since they're still VARCHAR values, all the functions will work on them just fine.
Most importantly, to the marketing department, they have these new MAX datatypes that sound cool. We're lucky it's not VARCHAR(EXTREME) or something like that.
If it weren't for marketing they probably would have just extended the string ops to TEXT and done the equivalent for the binary types. In fact, this is precisely what they did, and the MAX datatypes are just aliases to BLOB and CLOB types. So the reason VARCHAR(MAX) shows up as TEXT is because it *is* TEXT.