Azure SQL – Super fast string wildcard search with pagination

Cheat Code
Index on column

Today mission is to allow user to run super fast query on Azure SQL table.
Not any kind of query, it’s a string search query… something like:

SELECT site_id, Site_Name, EnglishEntityName
FROM [dbo].[DIM_SITE]
WHERE (site_id LIKE '%yahoo%'
OR Site_Name LIKE '%yahoo%'
OR Site_lTag LIKE '%yahoo%')
ORDER BY 1 OFFSET 91 ROWS FETCH NEXT 10 ROWS ONLY

The table is not too big, just 80 Millions rows (that’s freaking big actually).
Why the query written that way is beyond this post (we need the smart-ass developer to explain that).

Anyway jump to cheat here’s what works for me.
It’s running on Azure SQL S6 – 400 DTU

The table need to have 2 indexes:
> Clustered on the “ID”
> “Non Cluster-including ID” index

Also page compression to make it turbo!

CREATE TABLE [dbo].[RAW_SITE](
	[sitekey] [int] NULL,
	[sitename] [varchar](250) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[RAW_SITE] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);


CREATE NONCLUSTERED INDEX [idx_site] ON [dbo].[RAW_SITE]
(
	[sitename] ASC
)
INCLUDE([sitekey]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [idx_site_key] ON [dbo].[RAW_SITE]
(
	[sitekey] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]