[Communication] Index utilization problem when sqlserver handles like '%keyword%'

Statement 1: The percent sign % wildcard prefix will cause SQL queries to stop the index and instead use full table scanning. This statement is popular

The conclusion is wrong

In fact, this statement is not very accurate The wildcard % prefix will make SQL search for indexes extremely efficient, but in most cases it will still go to the index (no full-text index is required, just build a normal index)

CREATE NONCLUSTERED INDEX [Ix_index name] ON [dbo]. [wkf_ Table Name]
(
[db_title]  ASC
)

Execute at this time

SELECT top 10 [db_id],[db_Summary],[db_AddDate],[db_title] FROM [library name]. [dbo]. [wkf_database]   where [db_title]like '%dba%'  order by 1 desc

The query plan is clearly displayed

Before the comparison is indexed:

As an exception, complex queries The query optimizer may abandon the index in favor of full table scanning. This is not only the case with LIKE '%keyword%', but also related to query complexity

Statement 2: Percent % wildcard prefixes will make SQL queries index rather than no index

This statement is very one-sided, and 99% of the index will reduce IO and improve efficiency compared to not indexing, but the key matching action after the index is found is also partly performance-consuming. As shown in the two figures above, if the keywords are easily matched, the full table scan quickly finds the data, and the index scan does not save enough time to make up for the time consumed by the key matching action (most online queries do not have this problem).
Treatment:
1. If you don't care about it, the extra performance consumption is not very large. And different keywords have different consumption, but some keywords have this problem and can be ignored
2. A better way is to build an override index (also known as an INCLUDE index) if conditions allow it. Premise: a. The storage space is sufficient, b does not significantly affect DML operations, and c does not have large fields in the overwritten index

CREATE NONCLUSTERED INDEX [Ix_index name] ON [dbo]. [wkf_ Table Name]
(
[db_title]  ASC
)
INCLUDE ( [db_id],[db_Summary],[db_AddDate])

At this time, the execution query plan is as follows, which is much more refreshing

The above is what I can think of now for SQLSERVER processing SELECT * FROM TABLENAME LIKE '%Keyword %'