Post reply

changing varchar datatype to text

  • February 23, 2006 at 2:38 pm

    #111702

    I have 10 varchar filelds in my sql server.all the fileds have varchar size 600. Now I want to allow long strings which will be around 2000 characters into each varchar field. SO can I change the data type to text. Does it affect the existing data in the database.

     

    Thanks.

  • February 23, 2006 at 2:39 pm

    #623070

    I would prefer to know more about the table strucutre and what each field is for before I give an answer.

  • February 23, 2006 at 2:42 pm

    #623072

    they all store notes from the contact.

  • February 23, 2006 at 2:46 pm

    #623073

    Its a contact table and we will store contact info and the contact's email content will be stored in thsose 10 columns as email1,email2...email10.

     

    Thanks.

  • February 24, 2006 at 6:27 am

    #623170

    A better design might start with something like this:

    CREATE TABLE Contact

    (

      cid int IDENTITY(1,1) PRIMARY KEY

    -- some contact info, whatever you are collecting

    , clastname varchar(30)

    , cfirstname varchar(30)

    , cphone varchar(10)

    , emailaddress varchar(50)

    )

    CREATE TABLE ContactEmail

    (

      ceid int IDENTITY(1,1) PRIMARY KEY

    , cid int   -- foreign key related to Contact table

    , emailnumber tinyint  -- could constrain this to number 1..10

    , email varchar(2000)

    )

     

  • February 24, 2006 at 6:57 am

    #623182

    Varchar can be as large as 8000 characters, so just changing them to 2000 should not be a problem, depending on how much data you actually will store in the table.  You are limited to storing 8060 bytes in a row.  (Text fields do not count to that row size total, actually, only count 4 bytes for each text field.)  If you are increasing the size for the rare occurence of a large email address, but will only have < 200 bytes in the other fields, you may get away with it.

    Recognize that making the 10 fields all text datatypes may cause some issues.  Text data is stored in a separate area away from the row.  Queries that will get multiple text fields will have to combine these fields from different areas of the db, and may cause a performance issue. I also seem to remember that if you are using ADO, there are some issues with having more than one text field in a recordset.

    I (and most dbas) agree with mkeast, and you should normalize the email address field.  You will not have any size problems with that design.

    Hope this helps



    Mark

  • February 24, 2006 at 1:13 pm

    #623316

    I am not clear but in each of the 10 2000 character fields, do you intend on string a comma delimited list of emails?

    This should be further normalized into a table containing 1 row for each email address

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply