Searching an alternate of LEN() function for optimization in sql server

query-optimization, sql, sql-server

Solution

There are alternative ways of writing this. For example

SELECT firstName
FROM student
WHERE firstName LIKE '_____%'

But this is not any more index friendly.

You can create a computed column with `LEN(firstName)` and index that though.

CREATE TABLE student
(
firstName VARCHAR(100),
LenFirstName AS LEN(firstName) 
)

CREATE INDEX IX on student(LenFirstName) INCLUDE (firstName)

SELECT firstName
FROM student
WHERE LEN(firstName) > 5

Problem

I am having a query like this ``` SELECT firstName FROM student WHERE LEN(firstName) > 5 ``` There is a Non-Clustered Index on the 'firstName' column. It is normally advisable not to use SQL function with column of table in WHERE, HAVING etc, clauses. Otherwise sql server will unable to use indexes. So is there any alternate way to write this query without using LEN() on the firstName column?

Original source