Write more compact SQL Server code using new features

By: Sergey Gigoyan | Updated: 2018-11-20 | Comments (1) | Related: > TSQL

Problem

There are some common tasks in SQL Server where past solutions have been a bit long and complicated code. As there are no special functions for these purposes in older versions of SQL Server, but in SQL Server 2016 and 2017 several new functions and features allow Developers to rewrite code in a much more compact way.

Solution

In this tip, we are going to demonstrate some common problems requiring longer, complicated solutions in older versions of SQL Server. Then we will introduce a shorter solution for each task using new features that were released in either SQL Server 2016 and 2017 and will be noted in each solution below.

Create SQL Server Test Environment

Let’s create a test environment. This script creates a TestDB database with three tables with data: Student, Course and the StudentCourse table to map their relationships.

USE master
GO

--Database
CREATE DATABASE TestDB
GO

USE TestDB
GO

--Tables
CREATE TABLE Student
(
  StudentID INT NOT NULL,
  LoginName NVARCHAR(50),
  PRIMARY KEY (StudentID)
)
GO

CREATE TABLE Course
(
  CourseID INT NOT NULL,
  CourseName NVARCHAR(50) UNIQUE,
  PRIMARY KEY (CourseID)
)
GO

CREATE TABLE StudentCourse
(
  StudentCourseID INT NOT NULL IDENTITY(1,1),
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentCourseID),
  CONSTRAINT UC_StudentID_CourseID UNIQUE(StudentID,CourseID),
)
GO

--Filling data
INSERT INTO Student(StudentID, LoginName)
VALUES(1, '  [email protected]   '),
     (2, '[email protected] '),
     (3, ' [email protected]'),
     (4, '[email protected]')

INSERT INTO Course(CourseID, CourseName)
VALUES(1, 'psychology'),
     (2, 'physics'),
     (3, 'chemistry'),
     (4, 'astrology'),
     (5, 'mathematics'),
     (6, 'biology'),
     (7, 'geography')

INSERT INTO StudentCourse(StudentID, CourseID)
VALUES(1, 1),
     (1, 3),
     (1, 5),
     (2, 5),
     (3, 1),
     (3, 2),
     (3, 3),
     (3, 4),
     (3, 5),
     (3, 6),
     (3, 7),
     (4, 2),
     (4, 4),
     (4, 5)

Rollup Rows and Create a Comma Separated List

Sometimes we need to represent values from several rows into one column by a using a comma-separated list. So, our first task will be for each student to get his/her login name and enrolled subjects in one row separated by a comma.

To get a list of students and the enrolled courses, we can simply use this code:

USE TestDB
GO

--Getting Students' enrolled courses
SELECT s.LoginName, c.CourseName
FROM StudentCourse sc
INNER JOIN Student s ON sc.StudentID=s.StudentID
INNER JOIN Course c ON c.CourseID=sc.CourseID

This returns a table with two columns showing the mapping between student and course:

Old Way - Rollup rows using STUFF

Our task is to have a table with one row for each student with his/her login and all of his/her courses separated by a comma. In older versions of SQL Server, we can use STUFF with FOR XML PATH to solve the problem as follows:

USE TestDB
GO

--USING STUFF and FOR XML PATH in older versions of SQL Server
SELECT  
      LoginName,
      STUFF(( SELECT ',' + c.CourseName  AS [text()]
              FROM StudentCourse sc
              INNER JOIN Course c ON c.CourseID=sc.CourseID
              WHERE sc.StudentID = st.StudentID
              FOR XML PATH('')
      ), 1, 1, '' ) AS StudentCourses
FROM Student st

We will receive the desired result:

New Way - Rollup rows using STRING_AGG

SQL Server 2017 introduces a new string function – STRING_AGG. It is an aggregate function that concatenates the values of rows (expression) into a single string separated by the given separator. As you can guess, it is an ideal function for solving our task. We can rewrite the code in SQL Server 2017 in the following way:

USE TestDB
GO

--SQL Server 2017 and later
SELECT s.LoginName, STRING_AGG(c.CourseName, ',') AS StudentCourses
FROM StudentCourse sc
INNER JOIN Student s ON sc.StudentID=s.StudentID
INNER JOIN Course c ON c.CourseID=sc.CourseID
GROUP BY s.LoginName

So, we have a simpler and more compact code to get the same result:

Replacing Leading and Trailing Spaces in a String

In our above example, you may have noticed that some of login names contain spaces. So our second task is to get the same result as in the previous task, but without spaces in the LoginName.

Old Way - Use LTRIM and RTRIM to remove unwanted leading and trailing spaces

In older versions of SQL Server, we use LTRIM and RTRIM functions to replace all spaces in a string:

USE TestDB
GO

-- Using LTRIM with RTRIM in older versions of SQL Server (note this code will not work in older versions because STRING_AGG is used)
SELECT RTRIM(LTRIM(s.LoginName)) AS LoginName, STRING_AGG(c.CourseName, ',') AS StudentCourses
FROM StudentCourse sc
INNER JOIN Student s ON sc.StudentID=s.StudentID
INNER JOIN Course c ON c.CourseID=sc.CourseID
GROUP BY s.LoginName

New Way - Use TRIM to remove unwanted leading and trailing spaces

SSQL Server 2017 introduced the TRIM function, which removes blanks (or other specified characters) from both the beginning and end of the given string. So, in SQL Server 2017 the code above can be rewritten by using just the TRIM, instead of two separate functions:

USE TestDB
GO

--SQL Server 2017 and later
SELECT TRIM(s.LoginName) AS LoginName, STRING_AGG(c.CourseName, ',') AS StudentCourses
FROM StudentCourse sc 
INNER JOIN Student s ON sc.StudentID=s.StudentID
INNER JOIN Course c ON c.CourseID=sc.CourseID
GROUP BY s.LoginName

Replacing Values in a String

Now, let’s assume that special characters in a string should be replaced by others. Let's say we have a list of subjects separated by the following symbols: , / - _ |. An example string is: 'psychology/physics,chemistry-astrology_mathematics|biology,geography'. It is required to use a comma as the only separator. In other words, any of these other symbols should be replaced with a comma.

Old Way - Replace values in a string using the REPLACE function

Before SQL Server 2017 we can do this by nesting REPLACE functions several times for each of the symbols. Since we are replacing four different values, we need to use the REPLACE function four times as follows:

USE TestDB
GO

--In older versions of SQL Server
DECLARE @str NVARCHAR(MAX)='psychology/physics,chemistry-astrology_mathematics|biology,geography'

SET @str=REPLACE(REPLACE(REPLACE(REPLACE(@str,'|',','),'_',','),'-',','),'/',',')

SELECT @str AS 'Subjects'

We will have the list of these subjects separated only by comma:

New Way - Replace values in a string using TRANSLATE function

In SQL Server 2017 we can use shorter code for this solution by using the new TRANSLATE function. This function allows replacement of some set of characters with the destination set of characters in the given string. The modern version of the code above will be the following in SQL Server 2017:

USE TestDB
GO

---SQL Server 2017 and later
DECLARE @str NVARCHAR(MAX)='psychology/physics,chemistry-astrology_mathematics|biology,geography'

SET @str=TRANSLATE(@str,'/-_|',',,,,')

SELECT @str AS 'Subjects'

Dropping Existing Objects

Sometimes, while creating a new object there is a possibility that there will be an existing object with the same name in the database. Therefore, it is reasonable to check the existence of the object and delete it before creating the new one. Let's say we need to check for a global temporary table ##tmpStudents and delete it if already exists.

Old Way - Check for object and drop if found

In older versions of SQL Server, it can be done in the following way:

--Using older versions of SQL Server
IF (OBJECT_ID('tempdb..##tmpStudent') IS NOT NULL)
   DROP TABLE ##tmpStudent

CREATE TABLE ##tmpStudent(StudentID int, LoginName NVARCHAR(50))

SELECT * FROM ##tmpStudent

New Way - Check for object and drop using IF EXISTS

From SQL Server 2016 and later we can use the DROP <object> IF EXISTS <object_name> command. In this code the object can be a table as well as view, index, trigger and so on. The full list of the objects that can be dropped in this way are listed in Microsoft’s documentation about the new features of SQL Server. The compact version of the code above is as follows:

--SQL Server 2016 and later
DROP TABLE IF EXISTS ##tmpStudent

CREATE TABLE ##tmpStudent(StudentID int, LoginName NVARCHAR(50))

SELECT * FROM ##tmpStudent

Conclusion

There are some useful features in the newer versions of SQL Server allowing Developers to write more compact code that facilitates the process of solving some routine tasks. It also makes the code more readable. In the next article we will talk about other new functions resulting in tighter code.

Next Steps

For more information about topics discussed in this article please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-11-20

Tuesday, November 20, 2018 - 8:42:22 AM - DCT	Back To Top (78295)
We need more of these articles!! Great job! I would add string split as another compact code feature as prior versions required a udf to split delimited strings.