locked
Moving from 'text' to 'varchar(max)' RRS feed

  • Question

  • I am trying to get rid off 'text' columns from the tables (SQL Server 2005). I do not use any functions on this column.

    1. Is there any issue related to UNICODE when i move from text to varchar(max)?

    2. Will moving to varchar(max) give any performance improvement?

    3. If i just change all the existing 'text' columns to 'varchar(max)' [instead of varchar(1000) assuming 1000 is the maximum length required], is there any disadvantage? This approach will require no change in the application code.

    4. Tables which has 'text' column, which stores only upto a maximum of 1000 characters. For this types, i am planning to use varchar(1000).

    5. Tables which has 'text' column, which stores larger than 1000 characters.(images etc) For this types, i am planning to use varchar(max) or varbinary(max)

    Please provide your expert opinions so that i can take right decision on this.
    Tuesday, March 31, 2009 10:48 AM

Answers

  • 1. If you need to store UNICODE, you need to use nvarchar(1000) or nvarchar(max).

    2. I have not seen or heard of any performance improvement

    3. This depends. It would be better to change things like the data types of input parameters to stored procedures to match the new data types. That might cause issues for your application, depending on the language and data access technology used.

    4. This is fine. Use nvarchar(1000) if you need to store UNICODE.

    5. This is fine. Use nvarchar(max) if you need to store UNICODE.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    Tuesday, March 31, 2009 6:17 PM
  • >>

    3. From the database (storage, performance aspects etc.) perspective, is there any disadvantage if i just convert all the 'text' columns to varchar(max)?
    <<


    Here is some background for you.

    SQL Server stores data in pages of 8KB and with a max. of 8060 bytes per row. If you are using varchar, nvarchar, varbinary, sql_variant datatypes then the column length will be limited to 8000 bytes each and the max. length of the row should fall below 8060 bytes. This limit is waived/NOTchecked against the varchar(max) data types. 

    If the row sizes exceeds 8060 bytes then there are certain performance implications as SQL Server enforces 8KB page size. SQL Server moves the max. width column in these scenarios to a separate page in the ROW_OVERFLOW_DATA allocation unit, and leaves a 24 byte pointer in the current page. This is done dynamically and when you update this column from short to long values the pages are moved dynamically causing a performance hit. ANd when you query these columns with sorts/joins there will be additional processing that needs to be done and this can cause slowness.

    --This will work
    create table T1 (c1 varchar(8000))
    
    --This will NOT work
    create table T2 (c1 varchar(8001))
    
    



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    Wednesday, April 1, 2009 6:58 AM
  • Conversion should be quite simple using the CONVERT function. If you have unicode characters, then use nvarchar(max) instead.  If you know the upper limit of the input, then use varchar(UpperLimit) otherwise use varchar(max). Demo follows.

    -- SQL Server 2008 T-SQL demo conver text to varchar(max)
    USE tempdb 
    GO 
    
    CREATE TABLE Omega ( 
      TextCol       TEXT, 
      VarCharMaxCol VARCHAR(MAX)) 
    O 
    
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('A',1100) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('B',1200) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('C',1300) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('D',1400) 
    
    UPDATE Omega 
    SET    VarCharMaxCol = CONVERT(VARCHAR(MAX),TextCol) 
    
    SELECT TextLen = DATALENGTH(TextCol), 
           VarCharMaxLen = LEN(VarCharMaxCol) 
    FROM   Omega 
    GO 
    
    /*   
    TextLen  VarCharMaxLen 
    1100  1100 
    1200  1200 
    1300  1300 
    1400  1400 
    */ 
    
    -- Cleanup 
    DROP TABLE Omega 
    GO 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    • Edited by Kalman Toth Thursday, October 4, 2012 11:27 AM
    Tuesday, March 31, 2009 11:09 AM

All replies

  • hi,

    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.

    Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.



    The large-value data types are similar in behavior to their smaller counterparts, varchar, nvarchar and varbinary. This similarity enables SQL Server to store and retrieve large character, Unicode, and binary data more efficiently.

    Use varchar(max), nvarchar(max), and varbinary(max) data types instead of text, ntext, and image data types.

    Above info is from
    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/b0d8769c-7598-4f97-8162-ace5f182b5bc.htm


    Rajesh Jonnalagadda http://www.ggktech.com
    Tuesday, March 31, 2009 10:57 AM
  • Conversion should be quite simple using the CONVERT function. If you have unicode characters, then use nvarchar(max) instead.  If you know the upper limit of the input, then use varchar(UpperLimit) otherwise use varchar(max). Demo follows.

    -- SQL Server 2008 T-SQL demo conver text to varchar(max)
    USE tempdb 
    GO 
    
    CREATE TABLE Omega ( 
      TextCol       TEXT, 
      VarCharMaxCol VARCHAR(MAX)) 
    O 
    
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('A',1100) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('B',1200) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('C',1300) 
    INSERT INTO Omega            (TextCol) SELECT REPLICATE('D',1400) 
    
    UPDATE Omega 
    SET    VarCharMaxCol = CONVERT(VARCHAR(MAX),TextCol) 
    
    SELECT TextLen = DATALENGTH(TextCol), 
           VarCharMaxLen = LEN(VarCharMaxCol) 
    FROM   Omega 
    GO 
    
    /*   
    TextLen  VarCharMaxLen 
    1100  1100 
    1200  1200 
    1300  1300 
    1400  1400 
    */ 
    
    -- Cleanup 
    DROP TABLE Omega 
    GO 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    • Edited by Kalman Toth Thursday, October 4, 2012 11:27 AM
    Tuesday, March 31, 2009 11:09 AM
  • 1. If you need to store UNICODE, you need to use nvarchar(1000) or nvarchar(max).

    2. I have not seen or heard of any performance improvement

    3. This depends. It would be better to change things like the data types of input parameters to stored procedures to match the new data types. That might cause issues for your application, depending on the language and data access technology used.

    4. This is fine. Use nvarchar(1000) if you need to store UNICODE.

    5. This is fine. Use nvarchar(max) if you need to store UNICODE.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    Tuesday, March 31, 2009 6:17 PM
  • Hi GlennAlanBerry,

    I have a follow-up question on 3.

    3. From the database (storage, performance aspects etc.) perspective, is there any disadvantage if i just convert all the 'text' columns to varchar(max)? I do not use stored procedures.


    Please confirm whether the following steps are enough for converting text to varchar(max).
    Moving from text to varchar(max)

    1. Alter column 'text' to 'varchar(max)'
    2. update column = column
    • Edited by siva.k Wednesday, April 1, 2009 5:30 AM added a question
    Wednesday, April 1, 2009 5:28 AM
  • Try this,

    ALTER TABLE TableName ALTER COLUMN ColumnName VARCHAR(MAX)
    Rajesh Jonnalagadda http://www.ggktech.com
    Wednesday, April 1, 2009 6:29 AM
  • >>

    3. From the database (storage, performance aspects etc.) perspective, is there any disadvantage if i just convert all the 'text' columns to varchar(max)?
    <<


    Here is some background for you.

    SQL Server stores data in pages of 8KB and with a max. of 8060 bytes per row. If you are using varchar, nvarchar, varbinary, sql_variant datatypes then the column length will be limited to 8000 bytes each and the max. length of the row should fall below 8060 bytes. This limit is waived/NOTchecked against the varchar(max) data types. 

    If the row sizes exceeds 8060 bytes then there are certain performance implications as SQL Server enforces 8KB page size. SQL Server moves the max. width column in these scenarios to a separate page in the ROW_OVERFLOW_DATA allocation unit, and leaves a 24 byte pointer in the current page. This is done dynamically and when you update this column from short to long values the pages are moved dynamically causing a performance hit. ANd when you query these columns with sorts/joins there will be additional processing that needs to be done and this can cause slowness.

    --This will work
    create table T1 (c1 varchar(8000))
    
    --This will NOT work
    create table T2 (c1 varchar(8001))
    
    



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Marked as answer by siva.k Monday, April 6, 2009 9:16 AM
    Wednesday, April 1, 2009 6:58 AM
  • I read somewhere that when converting text to varchar(max), an update on the column has to be done so that the data is kept (wherever possible) along with table (page). This happens only during insert/update operation, and not on alter column operation. That is the reason i mentioned the update query.
    Thursday, April 2, 2009 5:02 AM