You specify either a partition or a filegroup, not both (if partition, the partitioning will determine the filegroup(s) used).

create index IX_Service_Start_Date ON ivc_cds.CDS_Claim_Header (Service_Start_Date )

WITH (FILLFACTOR = 98, SORT_IN_TEMPDB =...

Try this:

SELECT DISTINCT Lotr.LabOrderUnique,
    STUFF( (SELECT DISTINCT CAST('^' AS varchar(max)) + CAST(lotr1.TestName AS varchar(max))
        FROM dbo.Ncs_conv_IntergyMM_Labordertestresult AS...

You very likely already have a clustered index on the table, on ( ClaimHeaderID, ClaimLineID ).  (That's a vastly better clus key than an identity column, btw.)

You can only have one...

But you're going to have to include the date in the clustering key to do partitioning anyway, you might as well change the clustering first.  And that will be less...

By far the best performance payoff for this is creating the best clustered index on the table, as coded below.  You would likely not even need to partition then.

Based on...

Clustering by the date will solve the performance issues.  Partitioning will aid performance only if you need to rebuild the table, in which case you would only need to rebuild...

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...