
	Home > SQL Server Tips > Database Administration > Managing identity columns with replication in SQL Server

SQL Server Tips:

EMAIL THIS

TIPS & NEWSLETTERS TOPICS

DATABASE ADMINISTRATION

Managing identity columns with replication in SQL Server

Baya Pavliashvili
08.03.2007
Rating: -3.75- (out of 5)

Expert advice on database administration

Digg This! StumbleUpon Del.icio.us

There are some issues associated with managing identity columns with replication in your SQL Server database. As with previous releases of software, SQL Server 2005 requires that database administrators use special care when replicating tables with identity columns.

First, let me offer a little background about identity columns to help you understand why they're different from any other column with a numeric data type. Identity columns have monotonously increasing numeric values that SQL Server assigns to each row automatically when the row is created. Normally, identity columns have INT or BIGINT data types, although you could use other numeric data types as well. By default, SQL Server seeds such columns at 1 and increments by 1, but you can change to the seed and increment of your liking.

Identity columns are good candidates for a table's primary key because they're unique for each row and cannot be updated without deleting and re-creating the row.

SQL Server replication scenarios

To make this tip easier to follow, let's imagine we're trying to replicate a table with the following schema:

Click here to view schema.

Next, let's

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	SQL Server Replication
	Top load balancing methods for SQL Server
	New replication features in SQL Server 2008 and what they mean to you
	SQL Server database replication tutorial
	Licensing a standby server for SQL Server replication
	Upgrade live applications to SQL Server 2005 for high availability
	Tool to synchronize two SQL Server databases
	Simplify SQL Server replication
	Replication techniques in SQL Server
	Podcast: SQL Server high availability options
	Podcast: SQL Server replication basics

Microsoft SQL Server 2005 (Yukon)

SQL Server Reporting Services Fast Guide

SQL Server Service Broker Tutorial and Reference Guide

Tips for tuning SQL Server 2005 to improve reporting performance

SQL Server consolidation: Why it's an optimization technique

Parent-child dimensions in SQL Server 2005 with Analysis Services MDX

Enforcing data integrity in a SQL Server database

SSIS error message due to installation problem on SQL Server 2005

Should you upgrade to SQL Server 2005 or SQL Server 2008?

Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005

How to configure Database Mail in SQL Server 2005 to send mail

Microsoft SQL Server 2005 (Yukon) Research

Microsoft SQL Server Performance Monitoring and Tuning

Performance implications of transaction log autogrowth in SQL Server

The short course on how SQL Server really works

Determining the source of full transaction logs in SQL Server

Improving SQL Server full-text search performance

New GROUP BY option provides better data control in SQL Server 2008

Microsoft SQL Server 2008 Resource Governor primer

Examining data files when SQL Server tempdb is full

Testing transaction log autogrowth behavior in SQL Server

Meeting business needs with SQL Server full-text search

Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS

Terms from Whatis.com − the technology online dictionary

replication (SearchSQLServer.com)

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

consider various replication scenarios where this table could be used:

Options to manage identity seeds for replicated tables

If you must use updateable subscriptions, you need to define identity ranges on publisher and subscriber servers to avoid the creation of duplicate primary keys.

SQL Server 2005 supports several options for managing identity seed ranges. Note that you can set these options only when you first add the table article to the publication. If you need to change the identity management options, you must remove the article from the publication and add it back. Depending on the table size and your application's availability requirements, dropping and re-adding articles on demand might not be an option. Be sure to carefully choose the proper option.

Here is the summary of identity management options:

Summary

In this tip, I discussed various scenarios for replicating tables that have identity columns and options for identity management. You don't have to ditch identity columns to use replication, just handle them with care.

[TABLE]

Rate this Tip
To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member.

DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

SQL Server Development - .NET, C#, T-SQL, Visual Basic

SEARCH

TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.


TechTarget Corporate Web Site \| Media Kits \| Site Map All Rights Reserved, Copyright 2005 - 2009, TechTarget \| Read our Privacy Policy