SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: multiple ntext?

  1. #1
    Pointiest Petitions carrotflowers's Avatar
    Join Date
    Jul 2003
    Location
    Suburbs of DC (MD)
    Posts
    595
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple ntext?

    Hi there,
    Am using Sql Server 2000. I'm having problems with a db which has 7 text fields, 5 are varchar and 2 are ntext. The problem, the data that needs to be stored in a record seems to be exceeding the row limit. If I changes all fields to (n?)text instead of varchar, would that be ok or is that bad, inefficient, will cause timeouts etc?

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your total byte size for field definitions is 8Kb in SQL Server.

    NText takes 16 bytes, so if you were to change a VARCHAR(2016) field to ntext you would gain 2000 bytes of field definitions.

    And of course, an ntext field can store up to 2GB of data, even though it only requires 16 bytes of space in the table definition.

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course, you will have major problems updating and retrieving ntext data, unless you are totally prepared for it

    You see, SQL server does not store the ntext data in the table. A pointer (16 bytes) is stored in the table, containing the byte location of the ntext data on the harddisk.

  4. #4
    Pointiest Petitions carrotflowers's Avatar
    Join Date
    Jul 2003
    Location
    Suburbs of DC (MD)
    Posts
    595
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I was just trying to get a general idea of whether it would be considered bad design to have many ntext fields in one table. I've been having some issues with the amount of data I need to store in a row and was considering storing all of it in multiple ntext fields, and decided to see whether this was known to be bad db design.

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this is a bad DB design.
    Consider this;

    part
    --------
    sku varchar(200)
    box_description varchar(500)
    short_description varchar(500)
    french_description varchar(500)
    english_description varchar(500)
    engineering_description varchar(500)
    marketing_description varchar(500)
    old_description varchar(500)
    exciting_description varchar(500)
    legal_description varchar(500)

    and so on. not good. now consider this:

    definition:
    texttypes
    ----------
    descriptionname varchar(200)

    Data:
    descriptionname
    ------
    legal
    french
    marketing
    technical


    definition:
    parts
    -------
    sku varchar(200)

    Data:
    sku
    ------
    12345678
    98765432

    definition:
    partdescription
    -------
    sku
    texttype varchar(200)
    description varchar(1000)


    Data:
    sku texttype description
    ------
    12345678 marketing "great product"
    12345678 technical "product meeting requirement dod18337"
    98765432 german "ganz ganz geiles gerät"


    It should be clear that the second way, using relational data, is normalized, robust, compact and clearer

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To make my point clearer, in my example I used varchar fields, but it could easily have been ntext fields.

    It is better to normalize the data than to repeat fields holding similar data in a table.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •