>Hi!
>
>Well, if there are no much of inserts into table, and transactions are as quick as possible, would it be ok for large number of users? I just want to know where this could be used at all. In my opinion, in most business applications users rarely do records insertions, and mostly do queries and/or updates. There are few operators that enter (insert) data, and huge number of users that just read data. With such scenario, would it cause much of performance lose?

IMO, with the scenario that you've provided, yes you would see a performance impact. Locks are obeyed by all operations, READ and WRITE. So if the table is locked, the "huge number of users that just read data" won't be able to.

In general, the best practice is to get the most granular lock that is possible. This is what the SQL Server optimizer and engine try to do. Locks are only escalated when the acquistion of a large number of them would cause resource issues.

-Mike

>
>>>If I explicitly lock a table when inserting record, would it cause much lose of performance?
>>
>>In general, the answer to your question is "yes." By always getting a table lock, you serialize all updates to the table. There would be huge impacts to concurrency. You could imagine it as forcing all of your users to use the same keyboard ;-)
>>
>>OTOH, if you had no load on the application and server, your users might see no lose of performance. For example, two users hitting a big SQL Server.
>>
>>-Mike
>>
>>>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