Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Unanswered: ntext + nvarchar - will this improve performance?

    Hi all

    I have a table that contains an ntext column for storing values up to a couple of Mb in size.

    However, I estimate that 95% of the values stored in this ntext field will fit into an nvarchar(4000) field.

    Is it worth me having both fields in the table?

    i.e. For rows where the values < 4000 characters I would store the value in the nvarchar column. Otherwise I would use the ntext column.

    Can anyone confirm whether this technique would increase performance given that ntext values are sort of stored separately to the rest of the table data?

    A colleague of mine is an Oracle DBA and he mentioned this technique is fairly caommonly adopted in the Oracle world.

    Thanks
    Matt

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    You need to check out the 'text in row' table option.

    sp_tableoption N'MyTable', 'text in row', 'ON'

    When enabled if extended data (like ntext) can be fitted into unused row space it will be, rather than written to seperate paging.

Posting Permissions

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