SQL Server: Index columns used in like?

by

Given the data ‘abcdefg’

WHERE Column1 LIKE '%cde%'  --can't use an index

WHERE Column1 LIKE 'abc%' --can use an index

WHERE Column1 Like '%defg' --can't use an index, but see note below

Note: If you have important queries that require ‘%defg’, you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:

WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index

More Related Contents:

  1. List of all index & index columns in SQL Server DB
  2. T-SQL for finding Redundant Indexes
  3. Max Nbr of Months W/ No Activity in Past 12 Months
  4. SQL server query to output the data shown below
  5. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  6. How do I generate a random number for each row in a T-SQL select?
  7. Combine multiple results in a subquery into a single comma-separated value
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. Floor a date in SQL server
  10. How to drop a table if it exists?
  11. Create a view with ORDER BY clause
  12. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  13. INSERT INTO vs SELECT INTO
  14. Possible to store value of one select column and use it for the next one?
  15. How FOR XML PATH(”) works when concatenating rows
  16. Scheduled run of stored procedure on SQL server
  17. SQL Server ORDER BY date and nulls last
  18. SQL Server variable columns name?
  19. How to split string and insert values into table in SQL Server
  20. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  21. How can I get the sum of multiple datetime values?
  22. Use of Begin / End Blocks and the Go keyword in SQL Server?
  23. How do you determine what SQL Tables have an identity column programmatically
  24. Ampersand (&) operator in a SQL Server WHERE Clause
  25. Avg of float inconsistency
  26. Should I index a bit field in SQL Server?
  27. how to write number to word function in sql server [closed]
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment