Select query within more than 150 different conditions in where clause

First, SQL Server can handle very long queries. I have created queries that are at least 150k characters, and they work without problem. The limit is considerably larger than that.

Second, you are correct that a bunch of like statements is going to take a long time. There is overhead to like.

Third, your patterns do not conform to your statement. If you want names that start with a particular pattern, then remove the wildcard from the beginning of the pattern. This has the added benefit that SQL Server can use a regular index on FullName for the match.

Finally, if you are really looking at initial strings, then you might want to consider a full text index (here is one place to start). These are usually more efficient than using like.