Sql-server – Table Partitioning and updates to partition subset

coincidently I am also looking into one of the client support where we designed key-value pair structure for flexibility and currently table is over 1.5B rows and ETL is way too slow. well there are lot of other things in my case but have you thought about that design. you will have one row with all 200 columns present value, that row will convert in to 200 rows in Key-Value pair design. you will gain space advantage with this design depending on for a given AssetID and Date how many rows has actually all 200 f1 to f200 values present? if you say even 30% od columns have NULL value than that is your space saving. because in key-value pair design if value id NULL that row doesn't need to be in table. but in existing column structure design even NULL takes space.(I am not 100% sure but if you have more that 30 columns NULL in table then NULL take 4bytes). if you see this design and assume that all 35M rows has values in all 200 columns then you current db will become 200*35M=700M rows in table right away. but it will not be much high in table space what you had with all columns in single table as we are just Transposing the Columns in to row. in this transpose operation actually we will not have rows where the values are NULL. so you can actually run query against this table and see how many nulls are there and estimate you target table size before you actually implement it.

second advantage is read performance. as you mentioned that new way of querying the data is any combination this f1 to f200 column in where clause. with key value pair design f1 to f200 are present in one column lets say "FildName" and their values are present in second column lets say "FieldValue". you can have CLUSTERED index on both columns. your query will be UNION of those Selects.

WHERE (FiledName = 'f1' and FieldValue BETWEEN 5 AND 6)

UNION

(FiledName = 'f2' and FieldValue BETWEEN 8 AND 10)

etc.....

I will give you some performance numbers form actual prod server. we have 75 price columns for each security TICKER.

No. Partitioning does not speed things up. Your table that has 2 million records before partitioning, it will continue to have exactly the same 2 million records after partitioning. If you want to look only at a small subset of the records use an index. It does seem like your data is a multi-tenant schema with the tenant key be the ManufacturerID. In such cases the most likely design is to have the ManufacturerID be a leading key in the clustered indexes. BTW, I recommend reading Multi-Tenant Data Architecture.

Partitioning would help in scenarios involving fast data switch-in and switch-out, or in scenarios requiring distribution of data do different physical paths (filegroups). A good read is How To Decide if You Should Use Table Partitioning.

Before you say 'but what about partition elimination?' I'll say this: there's almost nothing partition elimination can do that an index cannot do better. Not to mention that partition elimination is a lottery while range scan on leading key in clustered index is pretty much a sitting duck. Besides, how many tenants will you have per partition? And how will you distribute them evenly, consider how unbalanced they seem to be? Both these problems are, again, addressed much better by a leading key in the clustered index. And, not least, consider the huge design changes partitioning brings (e.g. kiss goodbye the unique/primary key constraints or introduce unaligned indexes), see Special Guidelines for Partitioned Indexes, pay special attention to the 'Memory Limitations' section.

Partitioning is an awesome feature, but is not a performance feature. An example of what partitioning could help is to do a very fast switch-in of the staged data (which is a problem you raised), but that would require one partition per manufacturer, and it will not scale, assuming that each 'manufacturer' is a client of your business (a tenant).