Mackin, Christopher
CMackin at quiznos.com
Thu Jan 6 13:45:08 CST 2005
The Ntext or Text in SQL is similar to a Memo field in access and I don't really know exactly what the 16 indicates in the field, but for Char, VarChar, NChar and NVarChar the number after it is the length of data allowed in the field, same as a text field in Access/Jet. As for the 30 being allowed, I've seen similar results (with a different length passed on) when not specifying the length of a VarChar field, I'm not sure what sets the default and make it a point to always specify a length when dealing with those data types. -Chris Mackin -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steven W. Erbach Sent: Thursday, January 06, 2005 12:39 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [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 > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com