Author : Gopal Krishna Ranjan

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Set vs Select – Assigning variables

SET and SELECT both can be used to assign values to the local variables in SQL Server. However, SET is an ANSI standard and also recommended by Microsoft to be used for variable assignment. In this post, “Set vs Select – Assigning variables“, we will explore the behavior of SET and SELECT statement during variable assignment from a query.

When we use a scalar subquery (which returns a single value) to assign a local variable using SET or SELECT, both statements behave similar. It means, if subquery returns no rows, they set variable to NULL during variable assignment. But when a column name from a query (which is not a scalar subquery) directly assigns to a local variable using SELECT statement and the query returns 0 rows, then the variable value remains unchanged.

As per its nature, SET statement uses a scalar subquery to assign a local variable … More

Change stored procedure output column names and data types 2

Can we change the output column name and their data type being received from a stored procedure during execution?

Yes, SQL Server 2012 introduced “WITH RESULT SETS” option which can be used with EXECUTE command to redefine the metadata of result sets being received from stored procedures. This option can handle single as well as multiple result sets.

WITH RESULT SETS” option can be useful if we want to reuse an existing stored procedure’s result set(s) with different column name and / or data type. Lets understand this with the help of an example. Below is the stored procedure named “uspGetEmployeeManagers” created in “AdventureWorks2014” datababse. This procedure return the list of managers for a given employee as below:

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 101
Current output columns

Current output columns

Imagine, this stored procedure is being used in many applications throughout organization. Now, in a new … More

The join order has been enforced because a local join hint is used – Warning in SQL Server 3

When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

SELECT * FROM Person.Person A 
INNER MERGE JOIN HumanResources.Employee B 
ON A.BusinessEntityID = B.BusinessEntityID

After execution, we will get below warning message with result set:

Warning Message

Warning Message

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.… More

Dynamic PIVOT query in SQL Server

PIVOT clause is used to generate cross tab outputs in SQL Server. We put unique data values from a column in the PIVOT clause to render them as multiple columns in aggregation with other columns required in the output. With the help of PIVOT clause, we can transpose the distinct values of a column into multiple columns. However, syntax of PIVOT clause requires these distinct values to be known at query design time. This kind of query can be considered as a static pivot query. We can hard code these distinct values in the PIVOT clause only if these values are fixed and static in nature.

However, when it comes to pivot uncertain values from a column, we would’t be able to cater these values in the PIVOT clause at query design time. Also, SQL Server does not provide any built in functionality which can be used in this scenario. … More

Sort data in Power BI

This post “Sort data in Power BI” explains how we can sort Power BI visual data by a column which is being used in the visual itself or by some other column which is not being used in the visual itself. We know that Power BI desktop tool provides set of functionalities to create flashy and interactive reports. It supports direct connectivity with many data sources, to know more about Power BI desktop application, visit our previous post “Rise of the Power BI“, to explore drill down functionality in Power BI, visit “Drill down report in Power BI” post.

To demonstrate Power BI visual data sorting, we are going to use below sample query which is extracting internet sales data along with sales territory from AdventureWorksDW2014 database:

DST.SalesTerritoryGroup AS GroupName, DST.SalesTerritoryCountry AS CountryName, DST.SalesTerritoryRegion AS RegionName,
ROUND(SUM(FIS.SalesAmount), 2) AS SalesAmount
FROM FactInternetSales FIS

Insert statement

In this chapter “Insert statement“, we are going to discuss T-SQL’s Insert statement which is used to add new records in a table. Whenever, we need to add a record in a table, INSERT T-SQL statement is used. We can insert single or multiple records at a time using INSERT statement. INSERT statement can be used in two ways as below:

  1. To insert fixed (hard-code) values
  2. To insert the result of a SELECT statement

Let’s discuss each of them in more detail with the help of example.

To insert fixed (hard-code) values

To insert fixed values in the table, below is the syntax:


INSERT INTO [<schema-name>.]<table-name>

[(column-1, column-2, column-3, …….., column-n)]


(value-1, value-2, value-3, …….., value-n)


INSERT INTO is used to declare a new INSERT statement.

[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists … More

Drill down report in Power BI

In previous post “Rise of the Power BI“, we have discussed about the Power BI and it’s components. In this post “Drill down report in Power BI”, we are going to learn how we can create a drill down report in Power BI.

To demonstrate the Power BI drill down functionality, we are loading the sample data using a SQL query from “AdventureWorks2014” database. Power BI desktop provides a direct connectivity to SQL Server which we are going to use in this tutorial.

Lets, load the data into power BI to create a drill down report:

Loading data into Power BI from SQL Server

To create a SQL Server connection to pull data from AdventureWorks2014 Database, follow below steps:

  1. Open Power BI Desktop application.
  2. A dialog box will appear at startup screen, click on “Get Data” button appearing at left of the dialog box.

Convert number into year, month, day, hour, minute and second

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.


100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)


Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:


Rise of the Power BI 1

What is Power BI?

Power BI is a new cloud first self service analytic tool introduced by Microsoft which empowers us to bring our whole business data at one place for analysis purpose. It provides an easy way to pull data from multiple data sources which includes but not limited to files, databases, azure databases, Hadoop files, websites and many more.

Power BI has various components, from which Power BI Desktop tool is used to create, publish and share interactive and stunning reports. Mobile users can download the Power BI Mobile application to view these published dashboards on their mobile devices. All together, it’s a new era of interactive and mobile friendly cloud based self service reporting.

It’s a free reporting solution from Microsoft which can be used by anyone, anytime, anywhere.

Why Power BI?

Microsoft is providing a complete enterprise BI solution to the industries since more than a … More

Query Performance Tuning – General Guidelines

Query performance tuning – Introduction

Query performance tuning is a process of improving system performance to make it faster and more scalable. It is a creative and iterative process which has a clear and well-defined objective. To achieve this goal, we have to follow certain defined steps.

Why we need this

Enterprise data changes frequently over time, which may impact the performance of existing queries. In most of the cases, a slight change in the index or in the query can improve the performance of the query significantly. In such cases, the improvement in performance can be much better than a CPU or memory upgrade.

How to tune a query

Performance tuning process has certain steps to follow in a defined way to achieve the clearly defined objective.

Query Performace Tuning - General Guidelines - Flow chart

Query performance tuning

In the above flow chart, you see that we begin by setting objective, followed by getting the current performance … More