var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: how to configure snapshot isolation for selects?
I'm implementing a UDF that selects from a database table.
I want this select to be as fast as possible (no locking or blocking) so snapshot isolation appears to be the way to go for this.
I know I can use the following command to set the snapshot transaction isolation level:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
If I put this command before a select statement in my UDF will this implement the transaction isolation level for the subsequent select as-is?
Or do I need to have some additional wrapper around the select? For example for multi-table DML I would wrap the transaction in BEGIN TRANSACTION at the beginning and COMMIT TRANSACTION at the end.
However, what I'm doing is just a select statement so no need to commit anything. Or does SQL Server require some kind of wrapper around any SQL that leverages a transaction?
It will set this isolation level
. You do not have to explicitly work with transaction blocks, but you can. for the entire session
This is another good read.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
SET TRANSACTION ISOLATION LEVEL cannot be executed in a UDF , but can be applied with the use of TABLE HINTS.A UDF can’t execute the SET TRANSACTION ISOLATION LEVEL. The alternative for a UDF is a table hint. The table hint overrides the optimizer choice for the duration of the DML. An example of a table hint is :
SELECT col1,col2 FROM myTable WITH (TABLOCK)
WHERE col1 = ‘a_value’