Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am about to split a large database into a bunch of federated instances. Currently, most of the primary keys are auto-generated identity ints. Obviously, that's not going to work in a federated setup.

I've read people using auto-generated GUIDs to replace the ints, but that data type has well-known performance sapping problems on its own.

What are some of the strategies I can use for having unique values for primary keys across federated members?

share|improve this question
3  
See this for a start: brentozar.com/archive/2012/09/trouble-keys –  Jon Seigel Nov 1 '12 at 22:31
add comment

1 Answer

One alternative is to perform a hash on some existing value or combination of values, for instance the user's login name and autogenerated identity (int):

SELECT 
    CONVERT(NVARCHAR(40), 
        HashBytes('SHA1', UserName + CAST(UserId AS VARCHAR)) ,2)
    AS FederatedIdentity
FROM Users

This way the value would be consistent across databases. You could persist the hash in a table; I usually create a view to work with generated columns like this.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.