Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, find records not ending with s, find records not ending with s puzzle, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, puzzle sql developer, Puzzles, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle, What You Can (and Can't) Do With Filtered Indexes
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)
The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to firstname.lastname@example.org
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
Pawan Kumar Khowal