title: SQL Server: Does an UPDATE statement is faster if the table has indexes?
body: <p>I need to insert 1.5 Million records into a table with the following layout:</p>
  
  <pre class="lang-sql prettyprint-override"><code>MyEdition
  (
      MyEdition_ID INT,
      MyEntity_ID INT,
      copyType_ID INT,
      MyEdition_CopyText VARCHAR(MAX)
  )
  </code></pre>
  
  <p>I'm disabling all the INDEXES (both the clustered and non-clustered) and TRIGGERS on the table before inserting records from a temp table.</p>
  
  <p>I need to strip-out all the HTML tags (except Bold, Italic and Underline tags) from the <code>MyEdition_CopyText</code> column from all 1.5M rows, so I'm performing an <code>UPDATE</code> on the table which uses a SQL function to strip out HTML tags within the <code>MyEdition_CopyText</code> column.</p>
  
  <p>To summarise what I'm doing:</p>
  
  <ol>
  <li><code>ALTER INDEX [MyEdition_reindex] ON [MyEdition] DISABLE</code> (similarly all other indexes)</li>
  <li><code>ALTER TABLE [dbo].[MyEdition] DISABLE TRIGGER ALL;</code></li>
  <li><code>INSERT INTO MyEdition(&lt;columns&gt;) SELECT &lt;columns&gt; FROM &lt;#my_temp_table&gt;</code></li>
  <li><code>UPDATE MyEdition SET MyEdition_CopyText = dbo.StripHTML(MyEdition_CopyText)</code></li>
  <li><code>REBULID INDEXES</code></li>
  <li><code>ENABLE TRIGGERS</code></li>
  </ol>
  
  <p>The <code>UPDATE</code> statement is taking a little while, so I wonder if this is because all the indexes were disabled? What would be the best approach to strip out the HTML characters from the column?</p>
  
  <p>Any suggestions on how this could be improved? How SQL Server make use of indexes whilst performing an <code>UPDATE</code> operation? </p>
  
  <p>I'm using the following SQL function:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[StripOutHTML]
  (
      @HTMLText VARCHAR(max),
      @stripDisallowedOnly BIT
  )
  returns VARCHAR(max) 
  AS 
    BEGIN 
        DECLARE @Start INT
        DECLARE @End INT
        DECLARE @Length INT 
  
        -- Replace the HTML entity &amp; with the '&amp;' character (this needs to be done first, as  
        -- '&amp;' might be double encoded as '&amp;amp;')  
        SET @Start = Charindex('&amp;amp;', @HTMLText) 
        SET @End = @Start + 4 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '&amp;') 
              SET @Start = Charindex('&amp;amp;', @HTMLText) 
              SET @End = @Start + 4 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace the HTML entity &lt; with the '&lt;' character  
        SET @Start = Charindex('&amp;lt;', @HTMLText) 
        SET @End = @Start + 3 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '&lt;') 
              SET @Start = Charindex('&amp;lt;', @HTMLText) 
              SET @End = @Start + 3 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace the HTML entity &gt; with the '&gt;' character  
        SET @Start = Charindex('&amp;gt;', @HTMLText) 
        SET @End = @Start + 3 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '&gt;') 
              SET @Start = Charindex('&amp;gt;', @HTMLText) 
              SET @End = @Start + 3 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace the HTML entity &amp; with the '&amp;' character  
        SET @Start = Charindex('&amp;amp;amp;', @HTMLText) 
        SET @End = @Start + 4 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '&amp;') 
              SET @Start = Charindex('&amp;amp;amp;', @HTMLText) 
              SET @End = @Start + 4 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace the HTML entity   with the ' ' character  
        SET @Start = Charindex('&amp;nbsp;', @HTMLText) 
        SET @End = @Start + 5 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, ' ') 
              SET @Start = Charindex('&amp;nbsp;', @HTMLText) 
              SET @End = @Start + 5 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace any &lt;P&gt;, &lt;/P&gt;tags with a &lt;BR&gt;, so they will be replaced with a new line in next step  
        SET @HTMLText = REPLACE(@HTMLText, '&lt;P&gt;', '&lt;br&gt;') 
        SET @HTMLText = REPLACE(@HTMLText, '&lt;/P&gt;', '&lt;br&gt;') 
  
        -- Replace any &lt;BR&gt; tags with a newline  
        SET @Start = Charindex('&lt;br&gt;', @HTMLText) 
        SET @End = @Start + 3 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, 
                              Char(13) + Char(10)) 
              SET @Start = Charindex('&lt;br&gt;', @HTMLText) 
              SET @End = @Start + 3 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace any  tags with a newline  
        SET @Start = Charindex('&lt;br/&gt;', @HTMLText) 
        SET @End = @Start + 4 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, 
                              'CHAR(13) + CHAR(10)') 
              SET @Start = Charindex('&lt;br/&gt;', @HTMLText) 
              SET @End = @Start + 4 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Replace any  tags with a newline  
        SET @Start = Charindex('&lt;br /&gt;', @HTMLText) 
        SET @End = @Start + 5 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              SET @HTMLText = Stuff(@HTMLText, @Start, @Length, 
                              'CHAR(13) + CHAR(10)') 
              SET @Start = Charindex('&lt;br /&gt;', @HTMLText) 
              SET @End = @Start + 5 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Remove anything between  tags  
        SET @Start = Charindex('&lt;', @HTMLText) 
        SET @End = Charindex('&gt;', @HTMLText, Charindex('&lt;', @HTMLText)) 
        SET @Length = ( @End - @Start ) + 1 
  
        WHILE ( @Start &gt; 0 
                AND @End &gt; 0 
                AND @Length &gt; 0 ) 
          BEGIN 
              IF @stripDisallowedOnly = 1 
                BEGIN 
                    IF ( Upper(Substring(@HTMLText, @Start, 2)) &lt;&gt; '&lt;B' ) 
                       AND ( Upper(Substring(@HTMLText, @Start, 3)) &lt;&gt; '&lt;/B' ) 
                       AND ( Upper(Substring(@HTMLText, @Start, 2)) &lt;&gt; '&lt;U' ) 
                       AND ( Upper(Substring(@HTMLText, @Start, 3)) &lt;&gt; '&lt;/U' ) 
                       AND ( Upper(Substring(@HTMLText, @Start, 2)) &lt;&gt; '&lt;I' ) 
                       AND ( Upper(Substring(@HTMLText, @Start, 3)) &lt;&gt; '&lt;/I' ) 
                      BEGIN 
                          SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '') 
                      END 
                    ELSE 
                      BEGIN 
                          SET @Length = 0 
                      END 
                END 
              ELSE 
                BEGIN 
                    SET @HTMLText = Stuff(@HTMLText, @Start, @Length, '') 
                END 
  
              SET @Start = Charindex('&lt;', @HTMLText, @End - @Length) 
              SET @End = Charindex('&gt;', @HTMLText, Charindex('&lt;', @HTMLText, 
                                                   @Start) 
                         ) 
              SET @Length = ( @End - @Start ) + 1 
          END 
  
        -- Remove any leading space/carriage return 
        DECLARE @trimchars VARCHAR(10)
        SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
           IF @HTMLText LIKE '[' + @trimchars + ']%' SET @HTMLText = SUBSTRING(@HTMLText, PATINDEX('%[^' + @trimchars + ']%', @HTMLText), LEN(@HTMLText))
        RETURN Ltrim(Rtrim(@HTMLText)) 
    END
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>I have tested the following three ways:</p>
  
  <ol>
  <li><p>Update all in one SQL statement.</p>
  
  <p><code>UPDATE MyEdition SET MyEdition_Copy=[dbo].StripOutHTML(MyEdition_Copy, 1)</code></p></li>
  </ol>
  
  <p>-- This approach took 3 hours </p>
  
  <ol>
  <li><p>Updated 10000 records in one batch.</p>
  
  <p><code>DECLARE @min INT, @max INT, @batchSize INT
  SET @batchSize = 10000
  SELECT @min=MIN(MyEdition_id), @max=MAX(MyEdition_id)
  FROM MyEdition
  --PRINT 'MAX:' + CAST(@max AS VARCHAR(50))
  WHILE @min &lt; @max
  BEGIN
      DECLARE @x varchar(max) = ''
      SET @x = 'UPDATE MyEdition SET MyEdition_CopyText=dbo.StripOutHTML(MyEdition_CopyText, 1)'
                  + 'WHERE (copytextedition_id BETWEEN ' + CAST(@min AS VARCHAR(50)) + ' AND ' + CAST((@min + @batchSize -1) AS VARCHAR(50)) + ');'
      Exec(@x)
      PRINT @x
      SET @min = @min + @batchSize
  END</code></p></li>
  </ol>
  
  <p>-- This approach took 5 hours 20 minutes</p>
  
  <ol>
  <li>Loaded the values into .Net program, Stripped those characters and saved into the database.</li>
  </ol>
  
  <p>-- This approach took about 24 hours. </p>