Monthly Archives: August 2016


STRING_SPLIT function – SQL Server 2016 1

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