SQL CODE TO GET LIST OF ROWS HAVING NULLS IN ALL COLUMNS

We may encounter a situation wherein the table contains rows that have null values in all the columns. We might need to perform some activity on those rows say clean up the table as those rows will not add value to the business. In this section, I will try to put in the code that takes the column names dynamically and forms a query that you can use further in your code.

Let’s get started:

Input:

Untitled1

Output:

Untitled2

 

SQL Code:

DECLARE @sql nvarchar(max), @whereclause nvarchar(max)

,@tblName nvarchar(100) = ‘tbl’ –Table Name

,@pkColumn nvarchar(100)  = ‘ID’ –Primary Key Column

DECLARE @tbl TABLE

(

ID int IDENTITY(1,1),

ColName nvarchar(50)

)

INSERT INTO @tbl(ColName)

SELECT sc.name FROM sys.columns sc

JOIN sys.tables st

ON sc.object_id  =st.object_id

WHERE st.name = @tblName and sc.name NOT IN (@pkColumn)

SELECT @whereclause = COALESCE(@whereclause + ‘ is NULL AND ‘,” ) + ColName FROM @tbl

SELECT @SQL  = ‘SELECT * FROM     ‘ + @tblName + ‘ WHERE ‘ + @whereclause + ‘ is null’

SELECT @sql

That’s it. We are done.

Happy Coding!

 

Killing running processes in SQL Server

Hey Guys!
This particular code should be handled with great care as you might end up killing production processes. Be very careful in firing this SQL.
First list out your running processes with the help of below query:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

The results returned by above query contains many columns of interest but we are particularly interested in ‘Session_ID’ as this column will be used in the code to kill other running processes.
Here goes the code:
DECLARE @tbl table
(
id int identity(1,1),
sessionid int
)

INSERT INTO @tbl (sessionid)
SELECT
req.session_id
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

DECLARE @rowcnt int, @sesid int, @sql nvarchar(max)
SELECT @rowcnt = count(*) from @tbl

WHILE (@rowcnt>0)
BEGIN
SELECT @sesid = sessionid FROM @tbl WHERE id = @rowcnt
SET @sql = ‘kill ‘ + cast(@sesid as nvarchar(5))
IF @sesid NOT IN (51,63) — Pass session ids of those processes that you don’t want to kill
EXEC sp_executesql @sql

set @rowcnt-=1

END

Enjoy!!
Happy Coding!

Display Comma Separated Values of a column group by another column in a different table

Hey guys!
Let’s get into another SQL code.
You have 2 tables TBL_EMP and TBL_DEPT with following schema and data:
TBL_EMP:
Emp_ID Dept_ID Emp_Name
1 1 Ram
2 2 Mohan
3 3 Sohan
4 2 Rita
5 3 Sonu
6 1 Karan

TBL_DEPT:
Dept_ID Dept_Name
1 .Net
2 Java
3 Php

Now you need to write SQL code that gives following output:
Dept_Name Emp_Name
.Net Ram,Karan
Java Mohan,Rita

Here goes the SQL program:

DECLARE @tmpTbl TABLE
(
ID BIGINT IDENTITY(1,1),
–Dept_ID int,
Dept_Name NVARCHAR(100),
Emp_Name nvarchar(100)
)

DECLARE @tmpResults TABLE
(
ID BIGINT IDENTITY(1,1),
Dept_Name NVARCHAR(100),
Emp_Name nvarchar(max)
)

DECLARE @emp_Name TABLE
(
Emp_Name nvarchar(max)
)

DECLARE @rowCnt int, @sql nvarchar(MAX), @counter int = 1,
@deptName nvarchar(100), @empName nvarchar(max)

INSERT INTO @tmpTbl (Dept_Name,Emp_Name)
SELECT TD.Dept_Name, TE.Emp_Name FROM Tbl_Dept TD
JOIN Tbl_Emp TE
ON TD.Dept_ID = TE.Dept_ID

SELECT @rowCnt = COUNT(*) FROM @tmpTbl

WHILE(@counter<=@rowCnt)
BEGIN

SELECT @deptName = Dept_Name FROM @tmpTbl WHERE ID = @counter

SELECT @SQL = COALESCE(@SQL,”,”) + Emp_Name + ‘,’ FROM @tmpTbl
WHERE Dept_Name = @deptName

SET @sql = LEFT(@SQL, LEN(@SQL)-1)

INSERT INTO @tmpResults (Dept_Name, Emp_Name)
SELECT @deptName, @sql

SET @sql=”

SET @counter+=1

END

SELECT DISTINCT Dept_Name, Emp_Name FROM @tmpResults

Enjoy!
Happy Coding!

 

Dimensional Schema Design

Hey guys! Whenever we need to do Dimensional Modeling for building DWs, the very first question that comes to our mind is to follow which Dimensional Schema Design: Star or Snowflake. I have complied list of differences between the two designs and you can decide which one best suits your requirements.

S.No. Star Snowflake
1. Single Central Fact surrounded by dimension tables One fact connected to many dimensions which further connects to other dimension tables
2. Uses de-normalized data that introduces redundancy Use normalized data thus eliminating redundancy
3. Business Hierarchy is not maintained via Referential Integrity. Dimensions directly refer to Fact table Business Hierarchy is maintained via Referential Integrity
4. Difficult to maintain and change Easy to maintain and change
5. Involves less complex queries Involves complex  queries
6. High performance\ less query execution time due to foreign keys Low performance\ more query execution time due to foreign keys
7. Preferred when relationships are 1:1 or 1:Many Preferred when relationships are Many:Many
8. ETL Design is simpler and can have parallelism  dimensions and facts loading is independent of each other ETL Design is complex and no parallelism since dimensions and facts loading is dependent

 

Note: Selection of Dimension Schema depends on Business Requirements. If you think star schema best fits the business needs then go for it and same with snow flake schema.

 

If you like the post please provide constructive comments.

Have Fun!

Implementing timer in SSIS: Auto Stop Jobs

One can easily schedule job to run Daily\ Weekly\ Monthly but if you are asked to stop your job without any manual intervention at a particular time in a day because there are other planned activities that needs the server resources and this happens daily, what would you do? I was asked the same and hence I built small logic to implement the same which I am going to share with you.

Let’s begin:

I just introduced an ‘Execute SQL Task’ in my SSIS package and written following SQL Script:

WHILE (1=1)

BEGIN

IF(RIGHT(RIGHT(GETDATE(),12),6) = ‘2:29PM’)

EXEC msdb.dbo.sp_stop_job  N'<<Your job Name>>’ ;

END

 

That’s all I did and accomplished my task.

Enjoy!

Happy Coding!

Automating Backup And Restore using SSIS

This particular post will throw light on how to perform backup and restore using T-SQL code and later describe the process to automate the same using SSIS.

Let’s begin

–Execute below code on Source Server Master Database

–Parameters

DECLARE @sourceDatabase nvarchar(100),

@backUpFileLocation nvarchar(100)

–T-SQL to perform backup

DECLARE @sql nvarchar(max),

@backUpFileName nvarchar(500)

SELECT @backUpFileName = @sourceDatabase + ‘_bac.bak’  –preparing backup file name

SET @sql =

‘BACKUP DATABASE [‘ +  @sourceDatabase + ‘]

TO DISK=N”’+ @backUpFileLocation + @backUpFileName +”’

WITH NOFORMAT

,NOINIT

,SKIP

,NOREWIND

,NOUNLOAD’

EXEC SP_EXECUTESQL @sql

Till this point, our backup is done and .bak file will be placed at backup file location specified in parameters.

Now it’s time to restore the back up. Here goes the code:

–Execute below code on Destination Server Master Database

–Parameters

DECLARE @DestinationDatabase nvarchar(100),

@backupfilelocation nvarchar(100), –same as specified while taking backup

@sourcemdffile nvarchar(100),

@sourceldffile nvarchar(100),

@backupfilename nvarchar(100), –same as specified while taking backup

@destinationMDFfile nvarchar(100),

@destinationLDFfile nvarchar(100)

–T-SQL code to perform Restore operation

DECLARE @targetDB nvarchar(100),

@sql nvarchar(max)

SELECT @targetDB = @DestinationDatabase

SELECT @sql = ‘

RESTORE DATABASE [‘ + @targetDB +’]

FROM DISK = N”’ + @backupfilelocation + @backupfilename + ”’

WITH FILE= 1

,MOVE ”’ + @sourcemdffile + ”’ TO ”’ + @destinationMDFfile + ‘\’+@targetDB +’.MDF”

,MOVE ”’ + @destinationMDFfile + ”’ TO ”’ + @destinationLDFfile + ‘\’ +@targetDB +’_1.LDF”

,NOUNLOAD’

EXECUTE SP_EXECUTESQL @SQL

 

Automating Backup and Restore:

To avoid manual execution of queries on 2 servers, you can follow below steps to automate both operations:

1. Develop SSIS package and wrap T-SQL code in ‘Execute SQL Task’.

2. Use variables to pass parameters to ‘Execute SQL Task’ for fetching source\ destination servers\ database names, backup file location dynamically.

3. Develop a job that will pass parameters to your SSIS package variables and use these parameters in the package you developed.

4. Now just specify parameters in the job you developed and run it. No need to go to source and destination servers to run code and your entire process is automated.

Happy Coding!

If you have any constructive opinion, please write to me or post comments

Multicast versus Conditional Split

Multicast and conditional split transformations are quite often used while developing an SSIS package. I have compiled some of the differences between the two transformations that might help understanding these transformations in a better way:

Multicast Conditional Split
Replicate source data to multiple destinations so that different logic can be applied to same source data Distribute the source data into multiple destinations based on conditions
Directs row to every output Directs row to single output
Does not contain any error output It has one error output
Do not require expressions Expressions are used for specifying conditions
Do not have default output It provides default output  so that if a row matches no expression it is directed to default output

If you have any constructive opinion, please write to me or post comments

Enjoy!