title: Why is my view slow when I have indexed the columns?
body: <p>In SQL Server I have put a clustered index on a view to <strong>eliminate</strong> the <strong>inefficiency</strong> of a <strong>join using LIKE</strong> statements:</p>
  
  <pre><code>   CREATE VIEW KeywordCount WITH SCHEMABINDING
      AS
      SELECT 
          K.ID AS KeywordID
          ,COUNT_BIG(*) AS KeywordCount
      FROM dbo.Grants G
      INNER JOIN dbo.GrantStatuses GS2 ON GS2.ID = G.StatusID AND GS2.Status NOT IN ('Pre-Submission', 'Awaiting Signatory Approval', 'Modifying', 'Closed')
      INNER JOIN dbo.Keywords K
          ON G.Keywords LIKE '%' + K.Word + '%'                                           --It's one of the grant's keywords
          OR G.Title LIKE '%' + K.Word + '%'                                              --Word appears in the title
          OR Replace(G.Title, '-', ' ') LIKE '%' + Replace(K.Word, '-', ' ') + '%'        --Word with hyphens replaced appears in the title
          OR G.Synopsis LIKE '%' + K.Word  + '%'                                          --Word appears in the Synopsis
          OR Replace(G.Synopsis, '-', ' ') LIKE '%' + Replace(K.Word, '-', ' ')+ '%'      --Word with hyphens replaced appears in the synopsis
      GROUP BY K.ID
      GO
  
      CREATE UNIQUE CLUSTERED INDEX IX_KeywordCount 
          ON dbo.KeywordCount (KeywordID)
      GO
  </code></pre>
  
  <p>Then I added another index on the KeywordCount column:</p>
  
  <pre><code>    CREATE INDEX IX_KeywordCount_Count 
          ON dbo.KeywordCount (KeywordCount)
      GO
  </code></pre>
  
  <p>So why does the following query take 7 minutes to run? Shouldn't the index give me much better performance?</p>
  
  <pre><code>    SELECT TOP 10 * FROM KeywordCount ORDER BY KeywordCount DESC
  </code></pre>
  
  <p><strong>EDIT</strong>
  Thanks everyone, but I know that <strong>LIKE statements and REPLACE will make this view inefficient</strong>. That's why I added the clustered index. I thought that putting a clustered index onto the view would materialize the data into a table so that the database <strong>would not have to do the joins</strong>. The query plan does say that it is doing the joins. Why is that? </p>