Post reply

Regarding Datatypes

  • December 30, 2008 at 1:11 am

    #203386

    Dear all,

    I have beeen told by one of the senior that we can not create two text column on single table? I tried it created table with two text column and also inserted data in both the columns.

    Now my table is having two cloumns and one row

    in first column data is of 9200 bytes and in second column data is of 8600 bytes. total row size is 17800 and its working ok.

    1) now i have read that the max rowsize is 8060, then how i m getting 17800?

    2) is it really true that we cant create two text column on single table?

    3) and please tell me the size in bytes for text datatype. like varchar takes 1 byte for one char like that....

    thanks

    Mithun

  • December 30, 2008 at 1:19 am

    #918836

    In SQL Server 2005/2008 you should be using varchar/nvarchar(max) not TEXT/NTEXT.

    Basically you "have more than" 8060 bytes on the row because SQL Server does not store TEXT data in the row on the data page. SQL Server is storing a pointer. From BOL:

    Usually, text, ntext, or image strings are large, a maximum of 2GB, character or binary strings stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored.

    Important:

    ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 31, 2008 at 3:14 am

    #919520

    sir

    i got my first question answer but still my two questions pending

    2) is it really true that we cant create two text column on single table?

    3) and please tell me the size in bytes for text datatype. like varchar takes 1 byte for one char like that....

    thanks

  • December 31, 2008 at 3:28 am

    #919527

    mithun.gite (12/31/2008)

    sir

    i got my first question answer but still my two questions pending

    2) is it really true that we cant create two text column on single table?

    3) and please tell me the size in bytes for text datatype. like varchar takes 1 byte for one char like that....

    thanks

    ad 2) Obviously it is wrong. 🙂

    ad 3) 1 character = 1 byte. But the engine only stores a 16 bytes pointer to the text data within the data row. The actual text data is commonly stored somewhere else unless you explicitely state that you want to store it "in_row".

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 31, 2008 at 3:37 am

    #919528

    Dear Sir,

    thanks for the prompt reply,,,,

    I got all my answers thanks a lot and a very happy new year to u.

    thanks

    Mithun

Viewing 5 posts - 1 through 4 (of 4 total)

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