locked
Best way to store HTML in SQL Server 2008? RRS feed

  • Question

  • Hi All,

     

    I am tryign to figure out the best datatype to use to store data in SLQ Server 2008.  My issue is that we want to store HTML and I wanted to use triggers to keep track of historical changes made to those HTML columns.  So in 2008 it appears that text/ntext has been deprecated from the inserted/deleted tables used in triggers so I was thinking I could use the varbinary(max) data type.  However, we would have to conver the data to varbinary on insert then have to do somethign similar when we extract it?

    The fields are bigger than 8000 so if I convert it using varchar(max) won't that truncate the data?

    Any thoughts /commets are gretaly appreciated.

    Thanks,


    S

    Friday, August 6, 2010 5:21 PM

Answers

  • If you do not have Unicode in the HTML then VARCHAR(MAX) is best, else NVARCHAR(MAX). It contains up to 2 GB of data and it is not truncated at 8000. 
    Plamen Ratchev
    • Marked as answer by BenHamiin Friday, August 6, 2010 5:46 PM
    Friday, August 6, 2010 5:25 PM
  • Hi Ben,

    All the MAX data types and XML hold up to 2 GB of data, which is well beyond the 8000 character limit in SQL 2000.  The answer to your question is going to depend on how you plan to use the data.  I would probably recommend you store the html as XML, if you plan to shred the content.  If you dont want to shred the content and only require a few modifications here and there, I would store the HTML as varbinary(max); otherwise, varchar(max)/nvarchar(max) will work. 

    Note: choose nvarchar(max) if your data has unicode characters.

    DECLARE @t TABLE(id INT,html XML)
    INSERT INTO @t VALUES (1,'<html><body><p>this is some text</p><p>this is some more text</p></body></html>')
    
    SELECT * FROM @t
    
    SELECT 
    	id,
    	x.i.value('.','varchar(max)') AS p_Txt
    from @t
    CROSS APPLY html.nodes('/html/body/p') x(i)
    
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Friday, August 6, 2010 5:53 PM
    • Edited by Adam Haines Friday, August 6, 2010 5:54 PM syntax
    • Marked as answer by BenHamiin Friday, August 6, 2010 7:19 PM
    Friday, August 6, 2010 5:32 PM

All replies

  • If you do not have Unicode in the HTML then VARCHAR(MAX) is best, else NVARCHAR(MAX). It contains up to 2 GB of data and it is not truncated at 8000. 
    Plamen Ratchev
    • Marked as answer by BenHamiin Friday, August 6, 2010 5:46 PM
    Friday, August 6, 2010 5:25 PM
  • Hi Ben,

    All the MAX data types and XML hold up to 2 GB of data, which is well beyond the 8000 character limit in SQL 2000.  The answer to your question is going to depend on how you plan to use the data.  I would probably recommend you store the html as XML, if you plan to shred the content.  If you dont want to shred the content and only require a few modifications here and there, I would store the HTML as varbinary(max); otherwise, varchar(max)/nvarchar(max) will work. 

    Note: choose nvarchar(max) if your data has unicode characters.

    DECLARE @t TABLE(id INT,html XML)
    INSERT INTO @t VALUES (1,'<html><body><p>this is some text</p><p>this is some more text</p></body></html>')
    
    SELECT * FROM @t
    
    SELECT 
    	id,
    	x.i.value('.','varchar(max)') AS p_Txt
    from @t
    CROSS APPLY html.nodes('/html/body/p') x(i)
    
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Friday, August 6, 2010 5:53 PM
    • Edited by Adam Haines Friday, August 6, 2010 5:54 PM syntax
    • Marked as answer by BenHamiin Friday, August 6, 2010 7:19 PM
    Friday, August 6, 2010 5:32 PM
  • Thanks a lot for the input Adam....I really appreciate it.  Is there anything special you have to do when you store it as varbinary(max) to get it to render properly? (Just curious).

     

    Thanks again!

     

    B

    Friday, August 6, 2010 7:19 PM
  • if you store in varbinary(max), you would have to convert it to display it; otherwise, you would be looking at binary data.  If you are going to be displaying the data alot, it would be better to use nvarchar(max)/varchar(max).  like i said it really depends on how you are going to use the column.
    http://jahaines.blogspot.com/
    Friday, August 6, 2010 7:39 PM