{"id":504,"date":"2011-12-07T15:27:36","date_gmt":"2011-12-07T15:27:36","guid":{"rendered":"\/blogs\/jonathan\/post\/Finding-Replication-Schema-Options.aspx"},"modified":"2017-04-13T14:41:27","modified_gmt":"2017-04-13T18:41:27","slug":"finding-replication-schema-options","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/finding-replication-schema-options\/","title":{"rendered":"Finding Replication Schema Options"},"content":{"rendered":"

This afternoon, Orson Weston (Twitter<\/a>), asked how to find the difference between two binary sets of schema options<\/a> for replication on the #SQLHelp<\/a> hash tag on twitter.  The valid values for the @schema_options parameter in replication are documented in the BOL Topic for sp_addarticle (http:\/\/msdn.microsoft.com\/en-us\/library\/ms173857.aspx<\/a>).  However, just having a table of values doesn\u2019t really help you figure out what is different between two binary @schema_options values without doing some bitwise operations on the values.  To do this, we can create a table variable to hold the valid values and descriptions for the schema options and then use the & (Bitwise AND)<\/a> operator to find which options are set for each of the binary values.<\/p>\n

\n
DECLARE<\/span> @options TABLE<\/span>\r\n(Value<\/span> VARBINARY(8), [Description] VARCHAR<\/span>(1000))\r\n\r\nINSERT INTO<\/span> @options (Value<\/span>, Description)\r\nVALUES<\/span> \r\n    (0x00, 'Disables scripting by the Snapshot Agent and uses creation_script.'<\/span>),\r\n    (0x01, 'Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.'<\/span>),\r\n    (0x02, 'Generates the stored procedures that propagate changes for the article, if defined.'<\/span>),\r\n    (0x04, 'Identity columns are scripted using the IDENTITY property.'<\/span>),\r\n    (0x08, 'Replicate timestamp columns. If not set, timestamp columns are replicated as binary.'<\/span>),\r\n    (0x10, 'Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'<\/span>),\r\n    (0x20, 'Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x40, 'Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.'<\/span>),\r\n    (0x80, 'Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.'<\/span>),\r\n    (0x100, 'Replicates user triggers on a table article, if defined. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x200, 'Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table are not replicated. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x400, 'Replicates check constraints. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x800, 'Replicates defaults. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x1000, 'Replicates column-level collation.'<\/span>),\r\n    (0x2000, 'Replicates extended properties associated with the published article source object. Not supported for Oracle Publishers.'<\/span>),\r\n    (0x4000, 'Replicates UNIQUE constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled.'<\/span>),\r\n    (0x8000, 'This option is not valid for SQL Server 2005 Publishers.'<\/span>),\r\n    (0x10000, 'Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization.'<\/span>),\r\n    (0x20000, 'Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization.'<\/span>),\r\n    (0x40000, 'Replicates filegroups associated with a partitioned table or index.'<\/span>),\r\n    (0x80000, 'Replicates the partition scheme for a partitioned table.'<\/span>),\r\n    (0x100000, 'Replicates the partition scheme for a partitioned index.'<\/span>),\r\n    (0x200000, 'Replicates table statistics.'<\/span>),\r\n    (0x400000, 'Default Bindings'<\/span>),\r\n    (0x800000, 'Rule Bindings'<\/span>),\r\n    (0x1000000, 'Full-text index'<\/span>),\r\n    (0x2000000, 'XML schema collections bound to xml columns are not replicated.'<\/span>),\r\n    (0x4000000, 'Replicates indexes on xml columns.'<\/span>),\r\n    (0x8000000, 'Create any schemas not already present on the subscriber.'<\/span>),\r\n    (0x10000000, 'Converts xml columns to ntext on the Subscriber.'<\/span>),\r\n    (0x20000000, 'Converts large object data types (nvarchar(max), varchar(max), and varbinary(max)) introduced in SQL Server 2005 to data types that are supported on SQL Server 2000. For information about how these types are mapped, see the "Mapping New Data Types for Earlier Versions" section in Using Multiple Versions of SQL Server in a Replication Topology.'<\/span>),\r\n    (0x40000000, 'Replicate permissions.'<\/span>),\r\n    (0x80000000, 'Attempt to drop dependencies to any objects that are not part of the publication.'<\/span>),\r\n    (0x100000000, 'Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers. Replicating tables that have FILESTREAM columns to SQL Server 2000 Subscribers is not supported, regardless of how this schema option is set. '<\/span>),\r\n    (0x200000000, 'Converts date and time data types (date, time, datetimeoffset, and datetime2) introduced in SQL Server 2008 to data types that are supported on earlier versions of SQL Server. For information about how these types are mapped, see the "Mapping New Data Types for Earlier Versions" section in Using Multiple Versions of SQL Server in a Replication Topology.'<\/span>),\r\n    (0x400000000, 'Replicates the compression option for data and indexes. For more information, see Creating Compressed Tables and Indexes.'<\/span>),\r\n    (0x800000000, 'Set this option to store FILESTREAM data on its own filegroup at the Subscriber. If this option is not set, FILESTREAM data is stored on the default filegroup. Replication does not create filegroups, therefore, if you set this option, you must create the filegroup before you apply the snapshot at the Subscriber. For more information about how to create objects before you apply the snapshot, see Executing Scripts Before and After the Snapshot Is Applied.'<\/span>),\r\n    (0x1000000000, 'Converts common language runtime (CLR) user-defined types (UDTs) that are larger than 8000 bytes to varbinary(max) so that columns of type UDT can be replicated to Subscribers that are running SQL Server 2005.'<\/span>),\r\n    (0x2000000000, 'Converts the hierarchyid data type to varbinary(max) so that columns of type hierarchyid can be replicated to Subscribers that are running SQL Server 2005. For more information about how to use hierarchyid columns in replicated tables, see hierarchyid (Transact-SQL).'<\/span>),\r\n    (0x4000000000, 'Replicates any filtered indexes on the table. For more information about filtered indexes, see Filtered Index Design Guidelines.'<\/span>),\r\n    (0x8000000000, 'Converts the geography and geometry data types to varbinary(max) so that columns of these types can be replicated to Subscribers that are running SQL Server 2005.'<\/span>),\r\n    (0x10000000000, 'Replicates indexes on columns of type geography and geometry.'<\/span>),\r\n    (0x20000000000, 'Replicates the SPARSE attribute for columns. For more information about this attribute, see Using Sparse Columns.'<\/span>)\r\n\r\nDECLARE<\/span> @schema_option VARBINARY(8) = 0x000000000807509F;\r\n\r\nSELECT<\/span> CONVERT<\/span>(INT<\/span>, @schema_option, 1) & CONVERT<\/span>(INT<\/span>,value<\/span>,1), *\r\nFROM<\/span> @options\r\nWHERE<\/span> CONVERT<\/span>(INT<\/span>, @schema_option, 1) & CONVERT<\/span>(INT<\/span>,value<\/span>,1) <> 0;\r\n\r\nSET<\/span> @schema_option = 0x000000000803509F;\r\n\r\nSELECT<\/span> CONVERT<\/span>(INT<\/span>, @schema_option, 1) & CONVERT<\/span>(INT<\/span>,value<\/span>,1), *\r\nFROM<\/span> @options\r\nWHERE<\/span> CONVERT<\/span>(INT<\/span>, @schema_option, 1) & CONVERT<\/span>(INT<\/span>,value<\/span>,1) <> 0;<\/pre>\n