Transfering rows from a development database to a production database can quickly become a nightmare.
To avoid this you should think your data model to deal with this new constraint: each row must be uniquely identified by a value which does not change in time and can be recomputed in a similary table in another database at anytime.
This unique value can be a hash. A hash is an asymetric cryptography function which always generates the same value when the same input is specified, and has a 99,9% chance to generate a different values with different inputs.
That means to generate our small hash to identify a row, we should find a primary key on our table which does not depend on an identity column. We can not use variable date fields nor uniqueidentifier fields as they are generated on row creation and will be different if we create a row with the same data on another database. This primary key can be a composite string key made of parts of the other table columns.
Using the HashBytes function with the SHA1 hash function in SQL 2005 generates a binary(20) result. With SQL 2000 you may use the checksum() function.
Example:
CREATE TABLE [dbo].[ITEM](
[ITEMID] int IDENTITY(1,1) NOT NULL,
[NAME] nvarchar(50) NOT NULL,
[DESCRIPTION] nvarchar(200) NOT NULL,
[HASH] AS (hashbytes('SHA1',[NAME]+[DESCRIPTION])),
CONSTRAINT [PK_ITEM_ITEMID] PRIMARY KEY CLUSTERED
(
[ITEMID] ASC
),
CONSTRAINT [IX_ITEM_HASH] UNIQUE NONCLUSTERED
(
[HASH] ASC
))
Limitation: HashBytes input string is limited to 8000 bytes.
Tip: you may use the undocumented function master
.dbo.fn_varbintohexstr(binary hash value) to convert the binary result to a string prefixed with 0x.