Category : SQL Server

Python use case – Map unequal comma separated values from two columns – SQL Server 2017

We know that Microsoft has integrated Python in SQL Server 2017 to enable rich data analytics capabilities within the database itself. Python is one of the most powerful languages which provides lots of built-in libraries for advanced data analytics and transformations. We can use Python for almost everything from website development to robotics and Data Science. In SQL Server 2017, Python can be used primarily for Machine learning purposes but it is not limited to that only. We can also use Python for complex data transformations and analysis which might be a bit tedious and complex while doing the same using T-SQL in SQL Server.

In this post, we will be exploring an use case example of Python for data transformation in SQL Server 2017. If you want to read more about Python and how to use it in SQL Server, you can visit my previous blog post “Why More

Why Python and how to use it in SQL Server 2017

Microsoft has integrated Python in SQL Server 2017 which can be used for in-database analysis purpose. In this post, we are going to explore “Why Python and how to use it in SQL Server 2017”, and then we will explore that how we can use it in SQL Server 2017.

Why Python

Python is a general purpose object oriented programming language which can be used to develop applications for a variety of domains. We can use Python for almost everything from desktop and website development, gaming, robotics, scientific and numeric computing to spacecraft control and much more. Python is a high-level programming language which is an interpreted language (execute line by line) instead of compiled language. The Python has gained popularity because of its user friendliness. The developers fall in love with Python because it is easy to learn, but still very powerful. The technology giants like Google, YouTube, Dropbox, … More

SQL Server 2017 New Features

Microsoft has launched the most recent SQL Server 2017 release candidate (RC1, July 2017). It can be downloaded from this link. SQL Server 2017 will run on both Windows as well as on Linux OS. It also supports macOS via Docker too. In this post, we will discuss the new features of SQL Server 2017.

SQL Server 2017 New Features (SQL Server vNext)

Though the SQL Server 2017 has many new features, in this post, we are going to highlight the features which can be mostly used by SQL Server Developers.

1. SQL Server Machine Learning Services – R and Python

SQL Server 2016 integrated the R programming which can be run within the database server and can be embedded into T-SQL script too. Now, in SQL Server 2017, we can execute the Python script within the database server itself. Both, R and Python are most popular programming language … More

Specific row at the top then sort the rest result set in SQL Server

In this post, “Specific row at the top then sort the rest result set in SQL Server“, we are going to learn that how we can order a result set in a customized way which cannot be achieved using ORDER by Clause in a simple way.

To demonstrate this, let’s create a sample table named as “tbl_Department” and insert some dummy rows in it. Below is the code to create the sample table:

IF(OBJECT_ID('dbo.tbl_Department') IS NOT NULL)
DROP TABLE tbl_Department
CREATE TABLE tbl_Department
DeptAddress VARCHAR(512),
DeptContact VARCHAR(15)

Now, add some sample rows into the table:

INSERT INTO tbl_Department
(DeptName, DeptAddress, DeptContact)
('Engineering', 'New Delhi', '1234567890'),
('Human Resources', 'Chennai', '9876543210'),
('Admin', 'Kolkata', '2763282789'),
('Human Resources', 'New Delhi', '1928272727'),
('Not Assigned', 'Mumbai', '1839288976')

Let’s have a look at the table data now:

Sample table

Sample table

From … More

Use NEWID() inside function in SQL Server

In SQL Server, if we use the NEWID() function inside an user-defined function, it throws an error as “Invalid use of side-effecting or time-dependent operator in ‘newid()’ within a function.

For any reason, if we need to use the NEWID() function inside an user-defined function, we need to do some work around. In this post, we will discuss few tips which can be used to deal with such kind of scenarios.

To demonstrate this problem, first, let’s create a user defined function which uses NEWID() function inside. Though in production environment we might be using more complex logic with the NEWID value, but in this demo, to keep it simple, we will be using the LOWER() function which converts the given value to the lower case.

--do some stuff with NEWID and 

Extract the first number from an alphanumeric string in sql server 4

In this post, we are going to learn how to extract the first numeric value from an alphanumeric string in SQL Server. To demonstrate this, we will create a dummy table and then we will insert some dummy rows into that table. Finally, we will use a SELECT statement to extract the first numeric value from the given alphanumeric string for each row of the table.

Let’s create the dummy table as below:

--Create dummy table named testTable
IF(OBJECT_ID('dbo.testTable')) IS NOT NULL
DROP TABLE dbo.testTable
CREATE TABLE dbo.testTable
val VARCHAR(100)

Now, let’s insert some alphanumeric values in this table as below:

--Insert some dummy rows in testTable
INSERT INTO dbo.testTable

We have below sample records in the testTable:

--Get data from testTable
SELECT * FROM dbo.testTable
Sample records in testTable

Sample records in testTable

Now, to extract the … More

Covering index and its usage

What is a covering index

A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.

The optimizer doesn’t need … More

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


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:



DATEDIFF(<datepart>, startdate, enddate)


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

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:

<EmpName>Employee 1</EmpName>
<EmpName>Employee 2</EmpName>
<EmpName>Employee 3</EmpName>
<EmpName>Employee 4</EmpName>
<EmpName>Employee 5</EmpName>

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