Hi!
Well, thanks for explanations.
If I explicitly lock a table when inserting record, would it cause much lose of performance?
>>What you meant as "context" of use?
>
>I would guess that if the application was used by a single user, in a single session, IDENT_CURRENT() would return the value that you would expect.
>
>>Inside transaction, when you insert new record into some table, thattable is probably locked from other insertions till transaction end. Am I right?
>
>Maybe. Maybe not. The SQL Server optimizer and engine determine the granularity of locks. You might get a table lock, a page lock, or just a row lock.
>
>If SQL Server always got a table lock, you'd have a sever impact on concurrency.
>
>Pick up a copy of Inside Microsoft SQL Server 2000.
>
>-Mike
>
>>Hi!
>>
>>What you meant as "context" of use?
>>
>>Well, what about following scenario:
>>
>>Inside transaction, when you insert new record into some table, thattable is probably locked from other insertions till transaction end. Am I right? If yes, than use of IDENT_CURRENT is very obvious:
>>
>>1. Start transaction
>>2. Insert record into "MyTable"
>>3. Get new record ID using IDENT_CURRENT("MyTable"). It will be reliable because nobody else can insert record there while table is locked.
>>4. End transaction.
>>
>>Would above work?
>>
>>I think it is required also to should disallow trigger recursion for above, though I think it is not very needed, because I really cannot imagine when it is needed to insert record into same table in the insert trigger.
>>
>>Anyway, I think it is better than @@IDENTITY.
>>
>>>I cannot think of a situation where you would want to use IDENT_CURRENT. The reliability of it's use would depend on the context in which is was used.
>>>
>>>-Mike
>>>
>>>
>>>>Hi!
>>>>
>>>>So, what about question related to IDENT_CURRENT? Is it reliable in multi-user environment? Is my assumption correct about inserting inside of transaction? I still could not understand it's purpose. For what such variable could be used, if it is completely useless/not reliable in multi-user environment?
>>>>
>>>>>It comes down to a number of issues, really:
>>>>>
>>>>>Remote views create ad-hoc queries that are sent to the server. I have very little control over the format of those queries. With a stored procedure, I have complete control.
>>>>>
>>>>>Also, when using stored procedure I have better control over security. I can deny the login access to all tables, yet still allow for the manipulation of data through stored procedure.
>>>>>
>>>>>Stored procedures provide another layer of abstraction. I can make changes to the schema, or adjust rules and as long as the stored procedure's signature doesn't change, the consumer isn't affected.
>>>>>
>>>>>There are also performance issues. Stored procedures will usually perform better than ad-hoc queries.
>>>>>
>>>>>-Mike
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.