One of our developers created a view whereby one of the select columns was wrapped in an RTRIM function. That same column on the underlying table has a non-clustered index on it.

Now queries using this view and a where statement on this column are failing to use the index due to the RTRIM. I need the view's output to be consistent as unfortunately this developer is no longer with us and I can't read his code.

What are some options for optimisation? I've got one, but I'm hoping there is better.


Create a new computed column on the underlying table that is an RTRIM of the column in question. Add an index on this column. Adjust the view to use this computed column.

  • 1
    Or make it an Indexed View Jun 29 '17 at 14:26
  • @DavidBrowne-Microsoft not used those before. I tried putting a clustered on the view ID column, and a non-clustered on the view column I care about. It didn't help, it still scans the index of the underlying table
    – Paul
    Jun 29 '17 at 14:41
  • You can try querying with the NOEXPAND hint. But a better solution is probably to ensure that no trailing spaces are present in the column and eliminate the RTRIM. You can ensure that no trailing spaces will be present with either a constraint or a trigger. Jun 29 '17 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.