How to fix Error 8196 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 8196 – Duplicate Column Specified as ROWGUIDCOL

Why This Error Happens

Error 8196 occurs when a CREATE TABLE or ALTER TABLE statement attempts to designate more than one column in the same table with the ROWGUIDCOL property. SQL Server 2025 enforces a strict rule that only a single column per table may carry this attribute, since ROWGUIDCOL is used to flag the column that uniquely identifies rows via a GUID for replication and merge scenarios. This typically happens due to copy-paste errors in T-SQL scripts, legacy migration scripts that redefine an existing ROWGUIDCOL column without dropping the property first, or schema merge conflicts when combining table definitions from multiple sources.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Columns

  • Run the following query to inspect which columns currently have the ROWGUIDCOL property set on the affected table:
  • SELECT name, is_rowguidcol FROM sys.columns WHERE object_id = OBJECT_ID('YourSchema.YourTableName') AND is_rowguidcol = 1;
  • Note the column names returned — if more than one row appears, you have confirmed the source of Error 8196.

Step 2: Remove the ROWGUIDCOL Property from the Extra Column

  • Use ALTER TABLE to drop the ROWGUIDCOL designation from the duplicate or incorrect column rather than dropping the column itself.
  • Example syntax: ALTER TABLE YourSchema.YourTableName ALTER COLUMN DuplicateColumnName ADD ROWGUIDCOL is not valid for removal — instead, you must drop and recreate the column constraint using sp_help 'YourTableName' to review constraints, then recreate the column without the property.
  • If the column was defined inline in a CREATE TABLE script, manually edit the script in SQL Server Management Studio (SSMS) located under C:\Program Files (x86)\Microsoft SQL Server Management Studio 21\Common7\IDE\Ssms.exe and remove the extra ROWGUIDCOL keyword.

Step 3: Correct the Source Script or Migration File

  • Open the original .sql deployment script, typically found in your project repository or under C:\SQLScripts\Deployment\.
  • Search for all instances of the keyword ROWGUIDCOL using Ctrl+F in SSMS or VS Code.
  • Ensure only one column definition (commonly a uniqueidentifier column named RowGuid or GUIDColumn) includes the ROWGUIDCOL keyword, then save and re-run the script.

Step 4: Re-run the Table Creation or Alteration Statement

  • Execute the corrected script against the target database in SSMS or via sqlcmd:
  • sqlcmd -S YourServerName -d YourDatabaseName -i "C:\SQLScripts\Deployment\FixedScript.sql"
  • Confirm the script completes without raising Error 8196 in the Messages pane.

Alternative or Verification Steps

  • Verify the fix by re-running the diagnostic query from Step 1 — it should now return exactly one row with is_rowguidcol = 1, or zero rows if no ROWGUIDCOL column is required.
  • If the error persists after editing the script, drop the table entirely (if no production data exists) and recreate it from a clean, validated template to rule out hidden duplicate definitions in linked synonyms or views.