One more way to get primary key details of table

One more way to get primary key details of table

EXEC sp_pkeys
    ‘SalesOrderDetail’,
    ‘Sales’

‘Sales.SalesOrderDetail’

Exec  sp_pkeys
@table_name,
@table_owner,
@table_qualifier

@table_name – Table Name
@table_owner – Table Owner Name Default is Null
@table_qualifier – DB Name where proc is running  Default is Null

Lets see an Exampple

EXEC sp_pkeys   ‘SalesOrderDetail’,   ‘Sales’

How to Restore MSDB Database

How to restore MSDB database

If your msdb goes suspect then you have two choices, if you have backup of database then restore it else you have to recreate it

If you want to recreate it here are the steps to do it

1. Start the server with trace flag 3608.Detach the damaged msdb.
2. Navigating to the directory ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn’ and start sqlservr.exe -c -T3608
3. Move or rename the damaged msdb files
4. Go to the folder ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install’ and run the instmsdb file.
5. Restart SQL Server without the 3608 trace flag

Session Property SQL Server 2005

In sql server we can get session property many ways

1. dbcc useroptions
dbcc 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

New Feature in Named Set – Analysis Service 2008

One of the important feature in SQL Server 2008 is Dynamic Named Sets. Till SQL Server 2005 named sets were static. In SQL Server 2005 once named sets are executed they were remain static during whole session or query or cube. This was giving wrong results during Topcount or Bottomcount like functions.

Lets look at the example to get more details

In Adventureworks cube

First we create a set of top 5 products for of Internet Sales

create set [Adventure Works].[top5products] as

‘topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])’;

First we will run query fro all time period

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

Result

Internet Sales Amount

Road-150 Red, 48 $1,205,876.99

Road-150 Red, 62 $1,202,298.72

Road-150 Red, 52 $1,080,637.54

Road-150 Red, 56 $1,055,589.65

Road-150 Red, 44 $1,005,493.87

Now we run the query for year 2003

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

Result

Internet Sales Amount

Road-150 Red, 48 (null)

Road-150 Red, 62 (null)

Road-150 Red, 52 (null)

Road-150 Red, 56 (null)

Road-150 Red, 44 (null)

Look at here this products have no sales in 2003, because in 2003 top5products are different then top5products for all time period. But even though it appears in the result because the set top5product is evaluated when it was created and it will be static for all sessions and does not reflect the change in where condition

drop set [Adventure Works].[top5products]

Now we will create dynamic set which will be effected from where condition in the query

create dynamic set [Adventure Works].[top5products] as

‘topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])’;
First we will run query fro all time period

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

Result

Internet Sales Amount

Road-150 Red, 48 $1,205,876.99

Road-150 Red, 62 $1,202,298.72

Road-150 Red, 52 $1,080,637.54

Road-150 Red, 56 $1,055,589.65

Road-150 Red, 44 $1,005,493.87

Now we will run the query from 2003

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

Result

Internet Sales Amount

Mountain-200 Black, 46 $431,458.12

Mountain-200 Black, 42 $403,918.24

Mountain-200 Silver, 38 $389,758.32

Mountain-200 Silver, 42 $373,518.39

Mountain-200 Black, 38 $367,198.40

Here result is different because top5products is different in 2003 than that of 2001 and we are getting right result because of dynamic sets.

drop set [Adventure Works].[top5products]

So because of dyamic set we get right results and better performance which is not possible in static named sets

How to get linked values in one group using CTE

/* 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