Daniel Calbimonte

How to implement array-like functionality in SQL Server

January 16, 2018 by

Introduction

I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.

I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.

Some developers asked me the same thing. Where are the arrays in SQL Server?

The short answer is that we use temporary tables or TVPs (Table-valued parameters) instead of arrays or we use other functions to replace the used of arrays.

The use of temporary tables, TVPs and table variables is explained in another article:

In this article, we will show:

  • How to use a table variable instead of an array
  • The function STRING_SPLIT function which will help us to replace the array functionality
  • How to work with older versions of SQL Server to handle a list of values separated by commas

Requirements

  1. SQL Server 2016 or later with SSMS installed
  2. The Adventureworks database installed

Getting started

How to use a table variable instead of an array

In the first demo, we will show how to use a table variable instead of an array.

We will create a table variable using T-SQL:

We created a table variable named myTableVariable and we inserted 3 rows and then we did a select in the table variable.

The select will show the following values:

Now, we will show information of the table Person.person of the adventureworks database that match with the nameS of the table variable:

The results will display the names and information of the table Person.person with the names of Roberto, Gail and Dylan:

Note that in SQL Server, it is better to use SQL sentences to compare values. It is more efficient. We do not use loops (WHILE) in general because it is slower and it is not efficient.

You can use the id to retrieve values from a specific row. For example, for Roberto, the id is 1 for Dylan the id is 3 and for Gail the id is 2.

In C# for example if you want to list the second member of an array, you should run something like this:

You use the brackets and the number 1 displays the second number of the array (the first one is 0).

In a table variable, you can use the id. If you want to list the second member (id=2) of the table variable, you can do something like this:

In other words, you can use the id to get a specific member of the table variable.

The problem with table variables is that you need to insert values and it requires more code to have a simple table with few rows.

In C# for example, to create an array, you only need to write the elements and you do not need to insert data into the table:

It is just a single line of code to have the array with elements. Can we do something similar in SQL Server?

The next solution will help us determine this

The function STRING_SPLIT function

Another solution is to replace arrays with the use of the new function STRING_SPLIT. This function is applicable in SQL Server 2016 or later versions and applicable in Azure SQL.

If you use the function in an old adventureworks database or in SQL Server 2014 or older, you may receive an error message. The following example will try to split 3 names separated by commas:

A typical error message would be the following:

Msg 208, Level 16, State 1, Line 8
Invalid object name ‘STRING_SPLIT’

If you receive this error in SQL Server 2016, check your database compatibility level:

If your compatibility level is lower than 130, use this T-SQL sentence to change the compatibility level:

If you do not like T-SQL, you can right click the database in SSMS and go to options and change the compatibility level:

The T-SQL sentence will convert the values separated by commas in rows:

The values will be converted to rows:

In the STRING_SPLIT function, you need to specify the separator.

The following query will show the information of people in the person.person table that matches the names used in the STRING_SPLIT function:

The query will show information about the people with the names equal to Roberto or Gail or Dylan:

If you want to retrieve a specific member of the string, you can assign a row # to each member row of the STRING_SPLIT. The following code shows how retrieve the information

ROW_NUMBER is used to add an id to each name. For example, Roberto has the id =1, Gail id=2 and Dylan 3.

Once you have the query in a CTE expression, you can do a select statement and use the WHERE to specify an ID. In this example, the query will show Dylan information (ID=3). As you can see, to retrieve a value of a specific member of the fake array is not hard, but requires more code than a programming language that supports arrays.

How to work with older versions of SQL Server

STRING_SPLIT is pretty helpful, but how was it handled in earlier versions?

There are many ways to solve this, but we will use the XML solution. The following example will show how to show the values that match the results of a fake vector:

The code will do the same that the STRING_SPLIT or the table variable solution:

In the first line, we just create a new fake array named oldfakearray and assign the names in the variable:

In the second line, we are declaring an XML variable:

In the next line, we are removing the comma and creating a XML with the values of the oldfakearray:

Finally, we are doing a select from the table Person.Person in the Adventureworks database where the firstname is in the @param variable:

As you can see, it is not an array, but it helps to compare a list of values with a table.

Conclusion

As you can see, SQL Server does not include arrays. But we can use table variables, temporary tables or the STRING_SPLIT function. However, the STRING_SPLIT function is new and can be used only on SQL Server 2016 or later versions.

If you do not have SQL Server, there were older methods to split strings separated by commas. We show the method using XML files.

References


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
Functions, T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

261 Views