none
our forum has msgs > 8K RRS feed

  • Question

  • Hi,

    Our website provide users with a forum where they can enter messages.  So, typically, a user would enter a message, click submit, and their message would be displayed on the forum.

    The db that stores these msgs uses a varchar field.  Now, we're adding a facility to convert any urls that are entered to links by adding 'a href' tags, so that when users see messages with urls, those urls appear as links, and they can click on them.  The problem is that if a user enters a message of 8K chars, then we add 'a href' tags to any urls they have, that makes the message length > 8K, preventing us from storing it in our db. (Please note that we do need to add HTML tags for other reasons as well, not just to convert urls to links.)

    We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive.   Timing tests revealed that the time to render such pages more than doubled.

    I have solved such problems previously by adding an extra field in the db to store any overflow data, that is, data that appears after the first 8K chars.

    However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max). 

    Thanks in advance for any advice.
    Shefali

    Monday, January 28, 2008 9:55 PM

Answers

  • We use both varchar(max) and text.  I will recommend varchar(max) over text any day if you do searching of any kind on the data, unless you plan to implement FTI.  With Varchar(Max) you have a 2GB limit per posting.  I doubt seriously that you will exceed that.

    Tuesday, January 29, 2008 2:48 AM
    Moderator

All replies

  • Moved to the Getting Started with SQL Server forum as this is clearly not an SSIS question.  With that said, if it works with your data, you can change to varchar(max) without any issues.  Have you tried it in a test environment?
    Tuesday, January 29, 2008 12:00 AM
    Moderator
  • We use both varchar(max) and text.  I will recommend varchar(max) over text any day if you do searching of any kind on the data, unless you plan to implement FTI.  With Varchar(Max) you have a 2GB limit per posting.  I doubt seriously that you will exceed that.

    Tuesday, January 29, 2008 2:48 AM
    Moderator
  • Thanks a lot.

    I simply converted a varchar(8000) field to varchar(max) and another ntext field to varchar(max), the latter conversion was for the sake of better perfromance of a varchar(max) vs. ntext, and my world stayed perfect.  Testing reveled no problems.  To do the conversion in sql server 2005, I used the statement:

    alter table <table name> alter column <column name> varchar(max).

    Just thought I should document this here in case it helps someone else.

    Shefali
    Sunday, February 10, 2008 11:31 PM