STRING_SPLIT function – SQL Server 2016

Finally, STRING_SPLIT function, one of the most awaiting feature for a long time, has been introduced by Microsoft. In SQL Server 2016, now, we have a built-in system function to split a string with a specified separator.

Earlier, whenever we had to map a denormalized string (e.g. comma separated string values) with the values stored in a table column (normalized values) , we had to create a table valued function which could create a table from a given string value by splitting it using predefined separator. The problem with creating our own function to split the string was its performance, especially if we were not using either a CLR function or an highly optimized SQL function.

One of the most common scenario where we need this functionality very frequently is whenever we need to deal with SSRS’s multi value parameters. Reporting services supplies a comma separated string value to the … More


DATEDIFF and DATEDIFF_BIG – SQL Server 2016

We are already familiar with DATEDIFF function introduced in the very initial version of SQL Server. But, in SQL Server 2016, Microsoft has introduced DATEDIFF_BIG function which can be used to compute the difference between two given dates in terms of the given date part. In this post, we are going to explore the use of the DATEDIFF_BIG function.

Lets have a look on the syntax of both these functions:

Syntax:

DATEDIFF

DATEDIFF(<datepart>, startdate, enddate)

DATEDIFF_BIG

DATEDIFF_BIG(<datepart>, startdate, enddate)

Difference between DATEDIFF and DATEDIFF_BIG function

In above syntax, we can see that both the functions have similar syntax and any of them can be used to get the difference between two dates. However, these functions have different return types. DATEDIFF function returns an integer value as a difference between two dates, whereas DATEDIFF_BIG function returns a big integer value as a difference.

The range of the integer value in … More


Unfolding | Set Based Approach | Create Attendance Report – Episode 7 1

In this video, we are going to learn;

  1. Generate date list from the given date range dynamically
  2. Repeat the date list for each employee without using a loop or recursive CTE

You can download the script used in this example from below the video.

/******************************************************************************************************************************************************
Start using the SetBasedApproach database
******************************************************************************************************************************************************/
--Start using the existing database SetBasedApproach which has been already created in this series
USE SetBasedApproach
GO

/******************************************************************************************************************************************************
Create required objects
******************************************************************************************************************************************************/
--Drop the EmployeeMaster table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeMaster') IS NOT NULL)
DROP TABLE dbo.EmployeeMaster

--Create Employee master table
CREATE TABLE dbo.EmployeeMaster
(
EmpID INT NOT NULL PRIMARY KEY,
EmpName NVARCHAR(255) NOT NULL
)

--Drop the EmployeeAttendanceDetail table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeAttendanceDetail') IS NOT NULL)
DROP TABLE dbo.EmployeeAttendanceDetail

--Create EmployeeAttendanceDetail table which stores the attendance marked by the employees
CREATE TABLE dbo.EmployeeAttendanceDetail
(
RowId INT NOT NULL IDENTITY(1, 1),
CalendarDate 
More

Select all elements regardless of level in XML

We have already published a post on “Read XML Data as a table in SQL Server” and “Read and compare XML nodes dynamically with unknown elements“. In this post, we will see that how we can skip levels in XML string to read all the elements at a given level regardless of the level name. A wildcard character * (asterisk) can be used to extract all the elements of a XML string regardless of the level name. Lets have a demo to understand this.

Suppose, we have below XML data:

<Department>
<HR>
<EmpDetail>
<EmpName>Employee 1</EmpName>
<EmpId>1</EmpId>
<JoiningDate>20-Jan-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 2</EmpName>
<EmpId>2</EmpId>
<JoiningDate>15-Jul-2015</JoiningDate>
</EmpDetail>
<EmpDetail>
<EmpName>Employee 3</EmpName>
<EmpId>3</EmpId>
<JoiningDate>18-Mar-2015</JoiningDate>
</EmpDetail>
</HR>
<Admin>
<EmpDetail>
<EmpName>Employee 4</EmpName>
<EmpId>4</EmpId>
<JoiningDate>22-Jan-2015</JoiningDate>
</EmpDetail>
</Admin>
<Engineering>
<EmpDetail>
<EmpName>Employee 5</EmpName>
<EmpId>5</EmpId>
<JoiningDate>26-Feb-2015</JoiningDate>
</EmpDetail>
</Engineering>
</Department>

In this data, the root level node is <Department> which has <HR>, <Admin>, and <Engineering> as their children … More


Unfolding | Set Based Approach | Generate Rows Dynamically – Episode 6

In this video, we are going to learn;

  1. How we can Generate rows dynamically for each record of a table in set based approach, and
  2. How to do calculations on each row of a table generated dynamically

You can download the script used in this example from below the video.

--Use SetBasedApproach database
USE SetBasedApproach
GO
--Create the table EmployeeLoanDetail
CREATE TABLE dbo.EmployeeLoanDetail
(
EmpID INT NOT NULL,
EmpName VARCHAR(100) NOT NULL,
[Address] VARCHAR(256) NOT NULL,
LoanAmount NUMERIC(9, 2),
RepaymentTenureInMonth INT,
RepaymentStartDate DATE
)
GO
--Insert few records in the table
INSERT INTO dbo.EmployeeLoanDetail
(EmpID, EmpName, [Address], LoanAmount, RepaymentTenureInMonth, RepaymentStartDate)
SELECT 1 AS EmpID, 'Adam' AS EmpName, 'New Delhi' AS [Address],
100000 AS LoanAmount, 3 AS RepaymentTenureInMonth, '1-Feb-2016' AS RepaymentStartDate
UNION ALL SELECT 2, 'Joy', 'New Delhi', 150000, 5, '1-Mar-2016'
UNION ALL SELECT 3, 'Roy', 'New Delhi', 120000, 6, '1-Feb-2016'
UNION ALL SELECT 4, 'Mac', 'New Delhi', 160000, 8, '1-Dec-2016'
More

Unfolding | Set Based Approach | Tally Table – Episode 5

In this video, we are going to understand Tally table which is very useful in writing set based SQL queries. You can download the script which can be used to create the Tally table from below the video.

--Creating database SetBasedApproach
CREATE DATABASE SetBasedApproach
GO

--Started using database SetBasedApproach
USE SetBasedApproach
GO

--Creating tally table
CREATE TABLE dbo.Tally
(
N INT NOT NULL PRIMARY KEY
)

--Adding records
INSERT INTO dbo.Tally(N)
SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N
FROM master.sys.all_columns Tbl1
CROSS JOIN master.sys.all_columns Tbl2
More

Unfolding | Set Based Approach | How join works internally – Episode 4

In this video, we are going to understand how join works internally. If you have a good understanding of joins and how it works, you can skip this video and move to the next video in this series. If not, lets start understanding the joins. You can download the script used in this example from below the video.

Here is the example script used in this video.

--Create database
CREATE DATABASE SetBasedApproach
GO

--Start using the database
USE SetBasedApproach
GO

--Create employee table
CREATE TABLE dbo.Employee
(
[Emp ID] INT,
[Emp Name] VARCHAR(256)
)
GO

--Create department table
CREATE TABLE dbo.Department
(
[Emp ID] INT,
[Dept Name] VARCHAR(256)
)
GO

--Insert some values in employee table
INSERT INTO dbo.Employee ([Emp ID], [Emp Name])
VALUES(1, 'Adam'),
(2, 'Joy'),
(3, 'Roy'),
(4, 'Mac')
GO

--Insert some values in department table
INSERT INTO dbo.Department ([Emp ID], [Dept Name])
VALUES(1, 'Engineering'),
(2, 'Human Resources'),
More

Unfolding | Set Based Approach | SQL Server Query execution architecture – Episode 3

In this video, we are going to understand the query execution architecture which helps us to understand the importance of set based approach. When a T-SQL statement gets submitted to SQL server, it goes through multiple steps before we get the final output. Lets have a look on SQL Server query execution architecture.

More


Unfolding | Set Based Approach | Introduction to the course – Episode 2

This video will give you insight of the topic that we are going to cover in this series. We know that the thinking in terms of sets is creative and innovative and once you start understanding the concepts, believe me, you will find it is simple too. Writing set based queries needs a good understanding of SQL joins and other set based operators with some imagination ability, which we are going to learn in this course.

More


Unfolding | Set Based Approach | Why set based approach – Episode 1

Welcome to my course “Unfolding | Set Based Approach”. This is my first video tutorial series. In this series, I am going to share the concepts and techniques being used to create set based SQL queries.

This course is not based on how to write queries (this you can find at many places), rather it is based on how to think in set based manner. This course contains real life examples with deep insights to explore set based concepts, so that you can start thinking in terms of sets. Lets watch this video which explains why we should use set based approach instead of cursors or loops.

Please do share and comment if you like this. Also, please subscribe to our newsletter to be updated.

 

More