Is it possible to replicate wide table in MS SQL?

Posted on

Question :

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 ?

Update:

4) It looks like custom replication SP is one possible answer. Did anybody try to replicate more than 1000 columns with custom replication SPs ?

Answer :

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.

Leave a Reply

Your email address will not be published.