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
- SQL Server 2016 or later with SSMS installed
- 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:
|
1 2 3 |
DECLARE @myTableVariable TABLE (id INT, name varchar(20)) insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan') select * from @myTableVariable |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @myTableVariable TABLE (id INT, name varchar(20)) insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan') SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [Adventureworks].[Person].[Person] where FirstName IN (Select name from @myTableVariable) |
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:
|
1 |
Array[1]; |
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:
|
1 2 3 |
DECLARE @myTableVariable TABLE (id INT, name varchar(20)) insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan') select * from @myTableVariable where id=2 |
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:
|
1 |
string[] names = new string[] {"Gail","Roberto","Dylan"}; |
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:
|
1 |
SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ','); |
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:
|
1 2 3 |
SELECT compatibility_level FROM sys.databases WHERE name = 'AdventureWorks'; GO |
If your compatibility level is lower than 130, use this T-SQL sentence to change the compatibility level:
|
1 |
ALTER DATABASE [Adventureworks] SET COMPATIBILITY_LEVEL = 130 |
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:
|
1 |
SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ','); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [Adventureworks].[Person].[Person] where FirstName IN (SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',')); |
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
|
1 2 3 4 5 6 7 8 |
WITH fakearray AS ( SELECT ROW_NUMBER() OVER(ORDER BY value DESC) AS ID,value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',') ) SELECT ID, value FROM fakearray WHERE ID =3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @oldfakearray VARCHAR(100) = 'Roberto,Gail,Dylan'; DECLARE @param XML; SELECT @param = CAST('<i>' + REPLACE(@oldfakearray,',','</i><i>') + '</i>' AS XML) SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [Adventureworks].[Person].[Person] WHERE FirstName IN (SELECT x.i.value('.','NVARCHAR(100)') FROM @param.nodes('//i') x(i)) |
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:
|
1 |
DECLARE @oldfakearray VARCHAR(100) = 'Roberto,Gail,Dylan'; |
In the second line, we are declaring an XML variable:
|
1 |
DECLARE @param XML; |
In the next line, we are removing the comma and creating a XML with the values of the oldfakearray:
|
1 |
SELECT @param = CAST('<i>' + REPLACE(@oldfakearray,',','</i><i>') + '</i>' AS XML) |
Finally, we are doing a select from the table Person.Person in the Adventureworks database where the firstname is in the @param variable:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [Adventureworks].[Person].[Person] WHERE FirstName IN (SELECT x.i.value('.','NVARCHAR(100)') FROM @param.nodes('//i') x(i)) |
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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023