InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:13:58 GMT20http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Paul White NZ (7/15/2010)[/b][hr][quote][b]Gianluca Sartori (7/15/2010)[/b][hr]...but consider that this solution can lead to very extensive row locks if run inside transactions...[/quote]Yes that's one of the things to watch out for, though it's not limited to row locks of course.There's nothing wrong with using a CLR function to work around this (the EXTERNAL_ACCESS permission set is required, but that's not something to worry about). The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.Paul[/quote]Another solution is to have a built-in "Increment" on the NextID proc and write code to know the correct answers ahead of time. No sense in a sequence table requiring RBAR.Thu, 15 Jul 2010 13:56:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxI'll chime in as well, Paul. People every day still use @@identity and forgo transaction log backups. If you've solved it, likely there are a few thousand people out there that don't understand it.Thu, 15 Jul 2010 08:44:30 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxThanks for the encouragement guys :-)I will revisit my decision.Thu, 15 Jul 2010 07:23:18 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxI strongly agree with Gianluca, Paul. Just because I don't have a clue what you meant (yet) doesn't mean that I won't need to know that the technique exists, and can refer back to your article when that happens. ;-)Thu, 15 Jul 2010 05:41:11 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Paul White NZ (7/15/2010)[/b][hr]The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.[/quote]That's the simplest way to achieve it. Unfortunately it works only in SQL 2008 and I had to code in CLR because we're still on SQL 2005.[quote]I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.[/quote]I strongly disagree. If it's well written (and I'm sure it is) it's always worth publishing. It's a subject that I see around sometimes and I think it can solve lots of problems. Autonomous transactions can save your life in many situations (sequences, logging, auditing) and other RDBMS vendors implement it natively (Oracle, DB2, Firebird, PostgreSQL...). It's a shame that MS hasn't decided yet to add this feature to SQL Server.Thu, 15 Jul 2010 04:28:31 GMTGianluca Sartorihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Gianluca Sartori (7/15/2010)[/b][hr]...but consider that this solution can lead to very extensive row locks if run inside transactions...[/quote]Yes that's one of the things to watch out for, though it's not limited to row locks of course.There's nothing wrong with using a CLR function to work around this (the EXTERNAL_ACCESS permission set is required, but that's not something to worry about). The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.PaulThu, 15 Jul 2010 03:54:07 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Paul White NZ (7/15/2010)[/b][hr][quote][b]mike 57299 (7/14/2010)[/b][hr]Paul, Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?Mike[/quote]Hey Mike,No - and that's the beauty of it: it's an atomic operation (single UPDATE statement) which occurs within its own implicit transaction, as normal.Paul[/quote]It's very similar to the sequence generator I use in my DB, but consider that this solution can lead to very extensive row locks if run inside transactions.Example:Open a new query in SSMS and run:[code]BEGIN TRANEXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;WAITFOR DELAY '00:02';COMMIT [/code]Open a new query and run the same code. This second query waits for the first transaction to end.If you don't mind gaps in the sequence, use a CLR procedure that implements autonomous transactions (not enlisted in the context connection's transaction) to generate the new id. Unfortunately, the permission to open a non-context connection in a CLR procedure must be granted marking the assembly as "external" or "unsafe".I'm writing an article on this subject and I hope I get it finished soon.Thu, 15 Jul 2010 02:37:22 GMTGianluca Sartorihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]mike 57299 (7/14/2010)[/b][hr]Paul, Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?Mike[/quote]Hey Mike,No - and that's the beauty of it: it's an atomic operation (single UPDATE statement) which occurs within its own implicit transaction, as normal.PaulThu, 15 Jul 2010 00:04:40 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxPaul, Do I need to do an explicit Begin Trans / Commit to make sure there is no duplicate sequence #'s?MikeWed, 14 Jul 2010 23:57:18 GMTmike 57299http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Jeff Moden (7/14/2010)[/b][hr]Classic T-SQL there Paul. That's how I resolved more than 640 deadlocks a day on a sequence table at a previous job.[/quote]Thanks, Jeff. It is easy to get Sequence Tables wrong, as your experience showed.Wed, 14 Jul 2010 23:02:21 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]Paul White NZ (7/14/2010)[/b][hr]This seems like a perfectly reasonable attempt to implement a Sequence Table to me.The code can be simplified:[code="sql"]-- Sequence table, holds next PK per tableCREATE TABLE dbo.GPK ( table_name SYSNAME NOT NULL PRIMARY KEY, next_value INTEGER NOT NULL );GO-- Example rowINSERT dbo.GPK (table_name, next_value) VALUES (N'dbo.MyTable', 0);GO-- Procedure to allocate a new PKCREATE PROCEDURE dbo.usp_GenPK(@table_name SYSNAME, @NewID INT OUTPUT)AS BEGIN UPDATE GPK SET @NewID = next_value = next_value + 1 WHERE table_name = @table_name;END;GO-- TestDECLARE @NewID INTEGER;EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;SELECT @NewID;[/code][/quote]Classic T-SQL therem Paul. That's how I resolved more than 640 deadlocks a day on a sequence table at a previous job.Wed, 14 Jul 2010 22:46:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxThis seems like a perfectly reasonable attempt to implement a Sequence Table to me.The code can be simplified:[code="sql"]-- Sequence table, holds next PK per tableCREATE TABLE dbo.GPK ( table_name SYSNAME NOT NULL PRIMARY KEY, next_value INTEGER NOT NULL );GO-- Example rowINSERT dbo.GPK (table_name, next_value) VALUES (N'dbo.MyTable', 0);GO-- Procedure to allocate a new PKCREATE PROCEDURE dbo.usp_GenPK(@table_name SYSNAME, @NewID INT OUTPUT)AS BEGIN UPDATE dbo.GPK SET @NewID = next_value = next_value + 1 WHERE table_name = @table_name;END;GO-- TestDECLARE @NewID INTEGER;EXEC dbo.usp_GenPK N'dbo.MyTable', @NewID OUTPUT;SELECT @NewID;[/code][i]edit: forgot the schema prefix[/i]Wed, 14 Jul 2010 22:05:04 GMTSQL Kiwihttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxChad, Thank you for your help. I will adapt the code to work for me. MikeWed, 14 Jul 2010 17:42:13 GMTmike 57299http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx@Chad:Since the posted table GPK has no unique constraint for gpk column you might end up with duplicates causing your code to fail.Either a unique constraint is required for the GPK column or a TOP 1 clause when assigning the value to @NewID.Regarding sympathy: mine is close to Zero. Honestly. The struggle to change the code is a one-time effort. To deal with the consequences (e.g. performance) is an all-time pain. I'd expect to have an INSERT sproc for each table that might get data inserted from more than one code section (maybe even for every DML statement - "it depends"). If not, it's time to change that, too.The OP deliberately puts the success of the product at risk just because of time constraints. And by success I'm talking about stuff like maintainability, performance, modification cost a.s.o.Wed, 14 Jul 2010 17:07:07 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxIn order to get the value, you need to grab the output from the stored proc, like this: [code]DECLARE @newID intEXEC @NewID = dbo.usp_GenPKSELECT @newID[/code]I used INTs since I don't have the ID data type...Grabbing a RETURN value this way is usually reserved for error codes, usually to tell the calling app/proc whether or not the procedure completed successfully. A better way to pass the data back is to use an OUTPUT parameter, like this:[code]IF OBJECT_ID ( 'dbo.usp_GenPK', 'P' ) IS NOT NULL DROP PROCEDURE dbo.usp_GenPK;GOCREATE PROCEDURE dbo.usp_GenPK( @table_name char(128) = '', @NewID int OUTPUT)AS BEGIN DECLARE @oTbl TABLE (pk int) DECLARE @return_ID int begin transaction GPK update GPK set gpk_ID = gpk_ID + 1 output INSERTED.gpk_ID INTO @oTbl WHERE gpk = 'HOLDER' SELECT @NewID = pk FROM @oTbl commit transaction GPK ENDDECLARE @newID intEXEC dbo.usp_GenPK '',@NewID OUTPUTSELECT @newID[/code]Again, I swapped INT for ID.I do agree with some of the other comments though, that making changes to your code would be very beneficial. I've seen and used a system similar to this before and it was quite the performance bottleneck. I'm also aware of the struggle to make large changes to an existing system, so I have sympathy. It will be worth the performance boost though. Good luck! ChadWed, 14 Jul 2010 16:33:37 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxDo you mean that you are inserting the PK value into each table and can't rewrite the inserts?Honestly how long can it take, and if this is an update issue, perhaps you might want to take the time and fix it. Otherwise you're burying things deeper and potentially going to have an issue later as clients grow.The other alternative is have you thought about a single column int, identity table that shadows each regular table? Drop an insert there, grab the identity, use it in your regular table. Then you would have a solid solution.Wed, 14 Jul 2010 16:31:56 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxI agree with Lutz. Wed, 14 Jul 2010 16:11:05 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]mike 57299 (7/14/2010)[/b][hr]Actually, I am the vendor and I don't have the time right now to rewrite all the code to handle identity columns. Currently, the code has to 'generate' the pk for each record.[/quote]OUCH!!!! :sick:I strongly advice you to take the time to get your code and table structure right! And I don't say it just because I'm usually a customer being faced with stuff like you're trying to implement occasionally.There is no valid reason whatsoever to implement this kind of code. Especially not with a reason like "I don't have the time right now".Wed, 14 Jul 2010 15:52:55 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxActually, I am the vendor and I don't have the time right now to rewrite all the code to handle identity columns. Currently, the code has to 'generate' the pk for each record.Wed, 14 Jul 2010 15:40:17 GMTmike 57299http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspx[quote][b]mike 57299 (7/14/2010)[/b][hr]I have legacy code that does not allow for identity columns, thus I have a table that holds the last "PK" value. I am getting occasional update issues. I need to create a stored proc that locks the "record" for a table, adds 1 to the last used #, unlocks the record and then returns the new ID.I know that SQL doesn't really do record level locking, but I think a transaction would work. [b]Does this help?[/b]Mike[/quote]Not really...If it's legacy code, get the vendor in to fix the issue.What would you need the stored proc for in the first place? Are you planning to insert rows without using the vendor's app? If so, I strongly vote against it. You might break the code or data integrity.Wed, 14 Jul 2010 15:25:31 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxI have legacy code that does not allow for identity columns, thus I have a table that holds the last "PK" value. I am getting occasional update issues. I need to create a stored proc that locks the "record" for a table, adds 1 to the last used #, unlocks the record and then returns the new ID.I know that SQL doesn't really do record level locking, but I think a transaction would work. Does this help?MikeWed, 14 Jul 2010 15:13:14 GMTmike 57299http://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxWhat exactly are you trying to do?I don't see the input parameter @table_name being used anywhere.The condition WHERE gpk_pk = 'HOLDER' will fail since gpk_pk is an INT data type.The script will fail as soon as you have more than one row in GPK with a value gpk='HOLDER' since you'd try to assign more than one value to the variable @Return_ID.Using GPK as the name of a table, a column of the table and as a transaction name is hopefully just for the sample code.... Otherwise I'd consider it really bad practice...If you'd tell us what you're looking for we might be able to provide an alternative.Wed, 14 Jul 2010 15:07:21 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic952715-1292-1.aspxHi All. I have a table:[code="plain"]Table GPKgpk Char(30) -- Name of tablesgpk_ID INT -- Last PKCREATE TABLE gPK (gpk CHAR(30), gpk_ID INT)INSERT gpk (gpk, gpk_id) VALUES ('HOLDER', 1)[/code]I created the following stored procedure:[code="other"]IF OBJECT_ID ( 'dbo.usp_GenPK', 'P' ) IS NOT NULL DROP PROCEDURE dbo.usp_GenPK;GOCREATE PROCEDURE dbo.usp_GenPK( @table_name char(128) = '')AS BEGIN DECLARE @oTbl TABLE (pk ID) DECLARE @return_ID ID begin transaction GPK update GPK set gpk_ID = gpk_ID + 1 output INSERTED.gpk_ID INTO @oTbl WHERE gpk_pk = 'HOLDER' SELECT @Return_ID = pk FROM @oTbl commit transaction GPK RETURN @Return_ID END[/code]Problem is that I can seem to get the return value into a variable. I tried this as a function but it wouldn't run it because of the transaction and update statements.Any ideas?Thanks,MikeWed, 14 Jul 2010 14:51:59 GMTmike 57299