July 10, 2003 at 8:41 am
Does anyone know how to handle the ID column in the talble without using the auto numbering in SQL. Thanks!
July 10, 2003 at 8:48 am
You could create your own control table but what are you trying to accomplish?
July 10, 2003 at 1:41 pm
I don't want to use auto number, so when I do the insert, I will need to get the next ID column first. Is there some technic I can use to optimize the lock. Have you used trigger to handle this type of problem? Thanks for your help!
July 11, 2003 at 12:47 am
quote:
I don't want to use auto number, so when I do the insert, I will need to get the next ID column first. Is there some technic I can use to optimize the lock. Have you used trigger to handle this type of problem? Thanks for your help!
Well, getting the next consequtive number is easy
SELECT MAX([KA-Nr])+1 AS Maximum FROM tblKapitalanlagenummern
I'm doing this on some tables that are NOT frequently updated or inserted, right because of the locking issue, you've mentioned. BTW, all these tables contain also an ident field.
I haven' t done this on frequently inserted tables, I guess in this case I'll stick with 'auto number'
Cheers,
Frank
Forget to mention, this is done via single SP's
Edited by - a5xo3z1 on 07/11/2003 12:48:52 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 11, 2003 at 4:27 am
I would also state that in doing that you need to explicitly issue a table lock to prevent other items from getting bad values.
July 11, 2003 at 9:42 am
I don't want to lock the whole table for a record insert. The tables are frequently used. It definitely will be hit by multiple user at the same time. I want a general sp to accomplish this task, and every insert statement can call it. I need to consider the performance also. Any ideas? Thanks!
July 11, 2003 at 2:11 pm
Hi,
quote:
The tables are frequently used. It definitely will be hit by multiple user at the same time. I want a general sp to accomplish this task, and every insert statement can call it. I need to consider the performance also. Any ideas?
only one idea.
Let SQL Server do the job!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 11, 2003 at 8:55 pm
If you are opposed to letting SQL Server handle this, don't do the max() technique, mainly because of the performance issues as Frank has measured.
Antares' control table solution has only one row and so the read time is significantly less than the max() solution but it does require a table lock on that one-row table as he's indicated.
You might do a search on this subject and look for Antares' name. He's provided some innovative ways of dealing with this issue in the past that required some detail, especially concerning avoiding the performance hit of foreign keys.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply