SQL Server Replication Snapshot: Drop and Recreate vs Truncate, NC Indexes, and Why the Default Will Hurt You

---

There is a replication configuration decision that production DBAs make without fully understanding its performance consequences until a large subscriber table starts causing problems on every daily reinit. The decision is what happens to the subscriber table when a snapshot is applied. SQL Server calls this the pre-creation command and it defaults to DROP, meaning the subscriber table is dropped and completely recreated from scratch on every snapshot application.

On a small subscriber that default is invisible. On a large subscriber with non-clustered indexes configured to copy from the publisher, that default means a full index rebuild on every reinit. Not an incremental update. Not a background operation. A complete rebuild of every index, on every table, while the snapshot agent is applying data, every day the snapshot runs.

This article explains exactly what DROP and TRUNCATE do, why enabling non-clustered index copying combined with DROP creates the performance problem, how to identify the current configuration in any environment, and how to change it correctly. All technical claims are verified against Microsoft Learn documentation.

1 The Four Pre-Creation Commands: What Microsoft Documents Beginner

The pre-creation command controls what SQL Server does to the subscriber table immediately before applying a snapshot. The parameter is @pre_creation_cmd in sp_addarticle and sp_changearticle. According to Microsoft documentation via the sysarticles system table, four values exist:

Value	Integer Code	What Happens at the Subscriber	Default?
none	0	No action taken. The snapshot is applied to whatever data exists. Rows are inserted on top of existing data. Used only when manually managing subscriber state.	No
drop	1	The destination table is dropped entirely. The snapshot scripts recreate the table structure from scratch, then load all data. All associated objects (indexes, permissions, constraints) are recreated from the snapshot scripts only.	YES
delete	2	Individual rows are deleted using DELETE statements, respecting any row filter WHERE clause. Used for horizontally filtered articles where TRUNCATE would remove rows outside the filter scope.	No
truncate	3	TRUNCATE TABLE is executed to remove all rows. The table structure remains in place. Existing indexes, subscriber-specific permissions, and any subscriber-only objects are preserved. Data is then loaded into the existing structure.	No

2 What DROP Actually Does: The Full Sequence Beginner

When the Distribution Agent applies a snapshot to a subscriber article configured with pre_creation_cmd = 'drop', the following sequence occurs at the subscriber:

1. The Distribution Agent issues DROP TABLE [subscriber_table]. The entire table including all data, all indexes, all constraints, all statistics, and all permissions is destroyed.
2. The snapshot scripts execute the CREATE TABLE statement from the snapshot to recreate the table structure. This includes the clustered index if one was present on the publisher and was included in the schema option.
3. If non-clustered indexes are configured to be copied (schema_option includes the NC index bit), the snapshot scripts execute CREATE INDEX for each NC index after the table is created but before data is loaded. This is the setup phase.
4. The bulk data load begins. All rows from the publisher table are applied to the now-empty subscriber table. With NC indexes already in place, SQL Server maintains the B-tree for each index as rows are inserted one by one (or in batches), which is significantly slower than loading data first and building indexes afterward.
5. Any post-snapshot scripts execute if configured.

3 What TRUNCATE Actually Does: The Full Sequence Intermediate

When the Distribution Agent applies a snapshot with pre_creation_cmd = 'truncate', the sequence is fundamentally different:

1. The Distribution Agent issues TRUNCATE TABLE [subscriber_table]. All rows are removed instantly as a single minimally-logged operation. The table structure, all indexes, all permissions, all statistics metadata, and all subscriber-specific objects remain in place.
2. No CREATE TABLE statement executes. No CREATE INDEX statements execute. The snapshot scripts skip object creation entirely because the objects already exist.
3. The bulk data load begins. Rows are inserted into the existing table structure. Existing indexes are updated as data loads. Existing subscriber-specific indexes are also updated.
4. Post-snapshot scripts execute if configured.

The critical difference is steps 2 and 3. With TRUNCATE there is no object recreation phase and no index build phase. The snapshot application becomes purely a data load operation rather than a schema recreation operation plus a data load operation.

4 How Non-Clustered Index Replication Works Intermediate

The decision to copy non-clustered indexes to the subscriber is controlled by the @schema_option bitmask in sp_addarticle. This is a separate setting from pre_creation_cmd and the two interact in ways that are not obvious from reading either setting in isolation.

When the NC index bit is set in schema_option, the Snapshot Agent generates CREATE INDEX statements for every non-clustered index on the publisher table and includes them in the snapshot scripts. These scripts are stored in the snapshot folder alongside the data files. When the Distribution Agent applies the snapshot, it executes these CREATE INDEX scripts as part of the snapshot application process.

This also means that if an NC index is added to the publisher table after the subscription is already running, that index does not automatically appear on the subscriber. A reinitialization or a manual CREATE INDEX on the subscriber is required. This is a common surprise for DBAs who add indexes to publisher tables expecting them to flow downstream.

5 DROP Plus NC Indexes: Why This Combination Causes a Full Rebuild on Every Snapshot Intermediate

The performance problem David described is the direct consequence of running pre_creation_cmd = 'drop' (the default) combined with NC index copying enabled in schema_option. Here is the exact sequence on every daily reinit and why each step adds cost:

Step	Operation	Cost on Large Tables
1	DROP TABLE at subscriber	Instant for the operation itself, but acquires exclusive lock. All queries against the subscriber table fail from this point.
2	CREATE TABLE from snapshot script	Negligible. Just DDL.
3	CREATE INDEX for each NC index (from snapshot scripts, before data load)	On an empty table this is also fast, but these index structures must be maintained during the data load in step 4.
4	Bulk data load into the now-indexed table	Every row insertion maintains all NC index B-trees. On a 50-million-row table with 4 NC indexes, this means 200 million index entry insertions with associated page splits. This is the full performance cost of having indexes present during a bulk load.
5	Index rebuild or optimize (if needed)	After the insertion-heavy load, fragmentation in the NC indexes is likely high. If an index maintenance job runs post-snapshot, this adds another full rebuild on top of the already expensive load.

The practical consequence is exactly what David encountered: a daily snapshot that was completing in a reasonable time starts taking significantly longer as the subscriber table grows. Eventually the snapshot application window extends past what is acceptable for the business, blocking subscriber queries for longer periods. The development team or business stakeholders see slow query performance on the subscriber and escalate, but the root cause is the combination of these two settings working against each other.

The fix is straightforward: change pre_creation_cmd to 'truncate'. With TRUNCATE, the table structure and indexes already exist. The snapshot application becomes a pure data load into an existing indexed structure, with index entries updated incrementally as rows are added. The indexes are not rebuilt from scratch. The total work is substantially less.

6 What Gets Silently Destroyed by DROP That Nobody Mentions Intermediate

The performance problem from NC index rebuilds is the most visible consequence of the DROP default but it is not the only one. Three other consequences occur silently and are frequently discovered only after something stops working.

Subscriber-specific indexes

In many production replication environments, the subscriber serves a different workload from the publisher. A subscriber used for reporting might have additional NC indexes tuned for large analytical queries that are not needed on the OLTP publisher. When pre_creation_cmd = 'drop' and a reinit occurs, those subscriber-specific indexes are dropped. They do not come back because they are not in the publication’s schema_option. They must be manually recreated after every reinit. If nobody knows this is happening, the subscriber gradually loses its indexes over time as reinits occur.

Table-level permissions

Microsoft documentation explicitly states that by default, when objects at the subscriber are dropped and recreated during reinitialization, all granted permissions for those objects are dropped. If the subscriber application connects as a role or user that has been granted SELECT, INSERT, or other permissions on the subscriber table, those permissions are silently revoked on every reinit. The application may work correctly for a period and then fail unexpectedly after the next snapshot is applied.

Subscriber-side triggers

Some subscriber implementations use triggers for audit logging, cascaded operations, or data transformation specific to the subscriber environment. Triggers are not part of the publication schema by default and are not recreated by the snapshot. A DROP reinit destroys them. The trigger logic stops executing silently. No error appears in replication monitoring. The trigger is simply gone.

7 Script 1: Audit Current Pre-Creation Commands Across All Articles Intermediate

Run this on the publisher database to see the current pre_creation_cmd setting for every article in every publication. Compare what each article is set to against what it should be based on the decision matrix in Section 11.

-- Audit pre_creation_cmd settings across all articles
-- Run on the PUBLISHER in the publication database
-- pre_creation_cmd values: 0=None, 1=DROP (default), 2=DELETE, 3=TRUNCATE

SELECT
    p.name                                              AS PublicationName,
    a.name                                              AS ArticleName,
    a.dest_table                                        AS DestinationTable,
    CASE a.pre_creation_cmd
        WHEN 0 THEN 'NONE    -- No action before snapshot applied'
        WHEN 1 THEN 'DROP    -- DEFAULT: drops and recreates table on every reinit'
        WHEN 2 THEN 'DELETE  -- Deletes rows (use only with row filters)'
        WHEN 3 THEN 'TRUNCATE -- Recommended: clears data, preserves structure'
    END                                                 AS PreCreationCmd,
    a.pre_creation_cmd                                  AS PreCreationCmdInt,
    -- Flag articles still using the default DROP
    CASE a.pre_creation_cmd
        WHEN 1 THEN 'REVIEW: Still using DROP default'
        ELSE 'OK'
    END                                                 AS ActionNeeded
FROM dbo.sysarticles                                    a
JOIN dbo.syspublications                                p
    ON a.pubid = p.pubid
ORDER BY
    p.name,
    -- Show DROP articles at top since they need review
    a.pre_creation_cmd DESC,
    a.name;

-- Summary count
SELECT
    CASE pre_creation_cmd
        WHEN 0 THEN 'NONE'
        WHEN 1 THEN 'DROP (default - review these)'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'TRUNCATE (recommended)'
    END                                                 AS Setting,
    COUNT(*)                                            AS ArticleCount
FROM dbo.sysarticles
GROUP BY pre_creation_cmd
ORDER BY pre_creation_cmd;

8 Script 2: Identify the NC Index Replication Setting Intermediate

The schema_option bitmask controls which schema elements are included in the snapshot scripts. The NC index bit is 0x0000000000000040. This script identifies which articles have NC index copying enabled, which is the other half of the performance problem when combined with DROP.

-- Identify which articles have NC index copying enabled
-- and cross-reference with pre_creation_cmd to find the dangerous combination
-- Run on the PUBLISHER in the publication database

SELECT
    p.name                                              AS PublicationName,
    a.name                                              AS ArticleName,
    a.dest_table                                        AS DestinationTable,
    CASE a.pre_creation_cmd
        WHEN 0 THEN 'NONE'
        WHEN 1 THEN 'DROP'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'TRUNCATE'
    END                                                 AS PreCreationCmd,
    -- Check if NC index copying is enabled
    -- Bit 0x40 in schema_option = Copy non-clustered indexes
    CASE
        WHEN (a.schema_option & 0x40) = 0x40
        THEN 'YES - NC indexes will be scripted in snapshot'
        ELSE 'NO  - NC indexes not included in snapshot'
    END                                                 AS NCIndexCopyEnabled,
    -- Check if clustered index copying is enabled
    CASE
        WHEN (a.schema_option & 0x10) = 0x10
        THEN 'YES'
        ELSE 'NO'
    END                                                 AS ClusteredIndexCopyEnabled,
    -- Flag the dangerous combination
    CASE
        WHEN a.pre_creation_cmd = 1           -- DROP
         AND (a.schema_option & 0x40) = 0x40 -- AND NC indexes enabled
        THEN 'PERFORMANCE RISK: DROP + NC indexes = full rebuild on every reinit'
        WHEN a.pre_creation_cmd = 1
        THEN 'REVIEW: Using DROP default'
        ELSE 'OK'
    END                                                 AS RiskAssessment,
    -- Show the raw schema_option value for reference
    master.dbo.fn_varbintohexstr(
        CAST(a.schema_option AS VARBINARY(8))
    )                                                   AS SchemaOptionHex
FROM dbo.sysarticles                                    a
JOIN dbo.syspublications                                p
    ON a.pubid = p.pubid
ORDER BY
    -- Show highest risk articles first
    CASE
        WHEN a.pre_creation_cmd = 1 AND (a.schema_option & 0x40) = 0x40 THEN 1
        WHEN a.pre_creation_cmd = 1 THEN 2
        ELSE 3
    END,
    p.name, a.name;

9 Script 3: Change Pre-Creation Command on Existing Articles Advanced

Changing pre_creation_cmd on an existing article requires sp_changearticle. The change takes effect the next time the snapshot is applied. A new snapshot does not need to be generated purely to change this setting: the Distribution Agent reads the current article metadata when it processes the next snapshot.

-- Change pre_creation_cmd from DROP to TRUNCATE on a single article
-- Run on the PUBLISHER in the publication database

EXEC sp_changearticle
    @publication  = N'YourPublicationName',   -- exact publication name
    @article      = N'YourArticleName',        -- exact article name
    @property     = N'pre_creation_cmd',       -- the property to change
    @value        = N'truncate',               -- 'none', 'delete', or 'truncate'
    @force_invalidate_snapshot = 1;            -- required: marks current snapshot as invalid

-- Verify the change was applied
SELECT
    a.name          AS ArticleName,
    a.dest_table    AS DestinationTable,
    CASE a.pre_creation_cmd
        WHEN 0 THEN 'NONE'
        WHEN 1 THEN 'DROP'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'TRUNCATE'
    END             AS PreCreationCmd
FROM dbo.sysarticles                a
JOIN dbo.syspublications            p ON a.pubid = p.pubid
WHERE p.name = N'YourPublicationName'
  AND a.name = N'YourArticleName';

-- Change pre_creation_cmd for ALL articles in a publication at once
-- Use with caution: verify each article individually first
-- Run on the PUBLISHER in the publication database

DECLARE @publication NVARCHAR(128) = N'YourPublicationName';

DECLARE @article     NVARCHAR(128);
DECLARE @cmd         NVARCHAR(20);

DECLARE article_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT a.name,
           CASE a.pre_creation_cmd
               WHEN 1 THEN 'DROP'
               ELSE 'NOT_DROP'
           END
    FROM dbo.sysarticles    a
    JOIN dbo.syspublications p ON a.pubid = p.pubid
    WHERE p.name = @publication
      AND a.pre_creation_cmd = 1;  -- only change articles currently set to DROP

OPEN article_cursor;
FETCH NEXT FROM article_cursor INTO @article, @cmd;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Changing article: ' + @article + ' from DROP to TRUNCATE';

    EXEC sp_changearticle
        @publication              = @publication,
        @article                  = @article,
        @property                 = N'pre_creation_cmd',
        @value                    = N'truncate',
        @force_invalidate_snapshot = 1;

    FETCH NEXT FROM article_cursor INTO @article, @cmd;
END

CLOSE article_cursor;
DEALLOCATE article_cursor;

PRINT 'All DROP articles changed to TRUNCATE in publication: ' + @publication;

-- After running, generate a new snapshot for the changes to take effect:
-- EXEC sp_startpublication_snapshot @publication = N'YourPublicationName'

-- Verify current settings for all articles after the change
SELECT
    a.name                              AS ArticleName,
    CASE a.pre_creation_cmd
        WHEN 0 THEN 'NONE'
        WHEN 1 THEN 'DROP'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'TRUNCATE'
    END                                 AS PreCreationCmd
FROM dbo.sysarticles    a
JOIN dbo.syspublications p ON a.pubid = p.pubid
WHERE p.name = N'YourPublicationName'
ORDER BY a.name;

10 Changing the Setting Through SSMS Beginner

The same change can be made through SSMS without T-SQL if that is the preferred approach.

1. In SSMS, expand the Replication folder, then Local Publications.
2. Right-click the publication and select Properties.
3. Click the Articles page in the left panel.
4. Select the article to modify in the article list.
5. Click Article Properties, then click Set Properties of Highlighted Table Article.
6. In the Article Properties dialog, look under the section titled Destination Object.
7. Find the property labeled Action if name is in use.
8. The default value showing is Drop existing object and create a new one.
9. Change this to Truncate all data in the existing object.
10. Click OK. SSMS will warn that a new snapshot is required. Accept and generate a new snapshot before the next scheduled reinit.

11 Decision Matrix: When DROP Is Correct and When TRUNCATE Is Correct Intermediate

TRUNCATE is the right choice for most production replication scenarios where the goal is ongoing data synchronization. DROP is correct for specific circumstances where it provides capabilities TRUNCATE cannot.

Scenario	Recommended Setting	Reason
Initial subscription setup for a new subscriber	Either	For first-time initialization, the table does not exist at the subscriber yet. DROP creates it fresh. TRUNCATE requires the table to already exist (or be created by a pre-snapshot script). For brand-new subscribers, DROP is simpler.
Ongoing daily reinit, schema unchanged	TRUNCATE	No schema changes need to flow. TRUNCATE clears and reloads data without destroying the table structure. Fastest option. Preserves subscriber-specific indexes and permissions.
Schema change on publisher needs to flow to subscriber	DROP (temporarily)	TRUNCATE does not recreate the table, so schema differences between publisher and subscriber are not corrected by the snapshot. When a schema change must flow, switch to DROP, reinit, then switch back to TRUNCATE. This should be an exception, not the default.
Horizontally filtered article with row filters	DELETE	TRUNCATE removes all rows regardless of the filter definition. For articles where only a subset of rows is replicated based on a WHERE clause, DELETE removes only the rows within the filter scope, preventing data loss of rows outside the filter on the subscriber. DROP also handles this correctly but with the full recreation overhead.
Subscriber has custom indexes not in the publication	Never DROP	DROP silently destroys subscriber-specific indexes on every reinit. They must be manually recreated. TRUNCATE preserves all existing subscriber objects.
Subscriber table has permissions granted to application roles	Never DROP without a post-snapshot script	DROP removes all granted permissions. If DROP must be used, a post-snapshot script that regrants the necessary permissions is mandatory. TRUNCATE preserves existing permissions.
Large table, NC indexes enabled, daily reinit schedule	Never DROP	This is the specific combination that causes full index rebuilds on every snapshot application. Switch to TRUNCATE immediately.

12 The TRUNCATE Limitation for Filtered Articles Intermediate

TRUNCATE cannot be used with articles that have horizontal row filters applied. A horizontal filter restricts which rows are included in the publication using a WHERE clause in sp_addarticle @filter_clause. When a filtered article is reinitialized with TRUNCATE, the entire subscriber table is cleared including rows that fall outside the filter definition. This data loss is permanent.

The correct setting for filtered articles is pre_creation_cmd = 'delete'. The DELETE option removes rows using the same filter criteria applied to the article, meaning only rows within the publication scope are removed before the snapshot data is loaded. Rows on the subscriber outside the filter scope are preserved.

-- Identify filtered articles where TRUNCATE would be incorrect
-- These articles must use DELETE, not TRUNCATE
-- Run on the PUBLISHER in the publication database

SELECT
    p.name                          AS PublicationName,
    a.name                          AS ArticleName,
    a.dest_table                    AS DestinationTable,
    CASE a.pre_creation_cmd
        WHEN 0 THEN 'NONE'
        WHEN 1 THEN 'DROP'
        WHEN 2 THEN 'DELETE'
        WHEN 3 THEN 'TRUNCATE'
    END                             AS CurrentPreCreationCmd,
    a.filter_clause                 AS RowFilterClause,
    CASE
        WHEN a.filter_clause IS NOT NULL AND a.pre_creation_cmd = 3
        THEN 'INCORRECT: TRUNCATE on a filtered article will cause data loss'
        WHEN a.filter_clause IS NOT NULL AND a.pre_creation_cmd = 2
        THEN 'CORRECT: DELETE respects the row filter'
        WHEN a.filter_clause IS NOT NULL AND a.pre_creation_cmd = 1
        THEN 'REVIEW: DROP works but high overhead'
        WHEN a.filter_clause IS NOT NULL
        THEN 'REVIEW: Filtered article - verify setting'
        ELSE 'No row filter - TRUNCATE is safe'
    END                             AS Assessment
FROM dbo.sysarticles                a
JOIN dbo.syspublications            p ON a.pubid = p.pubid
ORDER BY
    CASE WHEN a.filter_clause IS NOT NULL THEN 0 ELSE 1 END,
    p.name, a.name;

13 Subscriber-Only Indexes: The Right Architecture Advanced

The better long-term design for subscribers that need indexes different from the publisher is to disable NC index copying in the schema_option entirely and manage subscriber indexes independently. This approach has several advantages.

Publisher and subscriber workloads are frequently different. Publishers handle OLTP insert and update patterns. Subscribers often handle read-heavy reporting, analytical queries, or lookup operations with different access patterns. The optimal index set for the publisher is often not the optimal index set for the subscriber. Replicating publisher indexes to the subscriber forces the subscriber to maintain indexes that may not serve its actual workload.

Managing subscriber indexes independently means: create only the indexes the subscriber workload actually needs, document them separately from the publication configuration, and ensure any post-snapshot scripts recreate them if DROP reinitialization ever becomes necessary for a schema change event.

-- Check which articles have NC index copying enabled
-- and consider whether those indexes actually serve the subscriber workload
-- Run on the PUBLISHER in the publication database

SELECT
    p.name                                  AS PublicationName,
    a.name                                  AS ArticleName,
    a.dest_table                            AS DestinationTable,
    CASE
        WHEN (a.schema_option & 0x40) = 0x40
        THEN 'YES - publisher NC indexes scripted into snapshot'
        ELSE 'NO  - subscriber manages its own NC indexes'
    END                                     AS NCIndexesReplicated,
    CASE
        WHEN (a.schema_option & 0x40) = 0x40 AND a.pre_creation_cmd = 1
        THEN 'RISK: full NC rebuild on every reinit'
        WHEN (a.schema_option & 0x40) = 0x40 AND a.pre_creation_cmd = 3
        THEN 'OK: TRUNCATE preserves existing, incremental update during load'
        WHEN (a.schema_option & 0x40) != 0x40
        THEN 'INDEPENDENT: subscriber manages its own indexes'
        ELSE 'REVIEW'
    END                                     AS Assessment
FROM dbo.sysarticles                        a
JOIN dbo.syspublications                    p ON a.pubid = p.pubid
ORDER BY p.name, a.name;

-- To disable NC index copying on an existing article:
-- This does NOT drop existing NC indexes on the subscriber.
-- It only means future snapshots will not script them.
-- The schema_option value must be calculated by clearing bit 0x40.
-- Use sp_changearticle to update:

/*
EXEC sp_changearticle
    @publication               = N'YourPublicationName',
    @article                   = N'YourArticleName',
    @property                  = N'schema_option',
    @value                     = N'0x0000000008034FDF',  -- your current value minus bit 0x40
    @force_invalidate_snapshot  = 1;

-- Calculate the new schema_option by getting the current value first:
SELECT master.dbo.fn_varbintohexstr(CAST(schema_option AS VARBINARY(8)))
FROM dbo.sysarticles
WHERE name = N'YourArticleName';
-- Then subtract 0x40 from that value.
*/

References

• Microsoft Docs: sp_addarticle (Transact-SQL) – @pre_creation_cmd parameter
• Microsoft Docs: sp_changearticle (Transact-SQL) – changing article properties
• Microsoft Docs: sysarticles (Transact-SQL) – pre_creation_cmd column values
• Microsoft Docs: Frequently Asked Questions for Replication Administrators
• Microsoft Docs: Initialize a Subscription with a Snapshot
• Microsoft Docs: Specify Schema Options
• Microsoft Q&A: Replication of Non-Clustered Indexes from Publisher to Subscriber
• Microsoft Q&A: Replication Indexes – Index operations not replicated as ongoing transactions
• SQLYARD: SQL Server Transactional Replication Performance Tuning: Log Reader and Distribution Agent
• SQLYARD: SQL Server Replication Jobs Explained