June 13, 2007 at 7:09 am
Is it possible to define snapshot isolation level for view? I have not found any view setting or table hint....I know only SET TRANSACTION ISOLATION LEVEL
command but I would prefer do it on view definition or inside view...
Thanks
Michal
June 13, 2007 at 8:37 am
Hi Michal
I don't think its possible to define snapshot isolation as table hint, it is feature enabled/disabled at database level. Can you please tell us why do u want this table hint?
Regards
Shrikant Kulkarni
June 14, 2007 at 12:20 am
It is clear that standard isolation level is defined for whole database. But sometimes it is good to change this level for some group of transactions. And my question was id there is some way to do this change at VIEW LEVEL. Views (in our system) are used for reporting only and we don´t need so actual data for the reports so I was thinking about changing isolation level to SNAPSHOT for reporting part of our SW. And that is why I was looking for something at view level. Of course I can do the same inside application using SET TRANSACTION ISOLATION LEVEL before and after block of code...
June 14, 2007 at 3:59 am
This would be error message you will get if you start a transaction in any other level and then changes isolation level to snpshot; at statement level with table hint (which microsoft does not provide as such)
set transaction isolation level repeatable read
begin tran t2
select * from employees
set transaction isolation level snapshot
select * from employees
commit tran t2
Msg 3951, Level 16, State 1, Line 3
Transaction failed in database 'Northgale' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.
I don't think there is way to override any other isolation level with snpshot but you can always do it other way round. I mean define transaction isolation level snapshot and use table hint inside to change concurrency at statement level.
Regards
Shrikant Kulkarni
June 14, 2007 at 5:05 am
OK, that is not problem for me, I have just experimented with new features of SQL2005. Some of them (eg. CTE) improved performance of our system and I was dealing with snapshot capabilities.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply