That should be fairly easy using tally tables.

The trickier part would be excluding certain letter combinations, meaning of course you have to determine which ones to omit.  For example: 'ASS',...

This should give you some idea.  With that partitioning, you can no longer have ClaimLineID as a pk (or even a stand-alone unique index/constraint).

If you can use data compression, you...

Not at that level.

I believe ONLINE create/rebuild of indexes is not available in standard.

If you're on SQL 2016 on above, data compression is still available.

I would definitely use a conversion table.

Personally I prefer the "A[0-9][0-9]" format.

Here's an alternative, an all-in-one function that directly produces the last_business_days from start_date and end_date params.

If for some reason you needed more than 100 months of results, naturally you'd have...

If you don't already have an index on the dbo.OptionsEOD table to directly support the "LAG(T1.CLOSE_PRICE) OVER PARTITION BY T1.UNDERLYING_SYMBOL, T1.ROOT_SYMBOL, T1.EXPIRATION, T1.STRIKE, T1.OPTION_TYPE ORDER BY T1.QUOTE_DATE)" you should try...

as an example - assume a sequence increment of 500 - and you need 5000. ...

you do a "SELECT NEXT VALUE FOR sequencename; " 10 times and you immediately...

The most important thing (assuming basics such as enough RAM and CPU, etc. are OK) is likely to analyze missing index data (do not blindly build all indexes SQL suggests). ...

SQL Server does not allow duplicate column names in a table.

You could put brackets in the actual column name if you really wanted to, although of course that's a terrible...

I agree.  Yes, MS might have good reasons for dropping the cache in those cases, but it should document that it will do that.

That seems to be normal SQL Server activity, to clear the plan cache when certain ALTER DATABASE statements are executed.  Ref:

https://www.sqlskills.com/blogs/erin/query-store-and-the-plan-cache-flushing/

Your table looks to be clustered first (probably only) on ID.  Add add_date after ID in the clus index to help that type of query.

Oops, I misread, I thought the 3rd column was just INCLUDEd, and that it had the same key columns as the original index.

The new index should handle any existing fk constraint(s), since both indexes have the same keys.

A unique constraint should be a different structure, i.e., you'd have to use a DROP...

Yes, you can delete idx1.  If you use hints that force use of that specific index (which is very rare (or at least should be!)), then you would have to...