[dba-SQLServer] Convert from ntext to varchar

Chris,

Thanks for the quick response. I don't think I've misunderstood the nature of varchar vs. char types. I *AM* confused by the necessity of giving a "length" for a varchar or ntext field. Here's why.

I restructured my table and changed the NewKeyStatsComment field to varchar 8000. I went to my web host's nifty SQL Server status page and saw the size of the table before I made the restructure and after.

I then changed the sproc so that the CAST statement used varchar(8000). That did the trick as far as copying the entire ntext comment column to the NewKeyStatsComment varchar column. Then I went back to the SQL Server status page and saw that the table's data had only increased in size by 56 KB.

The confusing part for me is that an ntext field, which has a variable-length up to a max of about a billion characters, requires a number for the LENGTH...but you can stuff as much as you want into the field without worry. However, if you try to transfer the data from an ntext(16) field to a varchar(16) field, the varchar field winds up with only 16 characters. Why is there a requirement for a LENGTH in the data definition for a varchar field or ntext field at all?

By the way, after I changed the structure to varchar(8000), I tried the sproc using just "varchar" in the CAST function. The NewKeyStatsComment field then wound up with 30 characters from the old comment field?! What's up with that? Then I changed the sproc to "varchar(8000)" and got the full comment.

Thanks for your help. I'm going to leave the length of the varchar field at 8000.

Steve Erbach
Neenah, WI

> ------------Original Message------------
> From: "Mackin, Christopher" <CMackin at quiznos.com>
> To: dba-sqlserver at databaseadvisors.com
> Date: Thu, Jan-6-2005 1:23 PM
> Subject: RE: [dba-SQLServer] Convert from ntext to varchar
> 
> The real determining factor is the size of the field where it's being 
> stored.  I would run something like:
> 
> SELECT Max(Len(CAST(KeyStatsComment AS VarChar(8000)))) FROM ......
> 
> That will give you the Max length of the existing data, then from there 
> you should be able to determine how many characters you need in the 
> field in the table.
> 
> I think you may be confusing VarChar and Char, VarChar is variable 
> length and stores only what it has, Char is fixed length and stores the 
> full amount of characters.
> 
> -Chris Mackin
>