Path: utzoo!news-server.csri.toronto.edu!cs.utexas.edu!asuvax!ncar!hsdndev!cmcl2!uupsi!netfs.dnd.ca!ncs.dnd.ca!ncs.dnd.ca!rpburry
From: rpburry@ncs.dnd.ca (Paul Burry)
Newsgroups: comp.databases
Subject: Generating unique keys in SYBASE. Whats the best method?
Message-ID: <1991Mar14.123156.17547@ncs.dnd.ca>
Date: 14 Mar 91 12:31:56 GMT
Sender: rpburry@ncs.dnd.ca (Paul Burry)
Organization: Dept of National Defence
Lines: 38


What is the best mechanism for generating unique keys in SYBASE.

Are there any better (faster, more concurrency) methods than:

	begin transaction
		/** update (and lock) the key control table **/
		update	control_table
		set	key = key + 1
		where	row = 1

		/** grab the key value **/
		select	@key=key
		from	control_table
		where	row = 1	
	commit transaction

	/** if the following tranaction is rolled back, then the	**/
	/** unique key created will be unused...			**/
	begin transaction
		/** use the key **/
		insert into the_table
		values (@key, ...)

		...
	commit transaction

forgive the syntax errors.  I hope you get the general idea.  Presumably
it is better to use a different control table for each table requiring
unique keys (to get around the page locking), and presumably splitting
the key generation and usage will get permit more concurency.

Any suggestions or comments??
-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Paul Burry			
Voice: (613)-991-7325		Internet: rpburry@ncs.dnd.ca
Fax:   (613)-991-7323		UUCP:	  ..!{uunet,cunews}!ncs.dnd.ca!rpburry