Does Adding Indexes speed up String Wildcard % searches?

We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance?

CREATE TABLE [dbo].[Product](
    [ProductId] [int] NOT NULL,
    [ProductName] [varchar](250) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL, 
    ...
    CONSTRAINT [PK_ProductId] PRIMARY KEY CLUSTERED 
    (
       [ProductId] ASC
    )
 )

Proposed Index:

CREATE NONCLUSTERED INDEX [IX_Product_ProductName] ON [dbo].[Product] [ProductName])

for this query

select * from dbo.Product where ProductName like '%furniture%'

Currently using Microsoft SQL Server 2019.

Answer

Creating a normal index will not help(*), but a full-text index will, though you would have to change your query to something like this:

select * from dbo.Product where ProductName CONTAINS 'furniture'

(* — well, it can be slightly helpful, in that it can reduce a scan over every row and column in your table into a scan over merely every row and only the relevant columns. However, it will not achieve the orders of magnitude performance boost that we normally expect from indexes that turn scans into single seeks.)