Script to see running jobs in SQL Server

Script to check the Sql Agent Running Jobs

SELECT

    *

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

    ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

    ON ja.job_id = js.job_id

    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND start_execution_date is not null

AND stop_execution_date is null;

LEAD in TSQL

LEAD has introduced in SQL Server 2012 to access data from a subsequent row in the same resultset without the use of self-join . LAG is a analytical function in a select statement to compare values in the current row with values in a previous row.



Create Table Sales( empid int, Year int, sales money)

insert into Sales values(1,2000,100)

insert into Sales values(1,2001,200)

insert into Sales values(1,2002,300)

insert into Sales values(2,2001,200)

insert into Sales values(2,2002,300)

--Subsequent Row   SELECT empid, Year, sales,

lead(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales

Results :

empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 200.00

1 2001 200.00 300.00

1 2002 300.00 0.00

2 2001 200.00 300.00

2 2002 300.00 0.00
 

LAG in TSQL

LAG has introduced in SQL Server 2012 to access data from a Previous row in the same resultset without the use of self-join . LAG is a analytical function in a select statement to compare values in the current row with values in a previous row.

Create Table Sales( empid int, Year int, sales money)

insert into Sales values(1,2000,100)

insert into Sales values(1,2001,200)

insert into Sales values(1,2002,300)

insert into Sales values(2,2001,200)

insert into Sales values(2,2002,300)

--Previous row
 SELECT empid, Year, sales,

LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales

Results:
empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 0.00

1 2001 200.00 100.00

1 2002 300.00 200.00

2 2001 200.00 0.00

2 2002 300.00 200.00

 
Two rows earlier


SELECT empid, Year, sales,

LAG(sales, 2, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit FROM Sales
 Results :

empid Year sales PrevProfit

----------- ----------- --------------------- ---------------------

1 2000 100.00 0.00

1 2001 200.00 0.00

1 2002 300.00 100.00

2 2001 200.00 0.00

2 2002 300.00 0.00

--YOY Profit

SELECT empid, Year, sales,

LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) AS PrevProfit,

sales-LAG(sales, 1, 0) OVER (PARTITION BY empid ORDER BY Year) as YOY FROM Sales
 
Results :


empid Year sales PrevProfit YOY

----------- ----------- --------------------- --------------------- ---------------------

1 2000 100.00 0.00 100.00

1 2001 200.00 100.00 100.00

1 2002 300.00 200.00 100.00

2 2001 200.00 0.00 200.00

2 2002 300.00 200.00 100.00

List of Tables and Stored procedure created recently

select *

from sys.objects

where (type = 'U' or type = 'P')

and create_date > dateadd(m, -3, getdate())

order by create_date desc

SQL Server 2016 new features

•    R

•    JSON 

•    SSRS combined with Datazen 

•    SSIS includes Power Query 

•    SSAS Tabular supports Many to Many 

•    Query Store 

•    Another fix to SQL Server Data Tools

•    Polybase : Bringing Big Data to SQL Server