How to make this query Sargable

Question

Hi All,

How to make this query Sargable and make use of index. I see the 2nd expression the WHERE is causing the query to take long time to execute.

<?query --
SELECT comm_val
						,party_id
						,@email_domain domain
					INTO #comm
					FROM C_B_PARTY_COMM(NOLOCK) comm
					WHERE COMM_TYP_CD = 'EMAIL'
					and  SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val))  = @email_domain
--?>

Regards,

Sam

Answer 1

Consider creating a computed column for the domain name along with a filtered index:

ALTER TABLE dbo.C_B_PARTY_COMM
	ADD email_domain AS CASE WHEN COMM_TYP_CD = 'EMAIL' THEN SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val)) END;

CREATE INDEX idx ON dbo.C_B_PARTY_COMM(comm_typ_cd, email_domain) 
	INCLUDE(comm_val)
	WHERE COMM_TYP_CD = 'EMAIL';

This should provide the index seek on both comm_typ_cd and the email_domain as shown in this plan with the query below: https://www.brentozar.com/pastetheplan/?id=HJQobusEa

DECLARE @email_domain varchar(255) = 'domain.com';
SELECT comm_val
	,party_id
	,@email_domain domain
INTO #comm
FROM C_B_PARTY_COMM(NOLOCK) comm
WHERE
	COMM_TYP_CD = 'EMAIL'
	AND email_domain = @email_domain;

The computed column CASE expression will parse only 'EMAIL' comm_typ_cd values and the index filter will include only 'EMAIL' comm_typ_cd rows in the index to reduce storage and memory requirements.