T-SQL Query | [ Records not ending with a character Puzzle ] – BEST Approach

Yesterday I was going through one of the groups on Facebook and got this question. The question is very easy; You have to find out employees where name does not end with ‘s’ . Many people can easily answer this but didn’t get the internals of it.. I will explain the puzzle in detail. Let’s first go through the sample input and expected output below-

Pictorial presentation of the puzzle.(Pic taken from some Facebook group)

Sample Input

Id	Name
100	Smith
101	Allen
103	Martin
102	Jones

Expected Output

Id	Name
100	Smith
101	Allen
103	Martin

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

--

--Table Creation

CREATE TABLE SearchString
(
	 ID INT
	,Name VARCHAR(10)
)
GO

--Data insertion

INSERT INTO SearchString VALUES
(100, 'Smith'),
(101, 'Allen'),
(102, 'Jones'),
(103, 'Martin')
GO

--Create Indexes
CREATE UNIQUE CLUSTERED INDEX Ix_ID ON SearchString(ID)
GO

CREATE NONCLUSTERED INDEX Ix_Name ON SearchString(Name)
GO

--

Update Sep 24 | Solutions # – Pawan Khowal

--

--Solution 1

SELECT Id, Name FROM SearchString
where Name LIKE '%[^s]'

--Solution 2

SELECT Id, Name FROM SearchString
where LEFT(REVERSE(Name),1) <> 's'

--

Lets compare the execution plans of the the above solutions –

Both the approaches are sub optimal only and equal in terms of  the performance. Although i will prefer the first approach any day since we are not putting any function around column in the where clause. In the both the cases we are index scan but i want seek. So lets go through another solution. We need a little of word around for this.

--
--Solution 3 - The Best Approach

ALTER TABLE SearchString
	ADD CharName AS RIGHT(Name,1)

CREATE NONCLUSTERED INDEX Ix_CharName1 ON SearchString(CharName)
INCLUDE (ID,Name)


SELECT Id,Name FROM SearchString
WHERE CharName = 's'

SELECT Id, Name,right(Name,1) FROM SearchString
WHERE RIGHT(Name,1) <> 's'

--

In the above case we have added a computed column and then created a non clustered index on computed column.Lets go through the execution plan of newly rewritten queries.

This the best approach (Add a computed column and have index on it) we have, but sometimes we don’t have permissions to alter tables or you have very less data and you don’t care about the performance. In that case you can follow the first approach.

Thank Guys !

Add a comment if you have any other solution in mind. We all need to learn. Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal