Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Write more compact SQL Server code using new features


By:   |   Last Updated: 2018-11-20   |   Comments (2)   |   Related Tips: More > T-SQL

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:

query results

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:

query results

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
query results

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:

query results

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:



Last Updated: 2018-11-20


next webcast button


next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 20, 2018 - 11:33:34 AM - Joe Celko Back To Top
Tip Comments Pending Approval

Tuesday, November 20, 2018 - 8:42:22 AM - DCT Back To Top

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


Learn more about SQL Server tools