2

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 ?

migrated from stackoverflow.com Feb 23 '14 at 16:35

This question came from our site for professional and enthusiast programmers.

  • Can you use a XML column? That will allow you unlimited extra fields in the XML DOM. – CRAFTY DBA Feb 22 '14 at 0:43
  • XML column will be my next possible choice, but I cannot index them and also cannot update them as separate columns. – alpav Feb 25 '14 at 21:19
1

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.

  • I agree. I've tried and could not even add wide table using sp_addarticle with , @vertical_partition = N'true'. It's also not allowed to add it MS SQL Studio UI. – alpav Mar 6 '14 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.