IT researcher (7/26/2013)
ChrisM@Work (7/23/2013)
-- why cast [Phone no] AS VARCHAR(MAX)?-- why use CHARINDEX? You don't need the character position, you only need to know if
-- one string exists inside the other.
SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )
-- use this instead
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '%9000413237%'
-- then try this, which is SARGable (can use a suitable index)
SELECT c.[Customer name],c.[Sl_No],c.[Id]
FROM [company].dbo.[customer] c
WHERE c.[Phone no] LIKE '9000413237%'
You have mentioned that using LIKE '9000413237%' is SARGable .There are many articles stating 'abc%' will use index but '%abc%' not.But using LIKE '%9000413237%' also helped me a lot.
Please see my thread regarding this here
It's quicker (in this case) to scan the phone_no index than to scan the whole table, because the index only contains one (key) column. That's less data to scan through. The point about SARGability is not that an index will be used, but that the index will be used for seeks or range scans. If you look at the second plan on your ScatExchange post you see an index scan for LIKE '%...%'. Look at what happens if you take out the leading % sign.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden