Author : Gopal Krishna Ranjan


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

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:

Syntax:

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

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

VALUES

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

Where,

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.
More

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

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.

Examples:

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)

etc.

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:

Or, … More


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.

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


Order max and min value rows alternatively in SQL Server

In this blog post “Order max and min value rows alternatively in SQL Server”, we are going to learn how we can order the rows of a table by combining max and min value rows alternatively. For example, from an employee table based on the salary column, we need to extract the records ordered as;

First row: First highest salary

Second row: First lowest salary

Third row: Second highest salary

Fourth row: Second lowest salary

Fifth row: Third highest salary

Sixth row: Third lowest salary

and so on…….

We know that the SQL Server database engine is optimized to operate in a set oriented way instead of RBAR (Row By Agonizing Row, processing row by row in a loop). We are going to achieve the desired output in a set based approach, unlike loops or recursive CTEs. Let’s have this tutorial in action now.

Create the dummy employee table and … More


Add constraint without checking existing data

In this post “Add constraint without checking existing data” we are going to learn how we can add a constraint on a column which already has invalid data. We create constraints to automatically enforce the integrity of the database. Using constraints we can define the rules for valid set of values for a given column. Let’s learn how we can instruct SQL Server to ignore check on the existing data during constraint creation on a column.

We have an employee table with columns EmpId, EmpName and ContactNumber. Column ContactNumber holds few invalid values, as we don’t had a constraint on this column. Now, we want to accept only valid values in the contact number column being validated with the custom business logic, but we don’t want to remove or modify the already existing data in the column. We need to put a constraint for coming data and not for existing … More


Change chart type dynamically SSRS

Today, we are going to learn how we can change chart type dynamically depending on the selected value in the report parameter in SSRS (SQL Server Reporting Services) report. Report parameter has few predefined chart type as values in the report, and depending on the selected value in the parameter, chart renders in the report.

We know that there is no any inbuilt way which can be used to change the chart type at run time in SSRS and to achieve this we need to forge a couple of things. In this post we will discuss a method which can be used to achieve this. For demo, I am using AdventureWorks2014 database in this tutorial. I have attached the zipped project file which can be downloaded from here or from the link provided at the bottom end of this post.

Let’s create a dummy project in SSDT to demonstrate how … More


Index on computed column 2

Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.

Create index on computed column

Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.

Below is the function being used to create a computed column:

Note that the above function is created with schema bind clause “WITH SCHEMABINDING” which is must required to allow indexing on the computed column, otherwise … More


Querying temporal table data

We have discussed what is a temporal table, how we can create it, and what are the limitations of a temporal table. We have also discussed how we can convert an existing table to a temporal table in our previous posts.

In this post “querying temporal table data” we are going to learn how to query the time varying data from a temporal table.

In SQL Server 2016, we have a new clause FOR SYSTEM_TIME, which has four new sub clauses to query temporal table data:

  1. AS OF <datetime>
  2. FROM <start_datetime> TO <end_datetime>
  3. BETWEEN <start_datetime> AND <end_datetime>
  4. CONTAINED IN (start_datetime, end_datetime)

Let’s create a temporal table with dummy data to query with FOR SYSTEM_TIME clause and its sub clauses with examples. Below script is used to create a temporal table and to insert few dummy rows in the table with SQL Server default datetime value (1900-01-01) … More