The reason of me using sparse columns is to overcome limit of 1024 columns in a table (I know that generally it’s not a good reason and there are downsides, but in my situation it’s good option compared to software rewrite option and I didn’t write that software, so don’t tell me it’s bad design, I know).
My customer uses transactional table replication for backup (which means they replicate whole table). So I wonder if replication will work after I start adding those extra sparse columns above 1024 limit.
So far I found 3 limits:
1) This says that I cannot replicate column sets.
2) This says that SQL Server snapshot or transactional publication limit is 1000 columns.
3) From my experiments I found that I cannot add more than 1024 sparse columns if I don’t have column set defined.
From these 3 limits I conclude that I cannot replicate wide master table into wide slave table.
Is my conclusion correct ?
Is it possible to replicate non-wide parts of master row to combine into one wide row in slave table ? Non-wide I mean < 1000 columns.
If possible then how ? Are there any gotchas for replicating wide tables ?
4) It looks like custom replication SP is one possible answer. Did anybody try to replicate more than 1000 columns with custom replication SPs ?
To make a table into a wide table, you must create sparse columns and also add a column set. If you don’t create a column set your table will still be limited to 1024 columns. This is why you can’t use replication for wide tables.