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:
Output:
SQL Code:
|
That’s it. We are done.
Happy Coding!
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!
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!
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!
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
Getting results of a column in comma separated format is often required while one is writing the stored procedure. The comma separated values then can be used in SQL ‘In’ clause to filter the result. Below example will show you how to get values of a column in comma separated format:
Suppose you have a table named ‘tblCountry’ with following data:
Now you want your countries to be in comma separated format like India,Australia,US,UK.
Following snippet will give you the desired result:
DECLARE @SQL nvarchar(MAX)
SELECT @SQL = COALESCE(@SQL + ‘,’,”) + CountryName FROM [dbo].[tblCountry]
SELECT @SQL
The result is a string of characters that can be used further in the code.
Enjoy! Happy Coding!!
DECLARE @NoOfIterations int, –Indicates in how many chunks you want to pull the data
@RangeAdd int,
@ColSIDStart int,
@ColSIDEnd int,
@Counter int ,
@MaxColSID int,
@MinColSID int
DECLARE @ColSIDRANGETBL TABLE
(
Iteration int,
ColSIDStart int,
ColSIDEnd int
)
SET @NoOfIterations = ?
SET @Counter = 1
SELECT @MinColSID = MIN(ColSID) FROM [dbo].[Table] (NOLOCK)
SELECT @MaxColSID = MAX(ColSID) FROM [dbo].[Table]] (NOLOCK)
SET @RangeAdd = (@MaxColSID – @MinColSID)/@NoOfIterations
SET @ColSIDStart = @MinColSID
SET @ColSIDEnd = @ColSIDStart + @RangeAdd
WHILE ( (@Counter<=@NoOfIterations) and (@ColSIDStart <= @ColSIDEnd) )
BEGIN
INSERT INTO @ColSIDRANGETBL
SELECT @Counter,@ColSIDStart,@ColSIDEnd
SET @ColSIDStart = @ColSIDEnd + 1
SET @ColSIDEnd = @ColSIDStart + @RangeAdd
IF(@ColSIDEnd>@MaxColSID)
BEGIN
SET @ColSIDEnd = @MaxColSID
END
SET @Counter = @Counter + 1
END
SELECT ColSIDStart, ColSIDEnd FROM @ColSIDRANGETBL
IF OBJECT_ID (N’dbo.uspSaveDataToXMLFile’) IS NOT NULL
DROP PROCEDURE dbo.uspSaveDataToXMLFile
GO
CREATE PROCEDURE dbo.uspSaveDataToXMLFile
@TheXML XML,
@Filename VARCHAR(255)
AS
SET NOCOUNT ON
DECLARE @MySpecialTempTable VARCHAR(255)
DECLARE @Command NVARCHAR(4000)
DECLARE @RESULT INT
–firstly we create a global temp table with a unique name
SELECT @MySpecialTempTable = ‘##temp’
+ CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
–then we create it using dynamic SQL, & insert a single row
–in it with the variable stocked with the XML we want
SELECT @Command = ‘create table [‘
+ @MySpecialTempTable
+ ‘] (MyID int identity(1,1), Bulkcol XML)
insert into [‘
+ @MySpecialTempTable
+ ‘](BulkCol) select @TheXML’
EXECUTE sp_ExecuteSQL @command, N’@TheXML XML’,
@TheXML
–then we execute the BCP to save the file
SELECT @Command = ‘bcp “select BulkCol from [‘
+ @MySpecialTempTable + ‘]’
+ ‘” queryout ‘
+ @Filename + ‘ ‘
+ ‘-w’–save as unicode
+ ‘ -T -S’ + @@servername
EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
EXECUTE ( ‘Drop table ‘ + @MySpecialTempTable )
RETURN @result
GO
–TEST SCRIPTS
–Parameters:
— 1. XML data
— 2. Fully qualified XML file path
–EXEC uspSaveDataToXMLFile ‘<<XML Data>>’, ‘F:\Extra\Projects\TEST.XML’
ssisblog 