March 31, 2009 at 5:52 am
I have just converted a column in SQL 2008 and run an update statement on the column.
I have now noticed something confusing.
When i look at the table in Management Studio it shows as NVARCHAR(MAX)
when i run sp_columns its shows as ntext.
This is even true when you create a new column, where can i correctly view this columntype...
April 1, 2009 at 5:17 am
select OBJECT_NAME(c.object_id)as [objectname], c.name as [column], t.name as [Typename], c.max_length
from sys.columns as c
join sys.types as t ON (c.user_type_id = t.user_type_id)
looks alright in sys tables, i'll steer clear of that stored proc..
April 6, 2009 at 9:58 am
I think this becase ALTER TABEL.
Database storage for TEXT and VARCHAR(MAX) are different (for TEXT it is LOB and pointer in table structure and for VARCHAR(MAX) it is table structure). But when you alter the table column from TEXT to VARCHAR(MAX), SQL server will not move the data to table structure.
I read something about this in below article.
http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
Regards,
Nitin
April 7, 2009 at 4:38 am
Hi jac,
What you see as data type by calling the sys proc sp_columns in the type_name column is the backward compatible ODBC type of the related sql type.
Eralper
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply