04.24.08
Transaction Isolation level
Few important things about isolation level
Read uncommitted
When its used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.
Read committed
This is the default isolation level in SQL Server. When its used, SQL Server will
use shared locks while reading data. It ensures that a physically corrupt data will notbe read and will never read data that another application has changed and not yet committed,but it does not ensure that the data will not be changed before the end of the transaction.
Repeatable read
When its used, the dirty reads and nonrepeatable reads cannot occur.
It means that locks will be placed on all data that is used in a query,
and another transactions cannot update the data.
Nonrepeatable read
When a transaction reads the same row more than one time, and between the
two (or more) reads, a separate transaction modifies that row. Because the
row was modified between reads within the same transaction, each read
produces different values, which introduces inconsistency.
Single column into comma seperated value
–Created one Example table
create table Events
(
PeopleName varchar(50),
EventType varchar(50),
Events varchar(100)
)
–Model Values
insert into Events values(‘Daya’,‘B’,‘Drinking’)
insert into Events values(‘Daya’,‘B’,‘Washing’)
–Created function to take Events with Comma Seperated on EventType basis
CREATE FUNCTION dbo.MakeCommaValue (@pC2 AS VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @oResult VARCHAR(8000)
SELECT @oResult= COALESCE(@oResult+‘,’,”)+CAST(Events AS VARCHAR(10))
FROM Events WITH (NOLOCK)
WHERE EventType = @pC2
ORDER BY Events
RETURN @oResult
END
–Call the Function like this
SELECT DISTINCT
PeopleName,EventType,dbo.MakeCommaValue(‘B’) as ‘Events’
FROM Events
–Result
PeopleName EventType Events
—————————————
Daya B Drinking,Washing
How to find out recently run queries in SQL Server 2005?
Select
dmStats.last_execution_time as ‘Last Executed Time’,
dmText.text as ‘Executed Query’
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc
