DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Character length of Data type in SQL database table

  1. #1
    becky Guest

    Character length of Data type in SQL database table


    Hi, i need some help here.
    What is the length of the datatype text in the sql datatable?
    There is a default value 'length' in sql table, what is the units of measurement
    for the value shown? is it in Byte?
    for instance, the default value for datatype text is 16. Is is mean 16 bytes
    is the maximum value for the datatype text?

    HOpe somebody can give me some advice.
    Thank you.

    Becky

  2. #2
    Simon Sellick Guest

    Re: Character length of Data type in SQL database table


    Becky

    In SQL Server 6.0, a TEXT datatype can hold 2^31 - 1 characters. They aren't
    stored very efficiently: as soon as you store anything except NULL in a TEXT
    column, a number of 2K byte pages are allocated, and at least one 2K page
    stays allocated until the row is deleted, even if you update the column to
    NULL.

    If you convert them to CHAR or VARCHAR the limit is 255 bytes.

    Hope this helps.

    "becky" <boeykean@hotmail.com> wrote:
    >
    >Hi, i need some help here.
    >What is the length of the datatype text in the sql datatable?
    >There is a default value 'length' in sql table, what is the units of measurement
    >for the value shown? is it in Byte?
    >for instance, the default value for datatype text is 16. Is is mean 16 bytes
    >is the maximum value for the datatype text?
    >
    >HOpe somebody can give me some advice.
    >Thank you.
    >
    >Becky



  3. #3
    PP Guest

    Re: Character length of Data type in SQL database table


    Sql 7 can store upto a data page of text in a varchar (about 8KB) where a
    text is typically used for anything over 8KB. The following functions are
    needed to manipulate abstract data from a text field:

    DATALENGTH: length of the text in the field. LEN will only tell you how big
    the pointer to the text is (Always 16 bytes).

    UPDATETEXT, WRITETEXT, READTEXT: can be used in stored procedures to add,
    append and select the data into variables.

    TEXTPTR: needed so for manipulating the actual text rather than the point
    itself.

    PATINDEX: similar to the LIKE function


    The only thing about TEXT field is that it is often an unlogged process and
    will prevent the transaction log being backed up. You can add a WITH LOG
    to the necessary statements to overcome this problem but the transaction
    log then becomes very big very quickly.

    If you need to store upto 8KB of text then vertically partition the table
    with a 1:1 relationship between both parts and use a VARCHAR instead of a
    text field.


    Hope this helps.


    "Simon Sellick" <simon.sellick@tesco.net> wrote:
    >
    >Becky
    >
    >In SQL Server 6.0, a TEXT datatype can hold 2^31 - 1 characters. They aren't
    >stored very efficiently: as soon as you store anything except NULL in a

    TEXT
    >column, a number of 2K byte pages are allocated, and at least one 2K page
    >stays allocated until the row is deleted, even if you update the column

    to
    >NULL.
    >
    >If you convert them to CHAR or VARCHAR the limit is 255 bytes.
    >
    >Hope this helps.
    >
    >"becky" <boeykean@hotmail.com> wrote:
    >>
    >>Hi, i need some help here.
    >>What is the length of the datatype text in the sql datatable?
    >>There is a default value 'length' in sql table, what is the units of measurement
    >>for the value shown? is it in Byte?
    >>for instance, the default value for datatype text is 16. Is is mean 16

    bytes
    >>is the maximum value for the datatype text?
    >>
    >>HOpe somebody can give me some advice.
    >>Thank you.
    >>
    >>Becky

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center