We have a database that we changed during the time in terms of adding/removing articles, columns to replication articles in a publication. Now when we script out that publication we have lines of code that are telling us that vertical partitioning is in question, like this:
exec sp_addmergearticle @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @source_owner = N'dbo', @source_object = N'Customer_DELETE_LOG', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'true', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_DATE', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_USER', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'COLUMN_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'ROW_ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'ROW_DELETED_VALUE', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'STATEMENT', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'PROCESSED', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'BATCH_ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'SERVER_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'APP_NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
Note here that @vertical_partition = N'true' and also note that we never do any vertical partition at least not intentionally.
Now the real issue here is when there are some computed columns in a table, and those computed columns are at the beginning of the table/article script (that includes vertical partitioning) processing will skip those columns! Because those columns are added to the replication article before their referenced column and thus they will break that sp_mergearticlecolumn statement. To avoid this (without checking every statement among 400) is to replace @vertical_partition = N'true' with @vertical_partition = N'false' and then run the whole thing. And this will work. BUT when you try to script out the whole thing those vertical partitioning statements will come again! WHY?
So again:
Thanks Dejan
UPDATE
I am digging on this now deeper, and started looking on tables themselves and noticed folowing:
Some tables were missing some indexes, like 1,2,3,5 -> where column at index 4 was probably deleted at a point of time. Could that cause this? Could that cause the publication scripting procedure to think that this is a partitioned table? If so then the Microsoft developers didn't finish this properly.
"what vertical partitioning actually means"
Vertical partitioning for a table article means that only specific columns and not all columns of the table are published.
why does SQL Server add it to the replication scripts
Most likely there are columns which are deselected/dropped from the article/table. Could you check in the article properties if there are columns which are deselected/not published?
Script wise, you could achieve the same by creating an article with @vertical_partition = N'false' and later @operation = N'drop' a column
--no vertical partitioning, all columns of the table are published
exec sp_addmergearticle @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @source_owner = N'dbo', @source_object = N'Customer_DELETE_LOG', @type = N'table',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'',
@vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0;
--drop/deselect one column from the article
exec sp_mergearticlecolumn @publication = N'secspe_pub', @article = N'Customer_DELETE_LOG', @column = N'LOG_USER', @operation = N'drop', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
GO
--if you script the publication anew, it will have article @vertical_partition = N'true' and all columns @operation = N'add' except for the LOG_USER column (which is excluded from publication and does not appear in the script at all)