HELP IN SQL SERVER 2000. I'M FACING PROBLEM IN SQL SERVER 2000. CAN ANYBODE HELP ME.
Terms of Agreement:
By using this article, you agree to the following terms...
You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
PROBLEM IS:
I HAVE A TABLE CONTAINING ABOUT 40,000 RECORDS IN SQL2000 DATABASE. THE SIZE OF DATABASE IS ABOUT 40MB. WHEN I ADD NEW RECORD IN THE TABEL AND ISSUE FOLLOWING COMMAND TO SELECT THE IDENTITY OF EFFECTED RECORD IT TAKES A LOT OF TIME (ABOUT 6-7 SECOND) TO RETURN.
SQL STATEMENT IS
SELECT @@IDENTITY FROM INVOICES
PLZ HELP ME HOW CAN I REDUCE THIS DELAY.
Use the following to narrow down your searches... print IDENT_CURRENT('Invoices') (If this comment was disrespectful, please report it.)
5/3/2005 3:55:44 AM:
You may want to implement/use indexes by creating a particular indexes for a certain search/select criteria. In that way your search would not be doing a sequential search from 1 to 40000+th element of your table. (If this comment was disrespectful, please report it.)
Is your identity column also your clustered index? If you are using a different column for your clustered index, then SQL Server may be doing some "moving things around" to place the new record in the correct location per the clustered index, rather than just appending it onto the end. Also, if you have a ton of indexes on the table, that will also slow things down on an insert. (If this comment was disrespectful, please report it.)
send or check execution graph, it will help. (If this comment was disrespectful, please report it.)
5/5/2005 2:13:24 PM:
Thanks Joey Filichia IDENT_CURRENT('Invoices') solved my problem. Thanks for your help (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
5/2/2005 3:25:20 PM: 
