/* How to get linked values in one group using CTE*/ create table table_a (col1 varchar(100), col2 varchar(100)) go insert into table_a values (‘item1′,’item2’) insert into table_a values (‘item2′,’item3’) insert into table_a values (‘item3′,’item4’) insert into table_a values (‘item5′,’item6’) insert into table_a values (‘item6′,’item7’) insert into table_a values (‘item8′,’item9’) go select * from table_a go /* As per the above data item1 is linked with item2 and item2 is linked with item3 and item3 is linked with item4 so all item1 , item2 , item3 and item4 are in same group, lets give it number 1. item5 is linked with item6 and item6 is linked with item7 so item5 , item6 and item7 are in same group, lets give it number 2 item8 and item9 will be in group 3. We can achieve this result using follwing query. */ WITH temp (Col1, Col2, id) AS (SELECT Col1, Col2, ROW_NUMBER() OVER (ORDER BY Col1, Col2) AS num FROM TABLE_A) , t as (select col1 , col2,id,1 as linkid from temp where id= 1 union all select temp.col1 , temp.col2,temp.id, case when (temp.col1 in (t.col1, t.col2) or temp.col2 in (t.col1, t.col2) ) then linkid else linkid+ 1 end from temp , t where temp.id= t.id+1 ) select col1, col2 , linkid from t Regards Amish Shah

Session propery SQL Server 2005

In sql server we can get session property many ways
1. dbcc useroptionsdbcc useroptions
This will give us information about all the enabled setting for that session.
In SQL Server 2005 two new options are available
2. sessionproperty function

It returns set option of a session
You can get information of following option usig set
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_ NULL
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

For ex.
select sessionproperty(‘Quoted_Identifier’)
If result is 1 then property on.
3. Dynamic view dm_exec_sessions Benefit of this view that you can see property not only of this session but of also other sessions.You can join this view other dynamic system views using session id.
This view also gives much more information than session property.
If the result is 1 then property is on and if its 0 then property is set off.
select session_id,
login_name,
program_name,
date_format,
quoted_identifier,
arithabort,
ansi_null_dflt_on,
ansi_defaults,
ansi_warnings,
ansi_padding,
ansi_nulls,
concat_null_yields_null,
case transaction_isolation_level
when 0 then ‘Unspecified’
when 1 then ‘ReadUncomitted’
when 2 then ‘ReadCommitted’
when 3 then ‘Repeatable’
when 4 then ‘Serializable’
when 5 then ‘Snapshot’
end as transaction_isolation_level,
deadlock_priority
from sys.dm_exec_sessions